Shelly Cashman Excel 365/2021 | Module 2: SAM Project 1b |Edgerton-Norris (2025)

Shelly Cashman Excel 365/2021 | Module 2: SAM Project 1b

Edgerton-Norris
FORMAT WORKSHEETS

GETTING STARTED
• Save the file SC_EX365_2021_2b_FirstLastName_1.xlsx as SC_EX365_2021_2b_FirstLastName_2.xlsx
o Edit the file name by changing “1” to “2”.
o If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
• With the file SC_EX365_2021_2b_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
o If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS
1. Caleb Ramos is a manager in the Accounts Receivable Department of Edgerton-Norris, a pharmaceutical company in Minneapolis, Minnesota. Caleb has started to create an Excel workbook to produce and track client invoices. He asks you to format the workbook to make the information clearer and easier to understand.

Go to the Invoice worksheet. Change the name of the worksheet tab to Current Invoice to use a more descriptive name.
2. Middle Align the contents of cell A1 to improve the appearance of the worksheet title.
3. Format the billing section to show that the information belongs together as follows:
a. Add Outside Borders to the range A2:F4.
b. Change the border color to Green, Accent 4 (8th column, 1st row of the Theme Colors palette).
4. Apply the Short Date number format to cell F3 to use a more common date format.
5. Go to the Invoice Tracker worksheet. Format the worksheet title in cell A1 to make it stand out as follows:
a. In cell A1, change the font to Franklin Gothic Medium.
b. Change the font size to 18 point.
c. Change the font color to White, Background 1.
d. Bold the worksheet title.
e. Merge the worksheet title across the range A1:I1.
6. Resize the width of column A and column B using AutoFit to display all the text in the column.
7. Caleb asks you to find any duplicate entries and then delete them. Respond to his request as follows:
a. In the range A5:A16, use Conditional Formatting Highlight Cells Rules to highlight Duplicate Values in Light Red Fill with Dark Red Text.
b. Delete the row containing the first duplicate value.
8. Caleb wants to keep a running count of the invoices sent to clients.

In cell B3, enter a formula that uses the COUNTA function to count the values in the range A5:A15 to determine the number of invoices.
9. Caleb wants to display all the text in cell E4, but format it so that it requires less horizontal space. Make this formatting change as follows:
a. Change the width of column E to 9.00, which is wide enough to display the characters in each word of the column heading in cell E4.
b. Wrap the contents of cell E4 to display the text on two lines.
10. Apply shading to the ranges A2:C3 and A17:B19 using Green, Accent 3, Lighter 80% (7th column, 2nd row of the Theme Colors palette) to separate these ranges from the rest of the worksheet.
11. In cell A3, decrease the number of decimal places to 2 to format the value as a dollar amount.
12. Apply the 40%—Accent3 cell style to the range A4:I4 to indicate those cells contain column headings.
13. Apply the Accounting number format to the range D5:D15 to show that the values are dollar amounts.
14. Caleb wants to know at a glance which invoices are for the most amounts.

In the range D5:D15, use Conditional Formatting to create a Data Bars rule with the Gradient Fill Green Data Bar color option.
15. Caleb also wants to highlight the invoices that have not been paid.

In the range F5:F15, use Conditional Formatting Highlight Cells Rules to format cells containing a cell value equal to N in Light Red Fill with Dark Red Text.
16. In cell B17, enter a formula using the AVERAGE function to calculate the average invoice amount (range D5:D15).
17. In cell B18, enter a formula using the MAX function to calculate the maximum invoice amount (range D5:D15).
18. In cell B19, enter a formula using the MIN function to calculate the minimum invoice amount (range D5:D15).
19. Check the Spelling in the workbook to identify and correct any spelling errors.
20. Change the tab color of the Invoice Tracker worksheet to Green, Accent 3 (7th column, 1st row of the Theme Colors palette) to match the color of the Current Invoice worksheet tab.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.
 
Final Figure 1: Current Invoice Worksheet

Final Figure 2: Invoice Tracker Worksheet