PAUSE & PRACTICE: EXCEL 5-1

For this project, you format grouped sheets to create static and dynamic data consolidations for Perfect Vacation Rentals. You also use a formula to link workbooks.

Files Needed: PerfectRentals-05.xlsx and PerfectRentalsLinked-05.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] PP E5-1.xlsx

1. Open the PerfectRentals-05 workbook from your student data files and save it as [your initials] PP E5-1.
2. Group worksheets.
   1. Select the Mississippi Valley sheet tab.
   2. Press Ctrl and click the Great Lakes tab.
3. Sum data on grouped sheets.
   1. Select cells E5:E10 and click the AutoSum button [Home tab, Editing group].
   2. Select cells B11:E11 and sum them.
4. Format sheets and ungroup them.
   1. Select cells A4:E4 and apply Bold and Center alignment.
   2. Select cell A11 and apply Bold and Right alignment.
   3. Select cells B5:E11 and apply Currency format.
   4. Decrease the decimal two times.
   5. Select cell A1.
   6. Right-click the Great Lakes tab and choose Ungroup Sheets.
5. Create a static data consolidation by position.
   1. Make a copy of the Great Lakes sheet immediately after the Great Lakes sheet and name it Static.
   2. Select cells B5:D10 and press Delete. The formulas calculate to zero.
   3. Confirm that cells B5:D10 are selected and click the Consolidate button [Data tab, Data Tools group].
   4. Verify that the Sum function is selected.
   5. Select and delete recently used reference ranges in the All references box if any are listed.
   6. Click the Reference box and click the Mississippi Valley sheet tab.
   7. Select cells B5:D10.
   8. Click Add in the Consolidate dialog box.
   9. Click the Great Lakes sheet tab. Verify that the same cell range (B5:D10) is selected. If it is not selected, select it.
   10. Click Add (Figure 5-23).
   11. Click OK. The total in cell E11 is $24,036.
   12. Edit the label in cell A2 to Great Lakes and Mississippi Regions.
6. Prepare a consolidation sheet.
   1. Click the SortedByService sheet tab. The service names in column A are sorted in ascending order.
   2. Click the Mississippi Valley sheet. The service names are not sorted.
   3. Make a copy of the SortedByService sheet tab at the end.
   4. Name the copy Dynamic.
   5. Select and delete column E. This consolidation sheet will average values so totals are not necessary.
   6. Select and delete row 11.
   7. Edit the label in cell A2 to Great Lakes and Mississippi Regions.
7. Create a dynamic data consolidation by category.
   1. Select cells A5:D10 on the Dynamic sheet and delete them.
   2. Click the Consolidate button [Data tab, Data Tools group].
   3. Click the Function drop-down list and choose Average.
   4. Select and delete each reference in the All references box, if any.
   5. Click the Reference box and click the SortedByService sheet tab.
   6. Select cells A5:D10 and click Add in the Consolidate dialog box.
   7. Click the Mississippi Valley sheet tab. Confirm that cells A5:D10 are selected.
   8. Click Add. Note that the Mississippi Valley sheet is first in the list.
   9. Select the Left column box in the Consolidate dialog box.
   10. Select the Create links to source data box (Figure 5-24).
   11. Click OK. The labels are consolidated in the same order as the Mississippi Valley sheet and the outline is collapsed.
8. Review the dynamic consolidation.
   1. Set the width of column A to 15.
   2. Expand the Trip Insurance item. The Consolidate command inserts a column with the file name when the Left column is used to identify the category.
   3. Select cell C11. This is a 3D reference to the Mississippi Valley sheet (Figure 5-25).
   4. Select cell C12. This is a 3D reference to the SortedByService sheet.
   5. Hide column B.
   6. Collapse the Trip Insurance item.
   7. Press Ctrl+Home.
9. Link workbooks using a formula.
   1. Open the PerfectRentalsLinked-05 workbook from your student data files. This is the source workbook.
   2. Click the Total Days sheet tab in [your initials] PP E5-1.
   3. Click cell C5.
   4. Type = to start the formula.
   5. Press Ctrl+F6 or click the icon on the Windows taskbar for PerfectRentalsLinked-05.
   6. Click the Mar-May worksheet tab.
   7. Select cell F5 and type + for addition.
   8. Click the June-Aug sheet tab.
   9. Select cell F5 and type + for addition.
   10. Click the Sep-Nov sheet tab and select cell F5 (Figure 5-26).
   11. Press Enter. The first result is 218 in the Total Days sheet.
10. Edit the formula to use relative references and copy it.
    1. Select cell C5 in the Total Days sheet.
    2. Click the Formula bar and click within the first occurrence of $F$5.
    3. Press F4 (FN+F4) three times to remove the dollar signs and display F5.
    4. Edit the formula in the Formula bar by clicking within the second occurrence of the $F$5 address and removing the dollar signs.
    5. Change the third occurrence of $F$5 to the relative address F5.
    6. Press Enter when all references are relative.
    7. Copy the formula in cell C5 to complete the Total Days column.
    8. Press Ctrl+Home.
11. Save and close the workbook (Figure 5-27). Close PerfectRentalsLinked-05 without saving.