## All Weeks Excel Skills for Business: Intermediate I Quiz Answers

### Excel Skills for Business: Intermediate I Week 1 Quiz Answers

#### Quiz 1: Multiple Worksheets

Q1. Which of the following options allow us to move a worksheet in a workbook?

(One or more answers are possible – partial credit will be awarded)

**Left-click to drag and drop the worksheet tab to the desired position****In the Home tab, select Format > Move or Copy Sheet…**- While pressing Ctrl, left-click to drag the worksheet tab to the desired position.
**Right-click the worksheet tab and select Move or Copy**

Q2. If you delete a sheet by mistake, you can click **Undo **or press Ctrl+Z to undo the operation.

- True
**False**

Q3. When you click on the **+** button, what will happen next?

- A new worksheet will be added to the right of
**Sheet1**. - A new worksheet will be added to the right of
**Sheet3**. **A new worksheet will be added to the right of Sheet2.**

Q4. How do you make a copy of an existing worksheet?

- Press
**Shift**, left-click, drag and release. **Left-click, drag and release.**- Press
**Ctrl**(**Alt**on Mac), left-click, drag and release.

Q5. Which of the following is true of the scenario shown below:

**Sheet1**,**Sheet3**and**Sheet5**have been grouped using the Shift key.- If I change something on
**Sheet1**, the same change will be applied to**Sheet3**and**Sheet5**. **If I change something on Sheet2, the same change will be applied to Sheet4.**

Q1. When you link to a cell in a different workbook, your reference will contain several components. What will the reference look like when you link to a cell such as **R28** on the worksheet, **Expenses**, in a file named **Facility-expenses2017.xlsx**?

- ‘(Facility-expenses2017)Expenses’!$R$28
- ‘[Facility-expenses2017]Expenses’!$R$28
**‘[Expenses!]Facility-expenses2017’$R$28**- ‘[Facility-expenses2017]Expenses’;$R$28

Q2. What tool did we use to display these four workbooks on the screen?

**Select the View tab, click on Arrange All, select Tiled.**- Select the
**View**tab, click on**Arrange All**, select**Vertical**. - Select the
**View**tab, click on**Arrange All**, select**Horizontal**. - Select the
**View**tab, click on**Arrange All**, select**Cascade**.

Q3. Why would we link workbooks?

- To push data from one workbook into one or more other workbooks.
- To pull values from one or more workbooks and work with them in one of these workbooks, or work with them in a separate workbook.
**To make the same change in all linked workbooks at the same time.**

Q4. Once you have created links between workbooks, they will remain intact and continue to work even if you move the location of any of your files.

- True
**False**

Q1. How do you access the **Consolidate **feature?

- Right-click on any cell that you want to use for the consolidated value and choose from the context menu.
**Navigate to the Data tab and locate the feature in the Get External Data group.**- Navigate to the
**Page Layout**tab and locate the feature in the**Sheet Options**group. - Navigate to the
**Data**tab and locate the feature in the**Data Tools**group.

Q2. When using the **Consolidate **feature, you can only use the SUM function.

- True
**False**

Q3. Which sentence is correct about **Consolidate**?

- The consolidation setup can be run just once.
- There is no way to link consolidated worksheet to the data sources.
**When you are linking to a data source, the “Create link to data source” check box should be ticked in Consolidate dialog box.**- It creates a link between data sources and the consolidated worksheet automatically.

Q4. You can undo **Consolidation with Links**.

- True
**False**

#### Quiz 2: Test your Skills: Working with Multiple Worksheets & Workbooks

Q1. Ensure you have downloaded all the **C2 W1 Assessment** workbooks. Open the workbook **C2 W1 Assessment Sales Di**. Move the worksheet called **Q1 Summary** to the front of the other worksheets. In the **Q1 Summary** worksheet click in cell **C5**, use 3D formulas to summarise the total quantity sold of item 3243 for January to March. How much commission did Di earn on this item?

*Do not enter the $ symbol – please enter the number as #.##*

Copy the formula in C5 down to C14. What was Di’s total commission?

*Don’t enter the $ symbol – please enter the number as ####.##*

*Save the workbook, but don’t close it, yet.*

**Sales Summary consolidate the Q1 summaries from the other three workbooks to get the total sales by item code.**

Q3. Open C2 W1 Assessment Sales Lemin and C2 W1 Assessment Sales Summary. In Sales Lemin note all the commissions are coming up as 0. Go to the Data tab, you will observe the commission rate cell F4 is empty. The rate needs to be picked up from the Sales Summary workbook Rates tab. Ensure you can see both workbooks on your screen, click in F4 in Sales Lemin and use a linking formula to pull the commission rate through from Sales Summary. In Sales Lemin return to the Q1 Summary, what is the updated Total Commission?

*Don’t enter the $ symbol – please enter the number as #.##*

*Save the workbook, but don’t close it, yet.*

**Sales Summary consolidate the Q1 summaries from the other three workbooks to get the total sales by item code.**

Q4. Open C2 W1 Assessment Sales Aneesha. You will see a message informing you of broken links, this is because the commission rate is still linked to the old sales summary. The link will need to be updated to look at the rate in C2 W1 Assessment Sales Summary. After updating the link what is Aneesha’s Q1 Commission?

*Don’t enter the $ symbol – please enter the number as #.##*

*Save the workbook, but don’t close it, yet.*

**Sales Summary consolidate the Q1 summaries from the other three workbooks to get the total sales by item code.**

Q5. In C2 W1 Assessment Sales Summary consolidate the Q1 summaries from the other three workbooks to get the total sales by item code for Q1. (Keep in mind that different sales people sell different products). What was the total sales figure for Item Code 3256?

