Microsoft Office Tutorials and References
In Depth Information
Learn It Online
1. Start Excel. Open the workbook Apply 2-1 Village of Scott Police Department. See the inside
back cover of this topic for instructions for downloading the Data Files for Students or see your
instructor for information on accessing the i les required in this topic.
2. Use the following formulas in cells E4, F4, and G4:
Mileage Cost (cell E4) = Miles Driven * Cost per Mile or = B4 * C4
Total Cost (cell F4) = Maintenance Cost + Mileage Cost or = D4 + E4
Total Cost per Mile (cell G4) = Total Cost / Miles Driven or = F4 / B4
Use the i ll handle to copy the three formulas in the range E4:G4 to the range E5:G12.
3. Determine totals for the miles driven, maintenance cost, mileage cost, and total cost in row 13.
Copy the formula in cell G12 to G13 to assign the formula in cell G12 to G13 in the total line. If
necessary, reapply the Total cell style to cell G13.
4. In the range B14:B16, determine the highest value, lowest value, and average value, respectively,
for the values in the range B4:B12. Use the i ll handle to copy the three functions to the range
5. Format the worksheet as follows:
a. change the workbook theme to Foundry by using the Themes button
(Page Layout tab | Themes group)
b. cell A1 — change to Title cell style
c. cell A2 — change to a font size of 16
d. cells A1:A2 — Rose background color and a thick box border
e. cells C4:G4 and D13:G13 — Accounting number format with two decimal places and i xed
dollar signs by using the Accounting Number Format button (Home tab | Number group)
f. cells C5:G12 — Comma style format with two decimal places by using the Comma Style button
(Home tab | Number group)
g. cells B4:B16 — Comma style format with no decimal places
h. cells C14:G16 — Currency style format with l oating dollar signs by using the Format Cells:
Number Dialog Box Launcher (Home tab | Number group)
i. cells G4:G12 — apply conditional formatting so that cells with a value greater than 0.80 appear
with a rose background color
6. Switch to Page Layout View and delete any current text in the Header area. Enter your name,
course, laboratory assignment number, and any other information, as specii ed by your instructor,
in the Header area. Preview and print the worksheet in landscape orientation. Change the
document properties, as specii ed by your instructor. Save the workbook using the i le name, Apply
2-1 Village of Scott Police Department Complete.
7. Use Range Finder to verify the formula in cell G13.