# A Skills Approach: Excel 2016 Chapter 1: Getting Started with Excel 2016.

A Skills Approach: Excel 2016 Chapter 1: Getting Started with Excel 2016.

Skill Review 1.1
The workbook for this project generates client bills from staff hours in multiple worksheets. In this project,
you will complete the worksheet for staff member Swinson to calculate the daily bill, the total billable hours
per week, and the total weekly bill. The worksheet for staff member Stevens has been completed. You may
use it for reference as necessary.
Skills needed to complete this project:
• Working in Protected View
• Navigating a Workbook
• Entering and Editing Text and Numbers in Cells
• Applying Number Formats
• Entering Dates and Applying Date Formats
• Inserting Data Using AutoFill
• Understanding Absolute and Relative References
• Entering Simple Formulas
• Calculating Totals with the Quick Analysis Tool
• Previewing and Printing a Worksheet
1. Open the start file EX2016-SkillReview-1-1. The file will be renamed automatically to include your
name. Change the project file name if directed to do so by your instructor, and save it.
2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of
the workbook so you can modify it.
3. Explore the workbook. If you accidentally make changes while exploring, press Ctrl + Z to undo the
change.
a. Click the worksheet tab labeled Stevens Hours.
b. If necessary, use the vertical scroll bar to scroll down so you can see both weeks of billable hours. (If
necessary, use the vertical scroll bar again to return to the top of the worksheet.)
c. Click cell B2 (the cell displaying the staff member’s last name, Stevens). This is the cell at the
intersection of column B and row 2.
i. Note that the column B and row 2 selector boxes highlight.
ii. Note that the status bar displays Ready, indicating that you are in Ready mode.
iii. On the Home tab, in the Number group, look at the Number Format box at the top of the group.
Note that the format for this cell is General.
iv. Double-click cell B2 to switch to Edit mode. Note that the status bar now displays Edit, and the
blinking cursor appears within the cell. If you needed to, you could edit the text directly in the
cell.
e. Press Enter twice to move to cell B4 (the cell displaying the staff member’s billable rate). This cell is
formatted with the Accounting Number Format number format.
i. Look in the Number Format box and note that the format for this cell is Accounting.
Step 1
start file
A Skills Approach: Excel 2016 Chapter 1: Getting Started with Excel 2016
2 | Page Skill Review 1.1 Last Updated 3/8/18
ii. On the Home tab, in the Styles group, look in the Cell Styles gallery, and note that the cell style
Currency is highlighted. (If the Cell Styles gallery is collapsed on your Ribbon, click the Cell Styles
button to display it.)
f. Click cell B9 (the cell displaying the number of hours for Monday, August 8). This cell is formatted
with the Comma Style number format.
i. Look in the Number Format box and note that the format for this cell is also Accounting.
ii. On the Home tab, in the Styles group, look in the Cell Styles gallery, and note that the cell style
Comma is highlighted for this cell. (If the Cell Styles gallery is collapsed on your Ribbon, click the
Cell Styles button to display it.)
iii. Note the style differences between cell B4 (Accounting Number Format) and cell B9 (Comma
Style format).
g. Click cell B7 (the cell displaying the date 8/8/2016). This cell is formatted using the Short Date
format. Note that the Number Format box displays Date.
h. Double-click cell B15.
i. Note that the status bar now displays Edit, indicating that you are in Edit mode.
ii. This cell contains a formula to calculate the daily bill for Monday, August 8: =B13*\$B\$4
iii. Note that cells B13 and B4 are highlighted with colors matching the cell references in the
formula.
iv. Note that the reference to cell B4 is an absolute reference (\$B\$4).
i. Press Esc to exit Edit mode.
j. Double-click cell B13.
i. Note that once again the status bar displays Edit, indicating that you are in Edit mode.
ii. This cell contains a formula using the SUM function to calculate the total billable hours for
Monday, August 8: =SUM(B9:B12)
iii. In this case, the SUM function uses a single argument B9:B12 to indicate the range of cells to
total.
iv. Note that the cell range B9:B12 is highlighted with the color matching the argument in the SUM
function formula.
v. Note that the reference to the cell range B9:B12 uses relative references.
k. Press Esc to exit Edit mode.
l. Press Tab to move to cell C13. Look in the formula bar and note that this cell contains a similar
formula to the one in cell B13: =SUM(C9:C12)
m. Press Right arrow key to move through cells D13 through H13. Note the formula in the formula
bar for each cell.
n. Did you notice that the cell references in the formulas in cells C13 through H13 all use relative
references?
A Skills Approach: Excel 2016 Chapter 1: Getting Started with Excel 2016
3 | Page Skill Review 1.1 Last Updated 3/8/18
4. Now you are ready to complete the worksheet for David Swinson. Navigate to the Swinson Hours
worksheet by clicking the Swinson Hours worksheet tab.
5. The staff member’s last name is spelled incorrectly. Navigate to cell B2 and edit the text so the last name
is spelled correctly (Swinson—with an i instead of an a). Use Edit mode.
a. Double-click cell B2.
b. Edit the text to: Swinson
c. Press Enter to accept your changes.
6. The billable rate amount is missing. Navigate to cell B4 and enter the rate. Use Ready mode.
a. Click cell B4.
b. Type: 175
c. Press Enter.
7. Modify the billable rate to use the Accounting Number Format.
b. On the Home tab, in the Number group, click the Accounting Number Format button.
8. The dates are missing from the timesheet. Enter the first date: August 8, 2016.
a. Click cell B7.
b. Type: 8/8/2016
c. Press Enter.
9. Use AutoFill to complete the dates in the timesheet.
a. Click cell B7.
b. Click the Fill Handle, and drag to cell H7. Release the mouse button.
10. Change the date format to the 8/8/2016 format.
a. The cell range B7:H7 should still be selected. If not, click cell B7, press and hold Shift key, click cell
H7, and then release the Shift key.
b. On the Home tab, in the Number group, expand the Number Format list, and click Short Date.
11. Use the Quick Analysis tool to enter total hours for each day.
a. Select cells B9:H12. Click cell B9, hold down the left mouse button, and drag the mouse to cell H12.
Release the mouse button. The cell range B9 through H12 should now appear selected.
b. The Quick Analysis tool button should appear near the lower right corner of the
selected cell range. (If the Quick Analysis tool button is not visible, move your mouse cursor over the
selected cell range again, without clicking. This action should make the button appear.)
c. Click the Quick Analysis tool button, and then click Totals.
d. Click Sum (the first option).
12. Format the hours billed section to use the Comma Style number format. Be sure to include the
total row.
A Skills Approach: Excel 2016 Chapter 1: Getting Started with Excel 2016
4 | Page Skill Review 1.1 Last Updated 3/8/18
a. Select cells B9:H13. Try another method: Click cell B9, press and hold Shift key, click cell H13, and
release the Shift key.
b. On the Home tab, in the Number group, click the Comma Style button.
13. Enter a formula in cell B15 to calculate the daily bill for Monday, August 8. The formula should calculate
the total billable hours for the day (cell B13) times the billable rate (B4).
a. Click cell B15.
b. Type: =
c. Click cell B13.
d. Type: *
e. Click cell B4.
f. Press F4 to change the cell reference B4 to an absolute reference (\$B\$4).
g. Press Enter.
h. The formula should look like this: =B13*\$B\$4
14. Use AutoFill to copy the formula to the remaining days in the timesheet.
a. Click cell B15 again.
b. Click the AutoFill handle. Hold down the left mouse button and drag to cell H15. Release the
mouse button.
c. The formulas in cells B15 through H15 should look like this:
Notice that when the AutoFill copied the formula, it updated the relative reference (B13) to reflect the
new column position, but it did not change the absolute reference (\$B\$4).
15. Now you can calculate the bill total for the week by summing the daily bill amounts. Enter a formula
using the SUM function with the cell range B15:H15 as the argument.
a. Click cell B16.
b. Type: =SUM(B15:H15)
c. Press Enter.
16. Preview how the worksheet will look when printed.
a. Click the File tab to open Backstage view.
b. Click Print to display the print preview.
c. Click the left arrow to close the Backstage view.
17. Save and close the workbook.
Step 2
Save
Step 3
Project

A Skills Approach: Excel 2016 Chapter 1: Getting Started with Excel 2016.