*Enter your answer as a 5-digit number ##### – please don’t use decimals, commas or symbols.*

*Save the workbook, but don’t close it, yet.*

**Sales Summary consolidate the Q1 summaries from the other three workbooks to get the total sales by item code for Q1.**

### Excel Skills for Business: Intermediate I Week 2 Quiz Answers

#### Quiz 1: Changing Text Case (UPPER, LOWER, PROPER)

Q1. Cell **A2** contains the text “Sean SANDERS” and Uma wants cell **B2** to be “Sean Sanders”. Which function will help her to achieve this?

**=PROPER(A2)**- Use the Change Case button
- =LOWER(A2)
- =UPPER(A2)

Q2. Cell **A1** contains “Macquarie ” and cell **B2** contains “University”. How will Excel compute the following function:

**=CONCAT(A1,” “,UPPER(B2))**

**Convert “University” to upper-case, i.e. “UNIVERSITY” first, and then add this text to the end of “Macquarie ” to result in: “Macquarie UNIVERSITY”.**- Convert “University” to upper-case, i.e. “UNIVERSITY” first, and then add this before the text “Macquarie ” to result in: “UNIVERSITY Macquarie”.
- Convert “Macquarie” to upper-case, i.e. “MACQUARIE” first, and then add this text to the end of “University”, to result in: “University MACQUARIE”.
- Add the text “Macquarie ” to the text “University” first, and then convert the whole text to upper-case, i.e. “MACQUARIE UNIVERSITY”.

Q3. Cell **A3** contains “Chairs” and cell **B3** contains “Samantha” and Uma would like cell **C3** to contain “Samantha CHAIRS”. Which of these will let her do this?

(Multiple answers possible. Partial credit awarded).

- =CONCAT(A3,” “,UPPER(B3))
**=CONCAT(B3,” “,UPPER(A3))****=B3&” “&UPPER(A3)**- =UPPER(CONCAT(B3,” “,A3))

Q1. What formula would return the first 5 letters of the text in cell **A1**?

- =LEFT(A1,1-5)
- =LEFT(5,A1)
**=LEFT(A1,1,5)**- =LEFT(A1,5)

Q2. An ISBN is a number that identifies a particular edition of a book. There are 2 lengths of ISBN: ISBN-13 which is 13 characters long and ISBN-10 which is 10 characters long. You can convert from an ISBN-13 to ISBN-10 by removing the first 3 numbers. In cell **A2** you have the ISBN-13 9780747532699. How would you convert this to an ISBN-10 (0747532699)?

- =LEFT(A2,3)
**=MID(A2,3,10)**- =RIGHT(A2,10)

Q3. Cell **A2** contains the US telephone number **(939) 555-0113**. What will be displayed using the formula **=MID(A2,7,3)**?

**There are 2 lengths of ISBN: ISBN-13 which is 13 characters long and ISBN-10 which is 10 characters long. You can convert from an ISBN-13 to ISBN-10 by removing the first 3 numbers.**

Q1. If cell **A1** contains the text “John Smith”, why does the formula: **=LEFT(A1,FIND(” “,A1)-1)** return the word “John”?

- In fact, why does this formula always return the first word, if cell
**A1**contains two words separated by a space, ” “? - The outermost function,
**LEFT**, actually extracts the first word, such as “John” in this example, on its own, and the innermost function,**FIND**, is redundant. - The innermost function,
**FIND(” “,A1)**, actually extracts the first word, such as “John” in this example, on its own, and the outermost function,**LEFT**is redundant. **Here LEFT is being used as a helper function for the FIND function. The helper function, LEFT, finds the location of the space character, ” “, and this function in turn speaks to the FIND function, to extract all the text from the left until this space.**- Here
**FIND**is being used as a helper function for the**LEFT**function. The helper function,**FIND**, finds the location of the space character, ” “, and this function in turn speaks to the**LEFT**function, to extract all the text from the left until this space.

Q2. When using the **FIND** function and the second argument for ‘within text’ is: “Microsoft Excel”, which of the following are correct?

(Multiple answers possible. Partial credit awarded)

**=FIND(“o”, “Microsoft Excel”, 6) returns 7**- =FIND(“o”, “Microsoft Excel”) returns 7
- =FIND(“T”, “Microsoft Excel”) returns 9
**=FIND(“e”, “Microsoft Excel”) returns 14**

Q3. Suppose that cell **A1** contains the text “Microsoft Excel”, What would the following formula return: **=FIND(“Excel”,A1)**?

- 5
**11**- 16

Q4. Postcodes in the UK look like **SW1A 1AA**. This consists of 2 parts: the **Outward Code** before the space (**SW1A**), and the **Inward Code** after the space (**1AA**). The **Inward Code** is always 3 characters but the **Outward Code** can be 2, 3 or 4 characters. Which of the following will extract the **Outward** and **Inward Codes** from this list?

You can try it yourself using the attached file (Quiz Premier League.xlsx).

(Multiple answers possible. Partial credit awarded).

- Outward Code:
**=LEFT(C2,3)** - Inward Code:
**=RIGHT(C2,FIND(” “,C2)-1)** - Outward Code:
**=MID(C2,FIND(” “,C2)-1,1)** - Inward Code:
**=MID(C2,3,FIND(” “,C2)+1)** **Outward Code: =LEFT(C2,FIND(” “,C2)-1)****Inward Code: =RIGHT(C2,3)****Outward Code: =MID(C2,1,FIND(” “,C2)-1)****Inward Code: =MID(C2,FIND(” “,C2)+1,3)**

