Description
Excel Module 2 Canvas Project Overview
In this project you will use the skills and knowledge you learned in this module and apply it to a real-world scenario. Watch the video below for a overview of the tasks and lessons you will learn this week to complete this assignment.
https://youtu.be/pLu5KRqUysQLinks to an external site.
Excel Module 2 Canvas Project Details
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.
Excel Module 2 Canvas Project Steps
Complete the steps below.
Download the Start File File:
Start_FileExMod2.xlsxDownload Start_FileExMod2.xlsx
Rename the Start_File workbook to Excel_Mod2_FirstLastName.xlsx and Save it in a location easily found by you. The Desktop is a good spot.
Example: Excel_Mod2_AndreAndersen.xlsx
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 (in the Number Format dropdown menu) 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%—Accent 3 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.
Save your changes, close the workbook, and then exit Excel. Your workbook should look like the following solution file.
Excel Module 2 Canvas Project Solution Review
Your document should look like the file below. Save your changes and then close Excel. Follow the directions below to submit your completed project.
SolutionFileCanvasProjectExcelMod2.pdf
