Microsoft Office Tutorials and References
In Depth Information
Practice the skills
you learned in
the tutorial using
the same case
Data Files needed for the Review Assignments: X410.xlsx, Paper.jpg
ExerComp introduced another heart rate monitor, the X410, two years ago. Tom wants
you to format a workbook that compares the sales of the X310 and X410 models during
that time. The workbook already has a Documentation sheet, a Model Comparison sheet
comparing the total units sold for each model in the eight sales regions, and a Monthly
Sales sheet reporting the number of units sold per month.
In the Model Comparison sheet, you will highlight the sales regions that showed the
greatest sales increases from 2011 to 2012. In the Monthly Sales sheet, you will calculate
the monthly sales totals for both models during 2011 and 2012. Complete the following:
1. Open the X410 workbook located in the Excel2\Review folder included with your
Data Files, and then save the workbook as X410 Sales Comparison .
2. In the Documentation sheet, enter your name in cell B4 and the date in the format
yyyy in cell B5. dd/
3. In the Documentation sheet, set the font color of cells A1 and A2 to blue, format
the text in cell A1 in a 26-point Times New Roman font, and then format the text in
cell A2 in a 10-point italicized Times New Roman font. In cell A1, change the font
color of the text “Comp” to red.
4. In the range A4:A6, set the font color to white and set the ﬁ ll color to blue. In the
range B4:B6, set the ﬁ ll color to white. In the range A4:B6, add border lines around
all of the cells.
5. In cell B5, display the date with the Long Date format and left-align it within the cell.
6. In the Documentation sheet, insert a background image using the Paper.jpg image
ﬁ le located in the Excel2\Review folder included with your Data Files.
7. Use the Format Painter to copy the format from the range A1:A2 in the
Documentation sheet to the range A1:A2 in the other two sheets. In cell A1, change
the font color of the text “Comp” to red.
8. Go to the Model Comparison sheet. In cells E6 and E17, enter Increase . In
ranges E7:E14 and E18:E25, enter formulas to calculate the increase in sales for
9. In cells F6 and F17, enter the text % Increase . In ranges F7:F14 and F18:F25, enter
formulas to calculate the percent increase in sales for each region.
10. In cells B15 and B26, enter the text Total . In cells C15, D15, C26, and D26, enter
formulas to calculate the total units sold per year for each model. In cells E15 and
E26, enter formulas to calculate the total increase in sales. In cells F15 and F26,
enter formulas to calculate the percent increase in total sales from 2011 to 2012.
11. Merge and center the range A6:A15, center the text vertically, and then change the
orientation to vertical text. Center the text in the range C6:F6, and then indent the
region labels in the range B7:B14 one character.
12. In the range C7:E15, format the numbers in a Number format using a thousands
separator, no decimal places, and negative numbers displayed with a minus symbol. In the
range F7:F15, format the numbers in a Percentage format with two decimal places.
13. Apply the Accent1 cell style to the range B6:F6 and the merged cell A6. Change the
font of cell A6 to 18 points and bold. Apply the Total cell style to the range B15:F15.
14. In the range E7:E14, apply a conditional format that adds a Top/Bottom Rule to
display the highest number in the range in dark green text with a green ﬁ ll. In the
range F7:F14, apply a conditional format that adds a Top/Bottom Rule to display the
highest number in the range in dark red text with a light red ﬁ ll.
15. Use the Format Painter to copy all of the formats from the range A6:F15 to the