Microsoft Office Tutorials and References
In Depth Information
16. In cell D3, enter highest , and then apply a conditional format to cell D3 that adds a
Highlight Cells Rule to format the cell that contains the text “highest” with Green Fill
with Dark Green Text. In cell D4, enter highest , and then apply a conditional format
to cell D4 that adds a Highlight Cells Rule to format the cell that contains the text
“highest” with Light Red Fill with Dark Red Text.
17. In cell E3, enter Highest increase in units sold . In cell E4, enter Highest % increase
in units sold . Format both cells with the Explanatory Text cell style.
18. Go to the Monthly Sales sheet. In cells D5 and I5, enter the text All Models . In cells
A18 and F18, enter the text Total .
19. Enter formulas in the worksheet to calculate the total sales by month, by model, and
then over all months and over both models.
20. Merge and center the range A4:D4, merge and center the range F4:I4, and then apply
the Heading 1 style to both merged cells. In the range B5:D5;G5:I5, center the text.
21. In the nonadjacent range B6:D18;G6:I18, format the numbers to show a thousands
separator (,) with no decimal places to the right of the decimal point.
22. Select the range A5:D18, and then apply Table Style Light 8 (the ﬁ rst table style in
the second row of the Light section in the Table Styles gallery). Turn off the ﬁ lter
arrows, and then display only the header row, ﬁ rst column, and last column table
style options. In the range A18:D18, apply the Total cell style.
23. Select the range F5:I18, and then repeat Step 22, applying the Total cell style to the
24. For the Model Comparison and Monthly Sales worksheets, set the page orientation
to landscape, display your name in the center section of the header, display the sheet
name in the left section of the footer, display the workbook ﬁ lename in the center
section of the footer, and then display the current date in the right section of the footer.
25. Save and close your workbook. Submit the ﬁ nished workbook to your instructor,
either in printed or electronic form, as requested.
If you have a SAM 2010 user profile, your instructor may have assigned an
autogradable version of this assignment. If so, log into the SAM 2010 Web site at
www.cengage.com/sam2010 to download the instructions and start files.
Case Problem 1
Data File needed for the Case Problem: Frosti.xlsx
FrostiWear Linda Young is a sales manager for FrostiWear, a successful new store based
in Hillsboro, Oregon. She’s tracking the sales ﬁ gures for FrostiWear’s line of gloves. She
created a workbook that contains the sales ﬁ gures from the past year for three glove
models. She wants you to help format the sales report. Complete the following:
1. Open the Frosti workbook located in the Excel2\Case1 folder included with your
Data Files, and then save the workbook as FrostiWear Sales Report .
2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4. Set
the background color for all the cells in the worksheet to standard blue, and then set
the background color for the range B3:B5 to white. Add a border line around each
cell in the range B3:B5.
3. Change the font of cell A1 to the Headings font of the current theme, change the font
size to 36 points, change the font color to white, and then bold the text. Change the
font size of the range A3:A5 to 16 points, change the font color to white, and then
bold the text.
4. Go to the Glove Sales worksheet. Add formulas to calculate the total sales for each
month and region over the three glove models.
5. Merge and center the range A1:H1, apply the Title cell style, and then increase the
font size to 26 points. Merge and center the range A2:H2, apply the Heading 4 cell
style, and then increase the font size to 16 points.