prepare the monthly sales worksheet.

As a staff member at Blue Lake Sports Company, you are expected to prepare the monthly sales worksheet. You edit and format data, complete calculations, and prepare the workbook for distribution. You also copy the sheet for next month’s data.

[Student Learning Outcomes 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.8]

File Needed: BlueLakeSports-01.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-BlueLakeSports-01.xlsx

Skills Covered in This Project

• Open and save a workbook.
• Choose a workbook theme.
• Edit and format data.
• Center labels across a selection.
• Use SUM and the Fill Handle.
• Adjust column width and row height.
• Insert a header and a footer.
• Adjust page layout options.
• Copy and rename a worksheet.

1. Open the start file BlueLakeSports-01.xlsx workbook. The start file will be renamed automatically to include your name. Change the file name if directed to do so by your instructor and save it.
2. If the document opens in Protected View, click the Enable Editing button in the Message Bar at the top of the document so you can modify it.
3. Apply the Slice theme to the worksheet.
4. Edit worksheet data.
   1. Edit the title in cell A2 to display Monthly Sales by Department.
   2. Edit cell D6 to 1950.
5. Select cells A1:F2 and click the Alignment launcher [Home tab]. Click the Horizontal arrow, choose Center Across Selection, and click OK.
6. Select and delete row 8.
7. Use the Fill Handle to complete a series.
   1. Select cell B3.
   2. Use the Fill Handle to complete the series to Week 4 in column E.
   3. AutoFit columns C:E to display the complete label.
8. Use SUM and the Fill Handle to calculate totals.
   1. Use the AutoSum button to build a SUM function in cell F4.
   2. Use the Fill Handle to copy the formula in cell F4 to cells F5:F16.
   3. Delete the contents of cell F17 if you copied the formula to that cell.
   4. Select cells B17:F17 and click the AutoSum button. The SUM formula is inserted, and a Quick Analysis options button appears (Figure 1-98).Figure 1-98 AutoSum complete with Quick Analysis button
   5. Press Esc to ignore the Quick Analysis options.
9. Format labels and values.
   1. Select cells A1:A2 and increase the font size to 18.
   2. Increase the row height of rows 1:2 to 24 (32 pixels).
   3. Format cells A3:F3 as Bold and increase the row height to 18 (24 pixels).
   4. Center the data in cells B3:F3.
   5. Format cells B4:F16 with Comma Style and decrease the decimal two times.
   6. Select cells B17:F17 and apply the Accounting Number Format with no decimal places.
   7. Apply the All Borders format to cells A3:F17.
   8. AutoFit columns B:F.
10. Finalize the worksheet.
    1. Click the Insert tab and click the Header & Footer button [Text group].
    2. In the right header section, insert the Sheet Name field.
    3. Go to the footer and click the right section.
    4. Type [your first and last name] (Figure 1-99).Figure 1-99 Add a footer in Page Layout view
    5. Click a cell in the worksheet and then return to Normal view.
    6. Center the worksheet horizontally on the page.
11. Copy and rename a worksheet.
    1. Right-click the January sheet tab and choose Move or Copy.
    2. Make a copy of the sheet at the end.
    3. Rename the copied sheet February.
    4. Format the February sheet tab color to Dark Blue, Accent 1 (fifth column).
    5. Delete the values in cells B4:E16 and press Ctrl+Home.
    6. Return to the January sheet and press Ctrl+Home.
12. Preview the January sheet.
13. Save and close the workbook (Figure 1-100).Figure 1-100 Excel 1-4 completed
14. Upload and save your project file.
15. Submit project for grading.