Q1. Use the **YEARFRAC** function to work out how many years difference is there between the date 1980-07-31 (31 July 1980) and 1998-05-02 (2 May 1998) (to 1 decimal place)?

**consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters**

Q2. The keyboard shortcut Ctrl+; is the same as using the **TODAY()** function. (Note: This is in the Toolbox for this week)

- True
**False**

Q3. Here is a super challenge for you if you feel up for it.

(You will need to work this out in Excel.)

In 2017 the Hindu festival of Diwali occurred on Thursday, 19 October. What fraction of the year was this? (to 1 decimal place). ** Hint**: Think carefully which start and end dates to use.

**consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters**

#### Quiz 2: Test your Skills: Text and Date Functions

Q1. It is necessary to provide more meaningful labels for boxes of items in stock. The first tab of the attached workbook contains the inventory items. You will need to generate the information needed on the **For Printing** tab using a range of text functions.

On the **Inventory** tab enter a formula in **J1** that will return the current date but not time. What function did you use?

*Please enter just the function name all in UPPERCASE letters with no equal sign, brackets or arguments.*

**consists of 2 parts: the Outward Code before the space (SW1A), and the Inward Code after the space (1AA). The Inward Code is always 3 characters but the Outward Code can be 2, 3 or 4 characters**

Q2. In J4 calculate the number of days since the last order, copy the formula down to J35. What is the check digit value in G1?

*Please enter just the number without decimal places, e.g. ###.*

Q3. In the For Printing worksheet in A5, generate a part code using “SKU-” followed by the Material Code, another “-” and the Part Number, e.g. SKU-07-2425. Copy the formula down. What is the value for Check Digit 1 (cell H5)?

*Please enter just the number ####.*

Q4. In B5 we need to see the material in upper case. Copy the formula down. What is the value for Check Digit 2 (cell H6)?

*Please enter just the number ####.*

Q5. In C5 we want to create a Distributor Code, which is the first 5 characters of the Distributor Name. Which function did you use?

*Please enter just the function name all in UPPERCASE letters with no equal sign, brackets or arguments.*

Q6. To avoid confusion, make a change to the formula in C5 so that all Distributor Codes show in all lower case. Copy the formula down. What is the value for Check Digit 3 (cell H7)?

*Please enter just the number.*

Q7. Many of the part descriptions are too long, so in D5 create a calculation to get the first 8 characters of the part description and then copy the formula down. That’s not ideal, so we want to change it to get all the text to the left of the first comma. Which function will allow us to locate the position of the first comma?

*Please enter just the function name all in UPPERCASE letters with no equal sign, brackets or arguments.*

Q8. Change the calculation in D5 to retrieve the part description up to the first comma. Copy the formula down. What is the value of Check Digit 4?

*Please enter just the number ####.*

Q9. The Distributor Address is in the format: “Street Address, Postcode, State”. In E5 enter a calculation that will retrieve just the post code from the Distributor Address. Which of the following combination of functions could you use to achieve this?

- RIGHT and CONCAT
- MID and FIND
**LEFT and CONCAT**- LEFT and FIND

Q10. Change the calculation in E5 to also include the State before the postcode, e.g. NSW2007. Copy the formula down. What is the value of Check Digit 5?

*Please enter just the number ####.*

### Excel Skills for Business: Intermediate I Week 2 Quiz Answers

#### Quiz 1: Introducing Named Ranges

Q1. Which of the following named ranges is valid?

- Sales 2016
- _Sales2016
**Qtr1**- Quarter-1

Q2. Named ranges behave like absolute cell references

**True**- False

Q3. Named ranges always contain a single cell.

- True
**False**

Q4. Which of the following are valid characteristics of names for named ranges?

(Multiple answers possible. Partial credit awarded)

**Underscores****Restricted length (no more than 255 characters)****Letters**- Labels used in cell references
- Hyphens

Q5. Uma wants to create a name for the range showing the Expenses for 2017. She types in Exp2017 but Excel rejects the name. What is the problem?

**Exp2017 is a cell reference**- Exp2017 is a mix of letters and numbers which is not permissible for names
- Exp2017 is a heading in the document so it cannot be used as a name

Q6. We can use the name box to:

**(Multiple options possible. Partial credit awarded)****Navigate to the range of an existing named range.**- Change the name of an existing named range.
**Name a new named range.**

Q1. Which of the following does the Name Manager allow us to do?

(Multiple answer possible. Partial credit awarded)

- Change the scope of a named range.
**Delete a named range.****Change the range of a named range.****Add a named range.**

Q2. The same range can have more than one name

**True**- False

Q3. The Name Manager tool can be found on which ribbon tab?

**Formulas**- Insert
- Data
- Home

Q4. Uma and Sean started their Monday morning with an argument. Uma thinks if you accidentally delete the wrong named range there is no need to worry, Excel will replace any range in a calculation that depends on this range with cell references. Sean strongly disagrees and suggests that the calculation will break down. Who is right?

- Uma
**Sean**

Q5. How do you find out whether there are any named ranges in a workbook?

(Multiple options possible. Partial credit awarded)

**Click on the Name Manager.****Click on the Name Box.**- Press F4.

#### Quiz 2: Test your Skills: Named Ranges

Q1. A travel expenses template has been compiled for your company to make it easier for staff to record their expenses when travelling to branches in other countries. It has recently been updated and the named ranges have been damaged in the process so most of the formulas are returning an error. You need to correct the named ranges to fix the problem.

