Shelly Cashman Excel 365 Module 2 SAM Project A | Shelly Cashman Excel 365 Module 2

Contact Us
Whatsapp No : +994-401583314

Shelly Cashman Excel 365 | Module 2: SAM Project A

ComputerWare Online
FORMAT WORKSHEETS

GETTING STARTED
• Save the file SC_EX365_2A_FirstLastName_1.xlsx as SC_EX365_2A_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_2A_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. Maya Morales is the warehouse manager for the ComputerWare Online warehouse in Madison, Wisconsin. The warehouse specializes in computer parts, which consumers buy from ComputerWare Online. Maya has started to create an Excel workbook to track items in the warehouse inventory. She asks you to format the workbook to make the information clearer and easier to understand before she finishes entering the data.

Go to the Bin worksheet. Change the name of the worksheet tab to Bin List to use a more descriptive name.
2. Center Align the contents of the range C3:C14 to match the alignment of the other cells on the worksheet.
3. Format the list of bins to emphasize that the information belongs together as follows:
a. Apply Thick Outside Borders to the range A1:C14.
b. Change the border color to Orange, Accent 3 (7th column, 1st row of the Theme Colors palette).
4. Apply the Short Date number format to cell B16 to use a more common date format.
5. In Page Layout view, enter Warehouse Bins in the center section of the header to provide a meaningful header when Shanice prints the worksheet.
6. Go to the Inventory List worksheet. To make it stand out, format the worksheet title in cell A1 as follows:
a. Merge and center the worksheet title across the range A1:I1.
b. Change the font to Calibri Light.
c. Change the font size to 22 point.
d. Change the font color to Aqua, Accent 1.
e. Bold the worksheet title.
7. Resize the width of column B using AutoFit to display the full text of the descriptions.
8. Maya wants to display the entire text in cell F3, but format it so it requires less horizontal space. Make this formatting change as follows:
a. Set the width of column F to 9.00, which is wide enough to display the characters in each word of the column heading in cell F3.
b. Wrap the contents of cell F3 to display the text on two lines.
9. Maya asks you to find any duplicate entries and then delete them. Respond to her request as follows:
a. In the range A4: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 second duplicate value.
10. Apply shading to the range A17:B18 using Aqua, Accent 1, Lighter 80% (5th column, 2nd row of the Theme Colors palette) to separate the range from the rest of the worksheet.
11. To format the value as a proper dollar amount, decrease the number of decimal places in cell A18 to 2.
12. Maya wants to keep a running count of the items in inventory.

In cell B18, enter a formula that uses the COUNTA function to count the values in the range B4:B15 to determine the number of items in inventory.
13. Apply the Accent 6 cell style to the range A3:I3 to indicate those cells contain column headings and to the range E16:E18 to indicate those cells contain row headings.
14. Apply the Accounting number format to the range G4:H15 to show that the values are dollar amounts.
15. Maya wants to know at a glance which items have the most inventory value.

In the range H4:H15, use Conditional Formatting to create a Data Bars rule with the Gradient Fill Light Blue Data Bar color option.