Guided Project 5-3

Hamilton Civic Center provides and tracks services for community residents. You prepare sheets for consolidation, create a dynamic data consolidation to link data, copy the company logo, and complete work for the summary workbook.

[Student Learning Outcomes 5.1, 5.2, 5.3, 5.4, 5.6]

 DO NOT use the files below if you are completing an autograded SIMnet Project. Using the linked files below will prevent you from uploading and submitting your work in SIMnet. The files for all autograded SIMnet Projects MUST be downloaded from your class assignment.

Files Needed: HamiltonCC-05.xlsx, HamiltonCCLinked-05.xlsx, and HCC.png (GP-E5-3) (Student data files are available in the Library of your SIMnet account.)

Completed Project File Names: [your initials] Excel 5-3a.xlsx and [your initials] Excel 5-3b.xlsx

Skills Covered in This Project

• Prepare grouped worksheets for consolidation.
• Copy and place a picture.
• Create a static data consolidation by category.
• Customize a built-in number format.
• Link workbooks by position in the Consolidate dialog box.
• Insert, size, and position a picture.

1. Open the HamiltonCC-05 workbook from your student data files and save it as [your initials] Excel 5-3a.
2. Group worksheets.
   1. Click the FirstWk worksheet tab.
   2. Press Shift and click the ThirdWk tab. Three sheets are grouped.
3. Complete grouped worksheets.
   1. Select cells B5 and fill the weekday names to Friday.
   2. AutoFit columns B:F.
   3. Select cells G6:G12 and click the the AutoSum button [Home tab, Editing group].
   4. Select cells B13:G13 and click the the AutoSum button [Home tab, Editing group].
   5. Click cell A4.
   6. Right-click the SecondWk sheet tab and choose Ungroup Sheets.
4. Copy and position a picture.
   1. Click to select the organization logo on the SecondWk sheet.
   2. Press Ctrl+C to copy the picture.
   3. Click the FirstWk sheet tab and press Ctrl+V to paste the picture.
   4. Point to the picture frame to display a move pointer.
   5. Drag the picture to fine-tune its location so that it appears in column A to the left of “Hamilton Civic Center.” Nudge the image with any keyboard directional arrow key to move it in tiny increments.
   6. Click cell A4.
5. Prepare a consolidation sheet.
   1. Make a copy of the FirstWk sheet at the end of the tabs.
   2. Name the copy as Static. Note that the service names are sorted in ascending order.
   3. Click the ThirdWk tab and note the order of the service names.
   4. Click the Static tab. You will consolidate by including the service names in column A.
   5. Select cells A6:F12 and press Delete.
6. Create a static data consolidation by category.
   1. Confirm or select cells A6:F12 on the Static sheet.
   2. Click the Consolidate button [Data tab, Data Tools group].
   3. Choose the Sum function.
   4. Select and delete references in the All references box.
   5. Click the Reference box and click the FirstWk tab.
   6. Select cells A6:F12 and click Add in the Consolidate dialog box.
   7. Click the SecondWk tab, verify that cells A6:F12 are selected, and click Add.
   8. Add the ThirdWk worksheet data to the All references list. Note that the FirstWk reference is first in the list.
   9. Select the Left column box in the Use labels in group (Figure 5-85).
   10. Click OK. The service names are sorted in ascending order because they follow the order on the first sheet in the list. The grand total is 896.
7. Edit and format the consolidation sheet.
   1. Select cells B6:G13 on the Static sheet.
   2. Click the Comma Style button [Home tab, Number group]. The built-in style formats values with a thousands separator when needed and two decimal places.
   3. Decrease the decimal two times.
   4. You want large values to show a comma but do not need decimal places; the values in this worksheet have not yet reached 1,000.
   5. Select cell A1; this is a merged cell.
   6. Click the Font launcher [Home tab, Font group] and click the Fill tab. The cell uses a gradient fill.
   7. Click Fill Effects.
   8. Click the Color 2 drop-down list and select White, Background 1, Darker 35% (first column) (Figure 5-86).
   9. Click OK to close both dialog boxes.
   10. Change cell A3 to read Three-Week Summary. This is a merged cell.
   11. Set the sheet tab color to Black, Text 1 (second column).
8. Save the [your initials] Excel 5-3a workbook and leave it open.
9. Open the HamiltonCCLinked-05 workbook from your student data files. Click Enable Content if link update is disabled; or click Update or Continue without updating. The message box depends on your work or classroom Excel environment.
10. Save the workbook as [your initials] Excel 5-3b.

1. Create a dynamic data consolidation to link workbooks.
   1. Select cells D7:D13 on the ServiceCounts sheet.
   2. Click the Consolidate button [Data tab, Data Tools group].
   3. Choose the Sum function.
   4. Select and delete references in the All references box.
   5. Click the Reference box and press Ctrl+F6 to switch to the [your initials] Excel 5-3a workbook.
   6. Click the Static sheet tab if necessary.
   7. Select cells G6:G12. When you have only one reference, you don’t need to click Add.
   8. Select the Create links to source data box (Figure 5-87). The focus returns to [your initials] Excel 5-3b.
   9. Click OK.

1. View outline results.
   1. Switch to the [your initials] Excel 5-3b workbook if necessary. The results are formatted as an outline.
   2. Select cell D21, click the AutoSum button [Home tab, Editing group]. The SUM function includes the value cells but not the 3D reference cells (Figure 5-88).
   3. Press Enter.
   4. Expand row 10 and click cell D9. This is a 3D reference to cell G7 in [your initials] Excel 5-3a.
   5. Click the Trace Error button for cell D9. Excel identifies an inconsistent formula. The outline format inserts rows for the 3D references. There is no error.
   6. Collapse the outline.

1. Insert a picture from a file.
   1. Click cell G2 on the ServiceCounts sheet.
   2. Click the Pictures button [Insert tab, Illustrations group] and choose This Device.
   3. Find and select HCC from your student data files.
   4. Click Insert. The picture displays at a default size.
   5. Click the Height box [Picture Format tab, Size group].
   6. Type .5 to replace the default height and press Enter. The image is proportionally resized.
   7. Point to the logo frame to display a move pointer.
   8. Drag the image to cover cells A1:B3.
   9. Click cell A25.
2. Save and close the [your initials] Excel 5-3a workbook. Save and close the [your initials] Excel 5-3b workbook (Figure 5-89)