Have a look at the Travel Expense worksheet. Note there are quite a few errors. Start by addressing the problem of the missing exchange rates by naming the ranges. Go to the **Currency Rates** worksheet and use **Create from Selection** to name all the rates (**A4:B12**) using the labels in column **A**.

What value is now showing for the **Other Expenses** in **K6**?

*Don’t enter the currency symbol – please enter the number as ##.##*

Q2. While the calculation of Other Expenses is looking better it is still not correct. Open the Name Manager. There is a range called **Coffee** that is no longer used, so delete it. Now have a look at **Ex_Rate**, it only goes to row 14, which explains the incorrect calculation. Edit it to go from **L11:L21**. Click OK and close the Name Manager.

What is the corrected value for **Other Expenses** in **K6**?

*Don’t enter the currency symbol – please enter the number as ##.##*

Q3. Let’s fix Travel Costs next. Open the Name Manager, there is a named range called **Travel_Costs**, but this is the wrong name, change it to **TravelCosts** and click OK and close the Name Manager.

What is the corrected value for **Transportation Expense** in **K3**?

*Don’t enter the currency symbol – please enter the number as ##.##*

Q4. Next, Lodging Costs. Use any method you think suitable to give the name **Lodging_Costs** to range **F11:F21**. What is the corrected value for **Lodging Expense** in **K4**?

*Don’t enter the currency symbol – please enter the number as ##.##*

Q5. And now to fix meals, let’s be efficient and use **Create from Selection** to name all three ranges simultaneously. Select **G10:I21** and click **Create from Selection**. What is the corrected value for **Meal Expense** in **K5**?

*Don’t enter the currency symbol – please enter the number as ##.##*

Q6. Our **Travel Expense** worksheet is now looking good, but we would also like to complete a breakdown of expenses by region. Start by adding the following named ranges:

**E11:J14**– London**E15:J18**– Paris**E19:J21**– Mumbai

Now go to the **Summary By Region** worksheet and observe the calculated values for London. What was the total amount spent in London in Pounds (**C4**)?

*Don’t enter the currency symbol – please enter the number as ##.##*

Q7. Enter a formula in **C5** to add up the total amount spent in Paris (use the named range you have just created). Then do the same in **C6** for Mumbai.

What was the total amount spent in Paris in Euros (**C5**)?

*Don’t enter the currency symbol – please enter the number as ##.##*

Q8. In **D5** create a calculation to convert Euros to Dollars by multiplying the Euros spent (**C5**) by the exchange rate for Euro (which is named **EUR**). Perform a similar calculation to convert the Indian Rupees to Dollars.

What was the total USD spent in Mumbai?

*Don’t enter the currency symbol – please enter the number as ##.##*

Q9. Click in **D7** and use Autosum to get the total spent in USD. If we were to now name the range **D4:D6**, would the formula just created in **D7** automatically change to use the named range? You should not actually create this named range.

- Yes, it would change immediately.
- Yes, but only when the formula is next refreshed.
**No, but you could use the Apply Named Range to apply it.**- No, you would have to manually change the formula.

Q10. Click in **B9** (still in Summary By Region), and use the Paste Names tool to Paste all the named ranges into your workbook. What value is in **B23**?

### Excel Skills for Business: Intermediate I Week 4 Quiz Answers

#### Quiz 1: COUNT functions

Q1. Given the following information, what would Excel return as the result for cell **B10**, if the formula was input as **=COUNT(B2:B8)**?

- 6
- 2
- 4
**7**- 1

Q2. Given the following information, what would Excel return as the result for cell **B10**, if the formula was input as **=COUNTA(B2:B8)**?

- 4
**6**- 2
- 1
- 7

Q3. Given the following information, what would Excel return as the result for cell **B10**, if the formula was input as **=COUNTBLANK(B2:B8)**?

- 6
- 7
- 2
- 4
**1**

Q4. For the following data, what formula(s) could you use to count the number of days that are being specified in Column **A**?

(Multiple answers are possible. Partial credit will be awarded)

- =COUNTBLANK(A2:A8)
**=COUNTA(A2:A8)****=COUNT(A2:A8)**- =COUNTDATE(A2:A8)

Q1. For the following information, what formula would you type into cell C12, to return the total number of calls made by Yeheli?

**=SUMIFS(C2:C9,B2:B9,”Yeheli”)**- =SUMIFS(B2:B9,”Yeheli”)
- =SUMIFS(B2:B9,C2:C9,”Yeheli”)
- =SUMIFS(C2:C9,”Yeheli”)

Q2. For the following information, what formula would you type into cell C13, to return the total number of calls made by Rahul?

- =SUMIFS(B2:B9,B2:B9,”Rahul”)
- =SUMIFS(C2:C9,C2:C9,”Rahul”)
- =SUMIFS(B2:B9,C2:C9,”Rahul”)
**=SUMIFS(C2:C9,B2:B9,”Rahul”)**

Q3. What would the following formula return: “=SUMIFS(C2:C9,B2:B9,”Yeheli”,A2:A9,”15/02/2013″)” ?

- 0
- 9
**5**- 3

Q4. For the following information, what would the formula below return: “=SUMIFS(C2:C9,B2:B9,”Yeheli”,B2:B9,”Rahul”)” ?

- 9
**17**- 0
- 8

We have a chart where the data approximately follows a straight line, which trendline would be the best fit?

- Power
**Logarithmic**- Linear
- Exponential

Q2. A graph shows a dramatic increase in sales for the first year followed by a steady levelling off in subsequent years. Which trendline would be the best fit?

- Exponential
- Power
**Linear**- Logarithmic

