This project covers the following MOS Objectives:
Merge and unmerge cells (EX21-2.2.1)
Apply cell styles (EX21-2.2.7)
Format cells by using Format Painter (EX21-2.2.3)
Apply number formats (EX21-2.2.5)
Apply cell formats from the Format Cells dialog box (EX21-2.2.6)
Modify cell alignment, orientation, and indentation (EX21-2.2.2)
Fill cells by using Auto Fill (EX21-2.1.2)
Insert sparklines (EX21-2.4.1)
Define a named range (EX21-2.3.1)
Name a table (EX21-2.3.2)
Wrap text within cells (EX21-2.2.4)
Paste data by using special paste options (EX21-2.1.1)
Remove conditional formatting (EX21-2.4.3)
Apply built-in conditional formatting (EX21-2.4.2)
Insert and delete cells (EX21-2.1.4)
Clear cell formatting (EX21-2.2.8)
Insert and delete multiple columns or rows (EX21-2.1.3)
Steps to complete this project:
Mark the steps as checked when you complete them.
Open the start file: EX2021-MOS-Ch2. If the file opens in Protected View, click the Enable Editing button in the Message Bar at the top of the file so you can modify it.
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.
On the By Region worksheet, make the following formatting modifications:
Merge and center the tile across cells A1:E1.
Apply the Title style to the merged cells.
Use Format Painter to copy the formatting from cell A3 to cells A4:A7.
Apply the Accounting number format to cells B3:E8.
Decrease the decimal so the values are whole numbers with no cents (e.g., $ 236,878).
Apply a thick, top, blue border to cells B2:E2. Hint: Use the Format Cells dialog and select the thick line style that is the second up from the bottom in the right column in the Border tab, Style box. Use the Standard blue color.
Change the horizontal alignment for cells B2:E2 to Center.
On the By Region worksheet, complete the total row.
Use AutoFill to fill the formula from cell B8 to cells C8:E8.
Apply the Total cell style to cells A8:E8.
On the By Region worksheet and add Column Sparklines in F3:F7 for the data range B3:E7.
Create named ranges.
On the By Region worksheet, create named ranges for each of the columns in cells B2:E7. The names should come from the column header cells in row 2. There should be four named ranges created.
On the By Region worksheet, create a new named range from cell B8. Name the range: Region1Total
Change the name of Table1 on the Sales Data worksheet to: SalesDataTable
On the Sales Data worksheet, modify the following formatting:
Apply cell wrapping to cells F2 and G2.
Apply the Currency number format to cells G3:H37.
Copy cells A2:H2 from the Sales Data worksheet (the column headings) and paste the text values but not the formatting to cells A2:H2 on the Sales Analysis worksheet.
On the Sales Analysis worksheet, make the following formatting changes:
Remove the conditional formatting from cells F3:F38.
Apply conditional formatting to cells G3:G38 to highlight cells greater than 50. Use the Green Fill with Dark Green Text formatting option.
Apply data bar conditional formatting to cells H3:H38. Use the solid fill blue data bar style.
Setup the Sales Template worksheet:
On the Sales Template worksheet, insert a new cell at C1, shifting cells to the right.
In cell C1, type: Feb
Use AutoFill to extend the month series from cells B1:D1 through cell M1.
Select cells B1:M1 (the month headings) and apply bold formatting.
With cells B1:M1 still selected, angle the cell alignment counterclockwise to 45 degrees.
Delete cell A2, moving the existing cells up.
Clear the formatting from cells A2:A5.
Insert four new rows below row 2.
Copy cells B2:E2 from the By Region worksheet (the region headings).
Paste the copied cells into the Sales Template worksheet, cells A3:A6. Hint: You will need to transpose the text so the headings are pasted vertically instead of horizontally.
Clear the formatting from the pasted cells.
Save and close the file.
Upload and save your project file.
Submit project for grading.
