Independent Project 5-5

At Sierra Pacific Community College District, student assignment and exam points are listed on a single sheet but in different name order. You consolidate by category and copy the results to another sheet. You also insert the school logo in a header.

[Student Learning Outcomes 5.2, 5.6, 5.8]

Files Needed: SPCCD-05.xlsx and SPCCD.png (IP-E5-5) (Student data files are available in the Library of your SIMnet account.)

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

Skills Covered in This Project

• Remove the Mark as Final property.
• Create a static data consolidation.
• Consolidate data by category.
• Insert a picture from a file in a header.
• Size a picture.
• Adjust page margins to fit a picture.

1. Open the SPCCD-05 workbook. It has been marked as final.
2. Click Edit Anyway to remove the Read-Only property from the Mark as Final command.
3. Save the workbook as [your initials] Excel 5-5.
4. Select the PPT&Access sheet. Note that student names in columns A and E are in different order.
5. Consolidate points by category.
   1. Select cells I4:K25 on the PPT&Access sheet. You will consolidate names and points in this range.
   2. Open the Consolidate dialog box and delete any references in the All references list.
   3. Choose Sum as the function. The first reference is cells A4:C25.
   4. Select cells E4:G25 for the second reference.
   5. Select the Left column box and do not use links (Figure 5-94).
   6. Click OK. The columns are consolidated, and the names are in the same order as the PowerPoint segment, alphabetical order.
6. Copy and format consolidation results.
   1. Select cells J4:K25 and click the Copy button [Home tab, Clipboard group] .
   2. Click the Total sheet tab. Student names are entered in alphabetical order.
   3. Paste the data in cell C4.
7. Format consolidation results.
   1. Select cell A4 and format the data as a table using Blue, Table Style Medium 16. The data does have headers.
   2. Show the Total Row and choose Average as the function in the Points and Exam columns.
   3. Type Average in cell A26 and format it bold and right-aligned.
   4. Type Total in cell E3.
   5. Enter a formula in cell E4 to add cells C4 and D4.
   6. Show Average in row 26 for column E.
   7. Format cells C26:E26 to display two decimal places. The values are rounded.
   8. Format cell A1 with a 16 pt. font size.
   9. Set the height of row 1 at 22.
   10. Center cells A3:E3 (Figure 5-95).
8. Insert a picture from a file in a header.
   1. Click the Page Layout view button on the Status bar.
   2. Click the right header section.
   3. Click the Picture button [Header & Footer tab, Header & Footer Elements group].
   4. Click Browse for the From a file group in the Insert Pictures dialog box.
   5. Find and select SPCCD in the folder with your data files.
   6. Click Insert. The code is placed in the section (Figure 5-96).
   7. Click a worksheet cell. The picture displays in Page Layout view at its default size, too big in this case.
9. Format a picture in a header.
   1. Click the right header section to display the &[Picture] code.
   2. Click the Format Picture button [Header & Footer tab, Header & Footer Elements group]. The Format Picture dialog box opens.
   3. Click the Size tab and change the Height in the Size and rotate group to .50 to replace the default height.
   4. Press Tab to move to the Width box. When Lock aspect ratio is active, the image is proportionally sized but you do not see it until you complete the header.
   5. Click OK and click a worksheet cell. The image extends slightly into row 1.
   6. Click the Normal view button on the Status bar. You do not see headers in this view.
10. Adjust margins to accommodate the image.
    1. Click the File tab and choose Print.
    2. Click the Show Margins button in the bottom right corner.
    3. Drag the header margin up slightly to reduce its size.
    4. Drag the top margin marker down slightly so that the image fits within the header space (Figure 5-97).
    5. Click the Page Setup link in the Print pane. You can determine or change your settings in the Page Setup dialog box.
    6. Click the Margins tab and set the Top margin at .9.
    7. Set the Header margin at .4. Close the dialog box.
    8. Return to the worksheet and press Ctrl+Home.
11. Save and close the workbook (Figure 5-98).