please answers these questions csis 110

Table of Contents

VIEW:

Instructions
Previous Attempts
Best Practices

Guided Project 5-3

Windows Mac

Hamilton Civic Center tracks the number of participants enrolled in classes and seminars. You create a dynamic data consolidation to link data, prepare monthly sheets for consolidation, copy the company logo, and complete work for the summary workbook.

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

Files Needed: HamiltonCC-05.xlsx (Available from the Start File link.)

Completed Project File Names: [your name] HamiltonCC-05.xlsx

Skills Covered in This Project

Group and format worksheets.
Create a static data consolidation by category.
Sort consolidated data.
Copy a picture.
Break links in a workbook.
Link workbooks in the Consolidate dialog box.
Create a dynamic data consolidation.
Insert, size, and position a picture.

Open the HamiltonCC-05 start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
Group the worksheets.
1. Click the January worksheet tab.
  
  Figure 5-70
  
  Border tab in Format Cells dialog box
2. Press Shift and click the March tab.
Format grouped worksheets.
1. Select cells A5:F12.
2. Click the arrow with the Borders button [Home tab, Font group] and select More Borders.
3. Click the Color arrow and choose Black, Text 1 (second column).
4. Click the thin solid line Style (bottom choice in the first column of styles).
5. Click the vertical middle of the preview box. If you place a border in the wrong location, click the line in the preview to remove it.
6. Click the second line Style in the first column (two below None).
7. Click the horizontal middle of the preview box. This border will appear between rows.
8. Click the bottom line Style in the second column (a double border).
9. Click the bottom of the preview area to place a bottom horizontal border (Figure 5-70).
10. Click OK.
Enter SUM in grouped worksheets.
1. Select cells F6:F11.
2. Click the AutoSum button [Home tab, Editing group].
3. Use SUM in cells B12:F12.
4. Click cell A1.
5. Right-click the February sheet tab and choose Ungroup Sheets.
Copy a picture.
1. Click to select the organization logo on the February sheet.
2. Press Ctrl+C to copy the picture.
3. Click the January sheet tab.
4. Press Ctrl+V to paste the picture.
5. Point to the picture frame to display a move pointer.
6. 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. Adjust the width of Column A if necessary.
7. Click cell B1.
Copy the March sheet to the end and name it Quarter 1.
Set the tab color to Black, Text 1 (second column)
Edit cell A3 to read First Quarter Enrollment.
Create a static data consolidation by category.
1. Delete the contents of cells A6:E11 on the Quarter 1 sheet. The labels in column A are not in the same order on the quarterly sheets.
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 January tab.
6. Select cells A6:E11 and click Add in the Consolidate dialog box.
7. Click the February tab, verify that cells A6:E11 are selected, and click Add.
  
  Figure 5-71
  
  Consolidate dialog box to consolidate by category
8. Add the March worksheet data to the All references list.
9. Select the Left column box in the Use labels in group (Figure 5-71).
10. Click OK.
Sort consolidated data.
1. Select cells A6:E11 on the Quarter 1 sheet.
2. Click the Sort & Filter button [Home tab, Editing group].
3. Choose Sort A to Z.
4. Click cell B1.
Save and close your file (Figure 5-75).
Upload and save your project file.
Submit project for grading.

Figure 5-75 Coplease answers these questions and follow the instructions