Independent Project $1-4$

As a staff member at Blue Lake Sporting Goods, you prepare a monthly sales worksheet. You edit and format data, complete calculations, and set page and print options. You also copy the sheet for next month$$s data.

$[$Student Learning Outcomes $1\mathrm{.}1,1\mathrm{.}2,1\mathrm{.}3,1\mathrm{.}4,1\mathrm{.}5,1\mathrm{.}8]$

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

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

Skills Covered in This Project

Open and save a workbook.

Choose a workbook theme.

Edit and format data.

Use the AutoSum button and the Fill Handle.

Apply alignment and font settings.

Adjust column width and row height.

Insert a footer.

Adjust page setup.

Add document properties.

Steps to complete This Project

Mark the steps as checked when you complete them.

Open the workbook BlueLake$-01.$xlsx start file. If the workbook opens in Protected View, click Enable Editing in the security bar.

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$.$

Apply the Office theme to the worksheet.

Edit worksheet data.

Edit the title in cell A$2$ to display First Period Sales by Department.

Edit the value in cell D$6$ to $1950.$

Unmerge cells and reformat data.

Select cells A$1$:A$2.$

Click the Merge & Center button arrow $[$Home tab, Alignment group$].$

Choose Unmerge Cells. The cells are no longer merged but the cells still have Center alignment.

Click the Align Left button $[$Home tab, Alignment group$].$

Change the font size to $16.$

Set the row height for rows $1$:$2$ to $24.$

Use the Fill Handle to complete a series.

Select cell B$3.$

Use the Fill Handle to complete the series to April in column E$.$

AutoFit columns C:E$.$

Use the AutoSum button and the Fill Handle to calculate values.

Select cell F$4$ and double$-$click the AutoSum button $[$Home tab, Editing group$].$ The suggested range is B$4$:E$4$ and the result is $13300.$

Double$-$click the Fill pointer for cell F$4$ to copy the function to cells F$5$:F$18.$

Select cell G$4.$

Click the AutoSum button arrow $[$Home tab, Editing group$]$ and choose Max.

Click and drag to select cells B$4$:E$4$ as the correct argument range $($Figure $1-107)$ and press Enter. Do not include a total in a range when looking for the maximum.

Select the range for the MAX function

Figure $1-107$ Select the range for the MAX function

Select cell H$4$ and use the MIN function to calculate the worst sales for cells B$4$:E$4.$

Select cells G$4$:H$4$ and double$-$click the Fill pointer at cell H$4.$

Delete the contents of cells G$18$:H$18.$

Select cell B$18,$ click the AutoSum button $[$Home tab, Editing group$],$ and accept the suggested range.

Select cell B$18$ and drag its Fill pointer to cell F$18.$ Ignore the Auto Fill Options button.

AutoFit columns that do not display the data.

Format labels and values.

Select cells B$4$:H$18$ and click the Accounting Number Format button $[$Home tab, Number group$].$

Decrease the decimal two times.

Select cells B$5$:H$17$ and press Ctrl$+1$ to open the Format Cells dialog box.

Click the Number tab. The Category list displays Custom because you decreased decimal places.

Select Accounting in the Category list on the Number tab.

Click the Symbol drop$-$down list and select None.

Click OK$.$ It is common practice to show currency symbols with the first and last rows in a dataset $($Figure $1-108).$

The dollar sign is the currency symbol in the first and last rows

Figure $1-108$ Accounting Number Format with and without currency symbols

Format cells A$3$:H$3$ as Bold with Center alignment.

Apply the All Borders format to cells A$3$:H$18.$

Insert a blank row at row $3$ and then increase the row height for row $4$ to $18.$ IMPORTANT NOTE: To ensure accurate grading, this step must be completed.

Select cells A$2$:H$2$ and choose Thick Bottom Border from the Border drop$-$down list.

Right$-$align cell A$19$ and apply Bold to cells A$19$:F$19.$

Increase the row height of row $19$ to $18.$

Select cells A$4$:H$4,$ press Ctrl and select cells A$19$:H$19,$ and apply Light Gray, Background $2$ as Fill Color.

AutoFit column widths if needed.

Insert a footer and scale the data to print.

Click the Insert tab and click the Header & Footer button $[$Text group$].$

In the left footer section, insert the Sheet Name field.

In the right footer section, insert the File Name field.

Click a worksheet cell and return to Normal view.

Open print preview in the Backstage view and scale the worksheet to fit a single page.

Add document properties.

Open the Info screen in the Backstage view.

Edit the Title property to display Sales by Department.

Edit the Status property to display First draft.

Save and close the workbook $($Figure $1-109).$