Step

Instructions

Points Possible

1

Start Excel. Download and open the Excel file named Excel_CH07_Assessment_Music.xlsx. Your name will automatically be added to the start of the file name. Save the file to a location where you are storing your data files.

0

2

When worksheets are grouped, what you do to one worksheet happens to the other worksheets in the group. Group the Valley through Mills worksheets. Create a formula in cell D5 to calculate the total charges for each instrument. Copy the formula through cell range D5:D15.

2

3

With the worksheets still grouped, format cells C5:D16 with the Accounting Number Format with no decimals.

2

4

Entering formulas into grouped worksheets is a very efficient way to simultaneously create new data in multiple worksheets and is the same as entering data in grouped worksheets. In cells B16 and D16, with the worksheets still grouped, calculate the total number of students and total charges for the school.

2

5

With the worksheets still grouped, apply the Total cell style to cell range B16:D16. Ungroup the worksheets.

0.8

6

3-D references allow formulas and functions to use data from cells and cell ranges across worksheets. On the Summary worksheet, enter a 3-D SUM function in cells B5:B16 to calculate the total students for all four schools.

2.4

7

To save yourself time in formatting the Summary worksheet, use Fill Across Worksheets to copy the contents and formatting of cell range C5:D15 from the Mills worksheet to the Summary worksheet. Copy the format only of cell range B16:D16. Total the TotalCharges column.

2.4

8

On the LinkedSummary worksheet, you want to consolidate data and include the cell references from other worksheets that contribute to the consolidated data result. In cell A4, create a linked consolidation using cell range A4:D15 from each of the school worksheets. Select Top Row, Left Column, and Create links to source data in the Consolidate dialog box.

2.8

9

On the LinkedSummary worksheet, change the column width of column A to 13, hide column B, and change the column width of Columns C:E to 12.

0.4

10

Delete the RentalRates worksheet. Click the Valley worksheet and note the errors in the formulas. You will fix the errors in the next step.

1.2

11

Next, you want to create a link between two workbooks to ensure the rates in the Music workbook are always up to date by linking to the rates in the Rates workbook. Open Excel_CH07_Rates.xlsx and arrange the workbooks side by side. On the Excel_CH07_Music workbook, group the Valley through Summary worksheets. Click cell C5, and in the formula bar, replace RentalRates in the VLOOKUP function with a link to cell range A4:B14 (named range InstrumentRates) on the Excel_CH07_Rates workbook. Copy the formula in cell C5 to cells C6:C15. Ungroup the worksheets on the Music workbook. Close the Excel_CH07_Rates workbook.

4

12

Save and close Excel_CH07_Assessment_Music.xlsx. Exit Excel. Submit your file as directed.

0

Total Points

20