Q3. Of the values below, which R-squared value suggests the best fit?

- 1.980
**0.981**- 0.198

Q4. Another challenge for you to solve in Excel. Type in the data and create a line chart for Revenues – then solve this question: What is the R-squared value of a linear trendline?

- 1.0000
- 0.3617
**0.0000**- 0.4159

Q5. Another challenge building on the data we got you to type into Excel for the previous question. Create a line chart for Revenues – then solve this question. What is the R-squared value when creating an exponential trendline?

- 0.0000
- 0.4159
- 0.3617
**1.0000**

#### Quiz 2: Test your Skills: Summarising Data

Q1. The attached workbook is needed to answer all the s associated with this quiz.

Use Create from Selection to name each of the columns of data in the Ealing Property Sales sheet.

Check the Name Box to see all your named ranges have been created correctly. What name has been applied to the data in Column D?

- Year-Sold
- Year_Sold
**YearSold**- Year Sold

Q2. In C3 use a COUNT function to count the values in the named range ID.

What answer does the COUNT function return?

*Please enter just the number.*

**the total Price Paid for all Terraced properties sold in 2014. Make any necessary adjustments and then drag the formula down**

Q3. This is not the result we were hoping for, look carefully at the ID column, can you see why we got this answer?

Why did the COUNT return this result?

- Because some IDs have spaces
- Because some IDs are blank
- Because some IDs contain text characters
**Because some IDs are invalid**

Q4. Have a look at column J (Flat Number), note that a lot of the cells are blank.

Which function would you use to count the number of blank cells in a column?

the total Price Paid for all Terraced properties sold in 2014. Make any necessary adjustments and then drag the formula down

Q5. On the Summary Data sheet, in cell B4, use a function to sum the Price Paid for all properties of type Terraced. Copy the formula down.

What was the total Price Paid for Flats?

*Don’t enter the currency symbol or decimal points, just the plain number of the format #####*

**the total Price Paid for all Terraced properties sold in 2014. Make any necessary adjustments and then drag the formula down**

Q6. In C4 create a formula to sum the total Price Paid for all Terraced properties sold in 2014. Make any necessary adjustments and then drag the formula down and across to complete the table.

Which of these formulas is correct?

**=SUMIFS(Price_Paid,Property_Type,$A4,Year_Sold,C$3)**- =SUMIFS(Price_Paid,Property_Type,$A$4,Year_Sold,C3)
- =SUMIFS(Price_Paid,Property_Type,$A$4,Year_Sold,C$$3)
- =SUMIFS(Price_Paid,Property_Type,A4,Year_Sold,$C$3)

Q7. In F4 create a sparklines showing the sales trends for terraced houses from 2014 to 2016. Copy the sparkline down to F8.

Which of these property types follows a completely different trend to the others?

- Semi
- Other
**Flat**- Detached

Q8. Click in A12. Note the drop down that allows you to select different Towns, leave it set to London. In B13 create a calculation that will show the number of properties sold in the selected town for July 2015. (Note you will need to add criteria to check Year Sold and Month Sold). Copy the formula down to get results for the other months.

Which Month had the lowest number of sales?

*Type out the full name of the month.*

**June**

Q9. In C13 create a formula to sum the total price paid for properties sold in the selected region (London) for July 2015. (Note you will need to add criteria to check Year Sold and Month Sold.) Copy the formula down to get results for the other months.

Which Month had the second lowest total sales?

*Type out the full name of the month.*

**July 2016**

Q10. Select the range A12:C18 and create a line chart. Put the Total Sales series on a secondary axis and change it to a Clustered Column chart.

Which of the following most closely resembles your chart?

- Option 1
- Option 2
- Option 3
**Option 4**

Q11. In your new chart select the Number Sold series and add a trend line. Show the R² value. Compare the results you get from the different trend line options.

Which of the following trendline options yields the best R² value?

- Exponential
- Linear
- Logarithmic
**Power**

Q12. Change your trendline to a Polynomial Order 2.

What R² value does the Polynomial Order 2 show?

**0.6789.**

### Excel Skills for Business: Intermediate I Week 5 Quiz Answers

#### Quiz 1: Creating and Formatting Tables

Q1. You need to select all your data before converting it to a table.

- True
**False**

Q2. The name of a table can be changed in the following way:

**Click the Tables Tools Design tab that appears when clicked anywhere in the table and navigate to Table Name in the ribbon.**- Click anywhere in the table and without going to the Design tab, find Table Name in the ribbon.
- Right click anywhere in the table, go to Table in the context menu and find Table Name.

Q3. In tables, named ranges will automatically extend when you add an extra row/column.

**True**- False

Q1. You can access the sorting and filtering tools in the following ways:

(Multiple answers possible. Partial credit will be awarded).

**In the right-click menu.****Data tab in the ribbon.**- Going to the Table Tools Design tab that appears when you are clicked anywhere on the table and navigating to Sort.
**Filter buttons found in the header row of the table.**

Q2. Uma told Sean that Excel automatically recognises that some columns contain a certain kind of format e.g. (date format, text format) and provides useful filters in light of this e.g. text filters for fields containing text. Is Uma right?

**Yes**- No

Q3. Using the attached Workbook:

Go to the **Table Tools Design** tab which appears when clicked on the table and go to **Table Style Options** and check (tick) the **Total Row**. Now, go to the Total Row in your table, hover to Annual Salary, press the drop down button and go to **StdDev**.

What value do you get?

- 1958400
**184 213 731.09**- 13572.54

Q1. You can convert a table to a range by:

(Multiple answers are possible. Partial credit will be awarded).

