Shelly Cashman Excel 365 | Module 2: SAM Project B SouthWest Beverages
FORMAT WORKSHEETS
· Excel Projects Help
GETTING STARTED
1. Save the file SC_EX365_2B_FirstLastName_1.xlsx as SC_EX365_2B_FirstLastName_2.xlsx
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
2. With the file SC_EX365_2B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS
1. Sierra Rodriguez is the warehouse manager for SouthWest Beverages,a beverage distribution company in Tuscon, Arizonia. Sierra 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 SKU worksheet. Change the name of the worksheet tab to SKU List to use a more descriptive name.
2. Left Align the contents of the range A3:A13 to match the alignment of the other cells on the worksheet.
3. Format the list of SKUs to emphasize that the information belongs together as follows:
a. Apply Outside Borders to the range A1:C13.
b. Change the border color to Pink, Accent 3 (7th column, 1st row of the Theme Colors palette).
4. Apply the Short Date number format to cell B15 to use a more common date format.
5. In Page Layout view, enter Warehouse SKUs in the center section of the header to provide a meaningful header when Sierra 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:H1.
b. Change the font to Calibri Light.
c. Change the font size to 24 point.
d. Change the font color to Blue-Gray, Accent 6.
e. Bold the worksheet title.
7. Resize the width of column A using AutoFit to display the full text of the descriptions.
8. Sierra wants you to display the entire text in cell E7, but format it so it requires less horizontal space. Make this formatting change as follows:
a. Set the width of column E to 8.50, which is wide enough to display the characters in each word of the column heading in cell E7.
b. Wrap the contents of cell E7 to display the text on two lines.
9. Sierra asks you to find any duplicate entries and then delete them. Respond to her request as follows:
a. In the range A8:A18, 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 A4:B5 using Plum, Accent 1, Lighter 90% (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 A5 to 2.
12. Sierra wants to keep a running count of the items in inventory. In cell B5, enter a formula that uses the COUNTA function to count the values in the range A8:A17 to determine the number of items in inventory.
13. Apply the Pink, Accent 3 cell style to the range A7:H7 to indicate those cells contain column headings and to the range H3:H5 to indicate those cells contain row headings.
14. Apply the Accounting number format to the range F8:G17 to show that the values are dollar amounts. Resize the width of column G using AutoFit to display the full formatted values.
15. Sierra wants to know at a glance which items have the most inventory value. In the range G8:G17, use Conditional Formatting to create a Data Bars rule with the Solid Fill Blue Data Bar color option.
16. Sierra also wants to highlight the items that need to be reordered. In the range H8:H17, use Conditional Formatting Highlight Cells Rules to format cells containing a value equal to Reorder in Light Red Fill.
17. In cell I3, enter a formula using the AVERAGE function to display the average cost of beverages in the warehouse (range F8:F17).
18. In cell I4, enter a formula using the MIN function to display the cost of the cheapest beverage located in the warehouse (range F8:F17).
19. In cell I5, enter a formula using the MAX function to display the cost of the most expensive beverage located in the warehouse (range F8:F17).
20. Check the spelling in the workbook to identify and correct any spelling errors.
21. Change the tab color of the Inventory List worksheet to Plum, Accent 1 (5th column, 1st row of the Theme Colors palette), to match the color of the SKU List 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: SKU List Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 2: Inventory List Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.