help

10/11/22, 9:50 AM SIMnet - Excel 2021 In Practice - Ch 1 Independent Project 1-4

https://bconline.broward.edu/d2l/le/content/565904/fullscreen/15628675/View 1/3

Excel 2021 In Practice - Ch 1 Independent Project 1-4

COURSE NAME Fall 20231 CGS 1060 MASTER | SStankovic CGS1060C COMP LITERACY ONLINE LIVE 715796

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.1, 1.2, 1.3, 1.4, 1.5, 1.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.

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

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

3. Apply the Office theme to the worksheet.

4. Edit worksheet data.

a. Edit the title in cell A2 to display First Period Sales by Department.

b. Edit the value in cell D6 to 1950.

5. Unmerge cells and reformat data.

a. Select cells A1:A2.

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

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

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

e. Change the font size to 16.

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

6. Use the Fill Handle to complete a series.

a. Select cell B3.

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

Start Date:09/07/2212:00 AMUS/Eastern Due Date:10/12/2211:59 PMUS/Eastern End Date:10/12/2211:59 PMUS/Eastern

Print Info

Student Name:

Trias Yilo, Clementina Coromoto

Student ID: triac3

Username: triac3

10/11/22, 9:50 AM SIMnet - Excel 2021 In Practice - Ch 1 Independent Project 1-4

https://bconline.broward.edu/d2l/le/content/565904/fullscreen/15628675/View 2/3

c. AutoFit columns C:E.

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

a. Select cell F4 and double-click the AutoSum button [Home tab, Editing group]. The suggested range is B4:E4 and the result is 13300.

b. Double-click the Fill pointer for cell F4 to copy the function to cells F5:F18.

c. Select cell G4.

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

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

Figure 1-107 Select the range for the MAX function

f. Select cell H4 and use the MIN function to calculate the worst sales for cells B4:E4.

g. Select cells G4:H4 and double-click the Fill pointer at cell H4.

h. Delete the contents of cells G18:H18.

i. Select cell B18, click the AutoSum button [Home tab, Editing group], and accept the suggested range.

j. Select cell B18 and drag its Fill pointer to cell F18. Ignore the Auto Fill Options button.

k. AutoFit columns that do not display the data.

8. Format labels and values.

a. Select cells B4:H18 and click the Accounting Number Format button [Home tab, Number group].

b. Decrease the decimal two times.

c. Select cells B5:H17 and press command+1 to open the Format Cells dialog box.

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

e. Select Accounting in the Category list on the Number tab.

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

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

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

h. Format cells A3:H3 as Bold with Center alignment.

i. Apply the All Borders format to cells A3:H18.

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

k. Select cells A2:H2 and choose Thick Bottom Border from the Border drop-down list.

l. Right-align cell A19 and apply Bold to cells A19:F19.

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

n. Select cells A4:H4, press command and select cells A19:H19, and apply Light Gray, Background 2 as Fill Color.

o. AutoFit column widths if needed.

9. Insert a footer and scale the data to print.

10/11/22, 9:50 AM SIMnet - Excel 2021 In Practice - Ch 1 Independent Project 1-4

https://bconline.broward.edu/d2l/le/content/565904/fullscreen/15628675/View 3/3

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

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

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

d. Click a worksheet cell and return to Normal view.

e. Open print preview and scale the worksheet to fit a single page.

10. Add document properties.

a. Open the Properties dialog box and click the Summary tab.

b. Edit the Title property to display Sales by Department.

c. Click the Custom tab in the Properties dialog box. Find and choose Status in the Name: list. Type First draft in the Value: box and click Add. Click OK to close the Properties dialog box.

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

Figure 1-109 Excel 1-4 completed

12. Upload and save your project file.

13. Submit file for grading.