- Going to the Data tab in the ribbon and navigating to Convert to Range.
- Going to the Formulas tab in the ribbon and navigating to Convert to Range.
**Right click context menu and going to table then Convert to Range.****Navigating to the Table Tools Design tab while clicked on the table and going to Convert to Range in Tools.**

Q2. What property of subtotal reports makes it important to name the sheet on which the subtotal report is produced?

- Subtotal reports can easily be mistaken for something else.
- Subtotal reports contain important information and must be named.
**The report is a snapshot of the database and won’t be updated should the database change, so a useful name can remind you of this.**

Q3. Which of the following steps should you always take before you use the Subtotal feature in Excel?

**Sort your data.**- Convert your data to a Table.
- Add a new row to display the subtotal.
- Filter your data.

#### Quiz 2: Test your Skills: Tables

Q1. The attached workbook is needed to answer all the s associated with this quiz. Please download the file and open it in Excel before you start answering the s below.

Is the Supplier Phone number data (**O3:P10**) in a Table?

- Yes
**No**

Q2. Convert the Discount Code data (**O13:P15**) to a table.

- Add a new record directly below the table as follows:
- Code: C
- %: 15%
- What is the corrected Average Discount % shown in
**P19**?

*Don’t enter the percentage symbol – please enter only the number as #.##*

Q3. Convert the Homewares Inventory data to a table. Apply the table style **White, Table Style Light 18**. Turn off banded rows and turn on banded columns.

Which of the following most closely resembles your table?

- This one:
- This one:
- This one:
- This one:

Q4. Turn on the **Total Row**. Change the calculation for the **Retail Price** column to calculate the average retail price.

What is the average retail price?

*Do not enter the currency symbol, please enter just the numeric value #.##*

**Table Tools Design tab which appears when clicked on the table and go to Table Style Options and check (tick) the Total Row. Now, go to the Total Row in your table, hover to Annual Salary, press the drop down button and go to StdDev.**

Q5. The **In Stock** column displays the number of items in stock for each product. Using the **Total Row** to add a calculation, how many items are currently in stock for all products?

**Table Tools Design tab which appears when clicked on the table and go to Table Style Options and check (tick) the Total Row. Now, go to the Total Row in your table, hover to Annual Salary, press the drop down button and go to StdDev.**

Q6. You have been informed that there may be duplicates in the data. Use the **Remove Duplicates** tool to remove any duplicate entries.

What is the corrected average retail price?

*Do not enter the $ symbol, please enter just the numeric value #.##*

**Table Tools Design tab which appears when clicked on the table and go to Table Style Options and check (tick) the Total Row. Now, go to the Total Row in your table, hover to Annual Salary, press the drop down button and go to StdDev.**

Q7. You need to find out which products need re-ordering most urgently. Sort the data by the column **In Stock** from Smallest to Largest.

Enter the full product code for the product with the smallest number in stock.

Q8. You are doing an audit of products supplied by the company Kestrel. Filter the data by the supplier Kestrel.

What is the updated value for the total number of items in stock now?

*Please enter just the number ###.*

Q9. Clear the filter on **Supplier** and add a new filter to get the top 5% of stock items by retail price.

What is the average retail price for these items?

*Don’t enter the currency symbol, please enter just the numeric value #.##*

Q10. Clear all filters and turn off the total row. In the first empty row add the following record:

Product Code | HARD-026 |

Item Description | Chrome-Plated Face Frame Hinge |

Supplier | PHISION |

Department | Hardware |

Origin | China |

Location | Showroom |

Rack | 02 |

In Stock | 100 |

Target Level | 100 |

Reorder Level | 50 |

Discount % | (Copy down from the row above) |

Unit Cost | $6.32 |

Retail Price | $7.49 |

Turn the total row back on. What is the updated Average Retail Price?

*Do not enter the currency symbol, please enter just the numeric value #.##*

### Excel Skills for Business: Intermediate I Week 6 Quiz Answers

#### Quiz 1: Creating and Modifying a Pivot Table

Q1. When you create a **Pivot Table**, your data must be in a **Table** beforehand.

- True
**False**

Q2. You can only have one **Pivot Table** in a worksheet.

- True
**False**

Q3. What combination of settings was used to create this Pivot Table?

- This:
- This:
**This:**- This:

Q4. Uma has just watched the video on creating **Pivot Tables** and she is excited that you don’t need to highlight the whole data range to create a pivot table, all you need to do is make sure that you are clicked on one of the cells in the data range. Has Uma understood the topic well?

- Yes, of course you have to indicate to Excel where to find the data by being clicked in one of the cells that contains the data.
**No, you can just specify the table/range and Excel will know where the data is located.**

Q5. Which of the following is NOT part of a **Pivot Tables**?

(Multiple answers possible. Partial credit awarded).

- Calculated item
- Slicers
**Sparklines**- Calculated Field

Q1. Using the attached Workbook:

Create a filter by **Customer Type**. (Hint: drag **Customer Type** into **Report Filter**).

Filter by **Home Office** and **Small Business**. What is the total for **2016 Qtr 1** in **VIC**?

**1481.08**- 12942.20
- 1308.49

Q2. Using the same **Pivot Table** as you did in 1, select **Show Report Filter Pages**. Which of the following is true?

**Two new worksheets with new Pivot Tables have been added, one showing data for Home Office and one for Small Business.**- A new worksheet with new
**Pivot Tables**has been added for each of the**Account Managers**. - A new worksheet with new
**Pivot Tables**has been added for each of the**Customer Types**.

