·
Open the file SC_EX19_2a_FirstLastName_1.xlsx,
available for download from the SAM website.
·
Save the file as SC_EX19_2a_FirstLastName_2.xlsx
by changing the “1” to a “2”.
o
If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The
program will add the file extension for you automatically.
·
With the file SC_EX19_2a_FirstLastName_2.xlsx
still open, ensure that your first and last name is displayed in cell B6 of the
Documentation sheet.
o
If cell B6 does not display your name, delete
the file and download a new copy from the SAM website.
PROJECT STEPS
1.
Shanice Traynor is the warehouse manager for the
Roadrunner Online warehouse in Detroit, Michigan. The warehouse specializes in
automobile parts, which consumers buy from Roadrunner Online. Shanice 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.
Bottom
Align the contents of the range C3:C13 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 Outside
Borders to the range A1:C13.
b.
Change the border color to Blue-Gray, 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 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.
In cell A1, change the font to Calibri Light.
b.
Change the font size to 20 point.
c.
Change the font color to Indigo, Text 2.
d.
Bold the worksheet title.
e.
Merge and
center the worksheet title across the range A1:I1.
7.
Resize the width of column B using AutoFit to display the full text of the
descriptions.
8.
Shanice asks you to find any duplicate entries
and then delete them. Respond to her request as follows:
a.
In the range A5: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.
9.
Shanice wants to keep a running count of the
items in inventory.
In cell B3, enter a formula that uses the COUNTA
function to count the values in the range B5:B15
to determine the number of items in inventory.
10.
Shanice wants to display the entire text in cell
F4, 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 F4.
b.
Wrap the contents of cell F4 to display the text
on two lines.
11.
Apply shading to the range A2:B3 using Aqua, Accent 4, Lighter 60% (8th
column, 3rd row of the Theme Colors palette) to separate the range from the
rest of the worksheet.
12.
To format the value as a dollar amount, decrease
the number of decimal places in cell A3 to 2.
13.
Apply the Accent3
cell style to the range A4:I4 to indicate those cells contain column headings.
14.
Apply the Accounting
number format to the range G5:H15 to show that the values are dollar amounts.
15.
Shanice wants to know at a glance which items
have the most inventory value.
In the range H5:H15, use Conditional Formatting to create a Data Bars rule with the Gradient Fill Blue Data Bar color
option.
16.
Shanice also wants to highlight the items that
need to be reordered.
In the range I5:I15, use Conditional Formatting Highlight Cells Rules to format cells containing a value equal to Reorder in Light Red Fill with Dark Red Text.
17.
Check the Spelling in the workbook to identify
and correct any spelling errors.
18.
Change the tab color of the Inventory List worksheet to Blue-Gray,
Accent 3 (7th column, 1st row of the Theme Colors palette), to match the
color of the Bin 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 SAM website to submit your completed project.
Get Free Quote!
427 Experts Online