Advanced Project 5-7

These instructions are compatible with both Microsoft Windows and Mac operating systems.

American River Cycling Club collects data about a national race day. For this project, you format and consolidate data for four states and insert the club’s logo in a header. You then create a SmartArt graphic about race preparation tips.

[Student Learning Outcomes 5.1, 5.2, 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: AmRiverCycling-05.xlsx and ARRC.png (AP-E5-7) (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 5-7.xlsx

Skills Covered in This Project

• Group and format worksheets.
• Create a static data consolidation.
• Insert a picture in a header.
• Create a SmartArt graphic.
• Format, size, and position a SmartArt graphic.

1. Open the AmRiverCycling-05 workbook from your student data files and save it as [your initials] Excel 5-7.
2. Review and group worksheets.
   1. Click the Illinois sheet tab and review the data.
   2. Click each of the remaining three state sheets to confirm that the data follows a pattern.
   3. Group the four sheets with state tab names.
3. Format grouped worksheets.
   1. Type American River Cycling Club in cell A1 on the grouped sheets. Format the font size as 20 pt.
   2. Type National Race Day Participation in cell A2 and format the font size as 18 pt.
   3. Select cells A1:F2 and apply the Center Across Selection command.
   4. Select cells A3:F12 and apply All Borders.
   5. Select cell C3 and give the command to wrap the text.
   6. Set the width of columns A:B to 16.
   7. Set the width of columns C:F to 10.
   8. Format the Height of row 3 to 30.
   9. Format the labels in row 3 bold and centered.
   10. Adjust the width of column C to 12.
   11. Open the Page Setup dialog box and center the sheets horizontally on the page.
   12. Insert a header with the file name in the left section.
   13. Click cell A1 and ungroup the sheets.
4. Consolidate three columns of data.
   1. Click the Static sheet tab.
   2. Click cell A5. Consolidated results will start in this cell; you need not select a range.
   3. Consolidate the data from the four state worksheets using Sum.
   4. Apply All Borders to cells A5:C13.
   5. Center the sheet horizontally on the page.
   6. Insert a footer with the file name in the left section.
5. Insert a picture in a header.
   1. Delete the contents of cell A1.
   2. Switch to Page Layout view and click the center header section.
   3. Find and insert the ARCC image from your student data files.
   4. Format the picture to be .75 inch high. NOTE: Mac users skip this step and move on to step 5.e.
   5. Move the label in cell A2 to cell A3.
   6. Merge and center cells A3:C3.
   7. Click a worksheet cell and return to Normal view.
   8. Preview the worksheet as it will print. The sheet and the image are centered on the page.
6. Insert a SmartArt graphic.
   1. Insert a new sheet and name it Race Prep.
   2. Insert a Basic Process graphic.
   3. Click the Add Shape button [SmartArt Design tab, Create Graphic group]. The graphic has four shapes (Figure 5-104).
   4. Click the leftmost shape and type Get enough sleep. Do not include a period and do not press Enter after typing the label.
   5. Type Keep hydration and nutrition goals in the second box.
   6. Type Service and maintain your bike in the third shape.
   7. Type Encourage friends to join you in the fourth shape.
7. Format a SmartArt graphic.
   1. Click the frame of the SmartArt graphic to select it.
   2. Apply the Cartoon SmartArt style from the 3-D group.
   3. Change the SmartArt colors to Colorful– Accent Colors.
   4. Drag the graphic so that its top-left selection handle is in cell A1. Remember that you can nudge an object in small increments with a keyboard directional arrow.
   5. Point to the bottom right selection handle and resize the frame to reach cell L25.
   6. Select cell A30.
   7. Change the page orientation to Landscape.
   8. Turn off the Gridlines display.
8. Save and close the workbook (Figure 5-105).