Q3. Uma suspects that the figures for **2015 Qtr 1** may be incorrect due to missing sales entries. How can Uma quickly investigate this value in the **Pivot Table**?

**Uma can double-click on the cell of interest to generate a list of all the records that yielded that value.**- Uma will have to go back to the original source data.
- Uma can right click on the cell of interest and select
**Value Field Settings**(**Field Settings**in Mac).

Q4. Uma has accidentally closed her field list. How can she get it back?

- Click away from the PivotTable and then click back on it.
**Navigate to the PivotTable Analyse tab and click Field List**- Double click on any cell in the pivot table.

Q1. Uma has just created a chart, however she doesn’t like how it looks like she feels that the legend and x-axis have been swapped. How can she rectify this problem?

(One or more answers are possible — partial credit will be awarded)

**Right-click on the chart and navigate to Select Data, click on it and an option to Switch Row/Column will be found.****Whilst clicked on the chart go to the field list and drag the fields between the Legends fields and axis fields.**- Whilst being clicked on the chart, go to the Analyze tab in the ribbon and navigate to Switch Row/Column Data.
**Whilst being clicked on the chart, go to the Design tab in the ribbon and navigate to Switch Row/Column Data.**

Q2. Uma can remember that when you create a Pivot Chart the **Field **filters appear on the Pivot Chart. Sean has just walked in talking about how useful **Report Filters **are. Uma now cannot recall if **Report Filters **also appear on the Pivot Chart. Do they? (Hop over to Excel and create a chart for a table that contains a report filter if you are confused as well).

**Yes**- No

Q3. Uma and Sean have not had a relaxing Friday, they cannot agree on whether it is possible to create a **Pivot Chart** by being clicked on the **Pivot Table** and navigating to the **Insert **tab and selecting a **Chart **under the** Chart Tools**. Is this possible?

**Yes, this works fine**- No, you have to the
**Analyze**tab that appears when clicked on a**Pivot Table**. - No, this does create a chart but the chart isn’t responsive to the
**Pivot Table**so is arguably pointless.

Q1. To connect a Slicer to a Pivot Table you have to be clicked on the pivot table.

- Yes
**No**

Q2. Sean has asked you to briefly explain to him what a Slicer is. What is the best response?

- Slicers are a very complicated concept that has been added to pivot tables in the newer versions of Excel, it is difficult to understand them and they are arguably pointless.
**A Slicer is a filter that has been made user-friendly and now anyone can use it with ease by just clicking on what they want the spreadsheet to filter by and the pivot table and charts (dashboard) display accordingly.**- Slicers are a more efficient way of creating pivot charts and pivot tables.

Q3. What options do you find under the Options tab which appears when clicked on a Slicer?

**Slicer Caption**- Field List Options
**Report Connections**- Change Data Source

Q1. The attached workbook is needed to answer all the s associated with this quiz.

Before creating pivot tables it is always a good idea to put your data into a table. Convert the sales data to a table. Rename the table **Sales**. Turn on the **Total Row** and in the total row in the **Price Paid** column select **Average** to get the average price paid.

What was the Average Price Paid?

*Don’t enter currency symbol, please enter just the number ######.##*

**PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter**

Q2. Turn off the **Total Row**. Create a **Pivot Table** in a new sheet to show the **Total Price Paid** for each **Town**.

What does the Pivot Table show as a total for **Southall**?

*Please enter just the number, no currency symbol or decimals.*

**PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter**

Q3. Modify the Pivot Table to show **Year Sold** in the columns.

What were the total sales for **Northholt** for 2016?

*Please enter just the number, no decimals.*

**PropertyType pivot, clear all filters, remove Year Sold from the Filter area and remove Property Type from the Rows area. Add Deed Date to the column area and group by Year and Quarter**

Q4. Change the value field settings to show the calculated values as a percentage of the Grand Total.

What percentage is shown for **London** in 2014?

*Don’t enter the percentage symbol, just the plain number with two decimal places – ##.##*

Q5. Still working in **Sheet1**, click into cell **A15**, and create a Pivot Table from your Sales data that shows total **Price Paid** for each **Property Type**. Rename the pivot **PropertyType**.

What was the **Total Price Paid** for **Semi** properties?

*Please enter the plain number, with no currency symbol or decimals.*

Q6. In the **PropertyType** pivot change the **Summarise Values By** setting to use a **Count** instead of a **Sum**.

How many **Flats** were sold?

Q7. Click in the **PropertyType** pivot and in the PivotTable field settings tick the **Estate Type** checkbox.

How many **Leasehold Terraced** properties were sold?

Q8. In the **PropertyType** pivot add **Year Sold** to the **Filters** section in the PivotTable field list. Change the filter to only show properties sold in 2014 and 2015.

How many **Freehold Terraced** properties were sold in this time period?

Q9. Still working in the **PropertyType** pivot, clear all filters, remove **Year Sold** from the **Filter** area and remove **Property Type** from the **Rows** area. Add **Deed Date** to the column area and group by **Year** and **Quarter**. If necessary expand the **Year** group for 2015.

How many **Freehold** properties sold in **Quarter 3** of 2015?

Q10. Add a slicer to filter the **PropertyType** pivot by **Postcode**. Select all postcodes that begin with **UB1** or **UB2**.

How many **Leasehold** properties sold in **Quarter 2** of 2015 for these postcodes?

#### Quiz 2: Final Assessment

Q1. Have a look at the first 3 worksheets, they contain student marks for 3 terms. Now go to the Final Marks worksheet and use 3D-Formulas to get Benjamin Abbot’s class test average for terms 1, 2 and 4. Copy the formula across to I4 and then down for the rest of the students. What was the Average Final Mark (as shown in cell M4)?

