GETTING STARTED
 Save the file SC_EX365_2021_2b_FirstLastName_1.xlsx as
SC_EX365_2021_2b_FirstLastName_2.xlsx
 Edit the file name by changing “1” to “2”.
 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.
 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 EdgertonNorris, 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. Cashman Excel 365/2021 | Module 2: SAM Project 1b 7. Caleb asks you to find any duplicate entries and then delete them. Respond to
8. his request as follows:
9. a. In the range A5:A16, use Conditional Formatting Highlight Cells
10. Rules to highlight Duplicate Values in Light Red Fill with Dark Red
11. Text.
12. b. Delete the row containing the first duplicate value.
13. 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.
14. 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.
15. 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.
16. In cell A3, decrease the number of decimal places to 2 to format the value as a
    dollar amount.
17. Apply the 40%—Accent3 cell style to the range A4:I4 to indicate those cells
    contain column headings.
18. Apply the Accounting number format to the range D5:D15 to show that the
    values are dollar amounts.
19. 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.
20. 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.
21. In cell B17, enter a formula using the AVERAGE function to calculate the
    average invoice amount (range D5:D15).
22. In cell B18, enter a formula using the MAX function to calculate the maximum
    invoice amount (range D5:D15).
23. In cell B19, enter a formula using the MIN function to calculate the minimum
    invoice amount (range D5:D15).
24. Check the Spelling in the workbook to identify and correct any spelling errors.
25. 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.