Shelly Cashman Excel 365/2021 | Module 2: SAM Project 1b
Shelly Cashman Excel 365/2021 | Module 2: SAM Project 1b
|
2 |
Edgerton-Norris
Format Worksheets
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
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.
Middle Align the contents of cell A1 to improve the appearance of the worksheet title.
Format the billing section to show that the information belongs together as follows:
Add
Outside Borders to the range A2:F4.
Change the border color to
Green, Accent 4 (8th column, 1st row of the Theme Colors palette).
Apply the
Short Date number format to cell F3 to use a more common date format.
Go to the Invoice Tracker worksheet. Format the worksheet title in cell A1 to make it stand out as follows:
In cell A1, change the font to
Franklin Gothic Medium.
Change the font size to
18 point.
Change the font color to
White, Background 1.
Bold the worksheet title.
Merge the worksheet title across the range A1:I1.
Resize the width of column A and column B using
AutoFit to display all the text in the column.
Caleb asks you to find any duplicate entries and then delete them. Respond to his request as follows:
In the range A5:A16, use
Conditional Formatting Highlight Cells Rules to highlight Duplicate Values in
Light Red Fill with Dark Red Text.
Delete the row containing the first duplicate value.
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.
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:
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.
Wrap the contents of cell E4 to display the text on two lines.
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.
In cell A3, decrease the number of decimal places to 2 to format the value as a dollar amount.
Apply the
40%—Accent3 cell style to the range A4:I4 to indicate those cells contain column headings.
Apply the
Accounting number format to the range D5:D15 to show that the values are dollar amounts.
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.
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.
In cell B17, enter a formula using the
AVERAGE function to calculate the average invoice amount (range
D5:D15).
In cell B18, enter a formula using the
MAX function to calculate the maximum invoice amount (range
D5:D15).
In cell B19, enter a formula using the
MIN function to calculate the minimum invoice amount (range
D5:D15).
Check the Spelling in the workbook to identify and correct any spelling errors.
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.