*Please enter the number with one decimal ##.#*

**Clustered Column pivot chart using the data in the second pivot table (if you have Excel for Mac select the data in A17:B20 and just create a regular chart). Add a linear trendline and display the R-squared value on the chart**

Q2. Note that you have a sheet called Marks Term 3 but it is not in the right position. Move this sheet to sit between the sheets Marks Term 2 and Marks Term 4. Check the Final Marks Sheet, what is the average Final Mark now?

*Please enter the number with one decimal ##.#*

**Clustered Column pivot chart using the data in the second pivot table (if you have Excel for Mac select the data in A17:B20 and just create a regular chart). Add a linear trendline and display the R-squared value on the chart**

Q3. Select the range A3:J465 and use Create from Selection to name each of the columns of data. This should have corrected the missing stats figures. What was the median Final Mark (M5)?

*Please enter the number with one decimal ##.#*

**Clustered Column pivot chart using the data in the second pivot table (if you have Excel for Mac select the data in A17:B20 and just create a regular chart). Add a linear trendline and display the R-squared value on the chart**

Q4. Select the range L20:M26 and name it Grades. This should have corrected the grades calculations. What grade did Olivia Jones get?

Q5. In M10 use a formula to calculate the total number of Fail grades. Copy the formula down to M16. Note cell P4 which displays the Total Number of students who achieved a “C” should have changed colour. What colour is the cell?

- Orange
- Green
- Yellow
- Blue

Q6. In N10 create a mixed reference formula that will count how many of Mr Chang’s students got a Fail. Drag the formula down and across to complete the table. Observe P5, which shows the number of A’s achieved by Ms Sekibo’s students. It should have changed colour. What colour is it now?

- Orange
- Yellow
- Green
- Blue

Q7. Have a look at the worksheets Absences Term 1 through to Term 4, they contain a list of dates that students were absent. We need to create a summary showing a count of how many days each student was absent. Go to the Absence Report Sheet. Click in A4, and then use the Consolidate tool to consolidate the data on the other Absences sheets. The results look a bit odd, but that is because the count values have been formatted as dates. Change the formatting to General or Number. Sort the data by Total Absences. How many students were absent for more than 15 days?

Q8. Go to the Student Report worksheet. Some of the information still needs to be completed. Create a formula in D4 to return the Student’s full name, this should be First Name followed by a space and then Surname. The case must also be corrected so that all words start with a capital letter but everything else is in lower case e.g., Benjamin Abbot. Copy the formula down for all the other students. What is the value of the check digit in S4?

Q9. In E4 create a formula to generate the student email address. This should be their first initial, followed by their surname, followed by “@newcollege.com”, and must all be in lower case, e.g. [email protected]. Copy the formula down for all the other students. What is the value of the check digit in S5?

Q10. The last two digits of the student number indicate the year the student enrolled. In F4 create a formula that will put “20” followed by the last two digits of the Student ID, e.g. 2015. Copy the formula down for all the other students. What is the value of the check digit in S6?

Q11. We would like to get an idea of how students have progressed over the year. Click in M4 and create a sparkline line chart that charts the data in cells I4:L4. Copy the sparkline down for all the other students. Change the sparkline to show the highest point. Which of these sparklines represents Olivia Jones’ data?

- This:
- This:
- This:
- This:

Q12. Convert the data in the Student Report Sheet to a table. Name the table Report and change the style to Green Table Style Medium 21. Which of the following styles did you choose?

- This:
- This:
- This:
**This:**

Q13. With the table still selected, turn on the Total Row. What are the Total Fees Owing?

*Don’t enter the currency symbol, just the number and decimal places, e.g. #######.##*

Q14. In the Total Row in the Year Enrolled column, chose the correct function to calculate the number of all students enrolled. How many are there?

Q15. Filter the table to show all Distance Learning students who owe more than $9,000. How many are there?

Q16. We would like to compare the results for different types of students. Clear all filters. Use the data in the table to create a pivot table (in a new sheet) that shows Grade in the Row Labels, Student Type in the Column Labels, and Count of Grade in the Values section. How many A’s did the Part Time Students get?

Q17. Change the pivot to show the values as a percentage of the column total. What percentage of Part Time students failed?

*Don’t enter the percentage symbol, please just enter the number as ##.## (2 decimal places).*

Q18. Mr Chang has observed that the students attending the college seem to be increasingly more able and more motivated. He would like to see if there is a pattern in the results based on enrolment date. Click in A17 and create another pivot table to show the average final mark by enrolment date. Add a filter field and change the filter to only show data for Mr Chang. Format the values to only show 2 decimal places. What was the Average mark for 2017?

*Please enter the number with two decimal places.*

Q19. Create a Clustered Column pivot chart using the data in the second pivot table (if you have Excel for Mac select the data in A17:B20 and just create a regular chart). Add a linear trendline and display the R-squared value on the chart. What is the R-squared value?

*Please enter the number as #.#### (4 decimal places).*

Q20. Have a look at the other trend line options and select the one that returns the best R-squared value. Forecast forward for 1 period. If the trend continues, students who enrol in 2018 are expected to get an average result closest to…

- 68
- 70
**73**- 76

#### Get All Course Quiz Answers of Excel Skills for Business Specialization

Excel Skills for Business: Essentials Coursera Quiz Answers

Excel Skills for Business: Intermediate I Coursera Quiz Answers

Excel Skills for Business: Intermediate II Coursera Quiz Answers

Excel Skills for Business: Advanced Coursera Quiz Answers