USA: +1(669)289-8683 
Sign in
Legal Staff Billable Hours LogEmployee Name Jervis
Employee Number
Billable Rate
Dates Worked
Hudson
Smith
Aturo
Proctor
Total Billable Hours Per Day
Brian
6287
Mon
Tue
3.5
3.5
Wed
Thu
Fri
0.5
3.5
Sat
Sun
2.25
2
2.25
2.5
2.25
2.25
3.5
2.25
3.5
1
2.25
3.5
Mon
Tue
Wed
Thu
Fri
Sat
3.5
3
2
1
2.25
1
3
1
4
2.5
1
2
Daily Bill
Bill Total for Week
Dates Worked
Hudson
Smith
Aturo
Proctor
Total Billable Hours Per Day
Daily Bill Total
Bill Total for Week
2
3
2.25
2
1
1
16
Sun
2
4
Legal Staff Billable Hours Log
Employee Name Stevens
William
Employee Number
4567
Billable Rate $
125.00
Dates Worked
Hudson
Smith
Aturo
Proctor
Total Billable Hours Per Day
Daily Bill $
Bill Total for Week $
Dates Worked
Hudson
Smith
Aturo
Proctor
Total Billable Hours Per Day
Daily Bill Total $
Bill Total for Week $
Mon
8/1/2022
3.50
Tue
8/2/2022
3.50
2.50
1.00
2.25
9.25
2.00
2.25
7.75
968.75 $
6,593.75
Mon
8/8/2022
Tue
8/9/2022
3.50
2.00
3.00
2.25
7.25
906.25
6,843.75
1,156.25
Wed
8/3/2022
3.50
968.75
Fri
8/5/2022
3.50
Sat
8/6/2022
3.50
Sun
8/7/2022
3.50
0.50
2.25
6.25
$
781.25 $
Wed
8/10/2022
2.00
3.00
2.25
7.25
2.00
2.25
7.75
$
Thu
8/4/2022
3.50
$
906.25 $
3.50
2.25
9.25
1,156.25
1.00
2.25
6.75
$
Thu
8/11/2022
1.00
1.00
1.00
6.00
9.00
1,125.00 $
843.75
2.00
2.25
7.75
$
968.75
2.25
5.75
$
718.75
Fri
8/12/2022
2.50
Sat
8/13/2022
2.50
Sun
8/14/2022
2.50
1.00
6.00
9.50
1.00
6.00
9.50
2.00
1,187.50 $
1,187.50
4.50
$
562.50
10.00
9.00
8.00
7.00
6.00
5.00
4.00
3.00
2.00
1.00
–
Week of 8/1/22
10.00
9.00
8.00
7.00
6.00
5.00
4.00
3.00
2.00
1.00
–
Hudson
Smith
Aturo
Proctor
1.
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 the workbook.
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.
d. Press Esc to exit Edit mode and return to Ready mode.
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.
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 B8 (the cell displaying the number of hours for Monday, August 1). This
cell is formatted with the Comma cell style.
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, Currency style) and cell B8 (Accounting Number Format,
Comma style).
g. Click cell B7 (the cell displaying the date 8/1/2022). This cell is formatted using the
Short Date format. Note that the Number Format box displays Date.
h. Double-click cell B14.
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 1: =B12*$B$4
iii.
Note that cells B12 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.
j.
Press Esc to exit Edit mode.
Double-click cell B12.
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 1: =SUM(B8:B11)
iii.
In this case, the SUM function uses a single argument B8:B11 to
indicate the range of cells to total.
iv.
Note that the cell range B8:B11 is highlighted with the color matching
the argument in the SUM function formula.
v.
Note that the reference to the cell range B8:B11 uses relative
references.
k. Press Esc to exit Edit mode.
l. Press Tab to move to cell C12. Look in the formula bar and note that this cell
contains a similar formula to the one in cell B12: =SUM(C8:C11)
m. Press → to move through cells D12 through H12. Note the formula in the formula
bar for each cell.
n. Did you notice that the cell references in the formulas in cells C12 through H12 all
use relative references?
4.
5.
6.
7.
8.
9.
The Stevens Hours worksheet contains a stacked column chart. Review the chart so you can
recreate it on the Jarvis Hours worksheet.
a. The stacked column chart to the right of the hours for the week of August 1
represents the hours worked for each client each day.
b. The column height for each day represents the total hours worked. Each column is
divided into segments representing the hours worked for each client.
c. The number of hours worked is represented on the y axis. The dates are
represented along the x axis.
d. The chart title has been changed to: Week of 8/1/22
Now you are ready to complete the worksheet for Brian Jarvis. Navigate to the Jarvis
Hours worksheet by clicking the Jarvis Hours worksheet tab.
The staff member’s last name is spelled incorrectly. Navigate to cell B2 and edit the text so the
last name is spelled correctly (Jarvis– with an a instead of an e). Use Edit mode.
a. Double-click cell B2.
b. Edit the text to: Jarvis
c. Press Enter to accept your changes.
The billable rate amount is missing. Navigate to cell B4 and enter the rate. Use Ready mode.
a. Click cell B4.
b. Type: 180
c. Press Enter.
Modify the billable rate to use the Accounting Number Format.
a. Press the ↑ to return to cell B4.
b. On the Home tab, in the Number group, click the Accounting Number
Format button.
The dates are missing from the timesheet. Enter the first date, August 1, 2022.
a. Click cell B7.
b. Type: 8/1/2022
c. Press Enter.
10. 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.
11. Change the date format to the 8/1/2022 format.
a. The cell range B7:H7 should still be selected. If not, click cell B7, press and
hold Shift, 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.
12. Use the Quick Analysis tool to enter total hours for each day.
a. Select cells B8:H11. Click cell B8, hold down the left mouse button, and drag the
mouse to cell H11. Release the mouse button. The cell range B8 through H11
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).
13. Format the hours billed section to use the Comma Style number format. Be sure to include the
total row.
a. Select cells B8:H12. Try another method: Click cell B8, press and hold Shift, click
cell H12, and release the Shift key.
b. On the Home tab, in the Number group, click the Comma Style button.
14. Enter a formula in cell B14 to calculate the daily bill for Monday, August 5. The formula should
calculate the total billable hours for the day (cell B12) times the billable rate (B4).
a. Click cell B14.
b. Type: =
c. Click cell B12.
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: =B12*$B$4
15. Use AutoFill to copy the formula to the remaining days in the timesheet.
a. Click cell B14 again.
b. Click the AutoFill handle. Hold down the left mouse button and drag to cell H14.
Release the mouse button.
c. The formulas in cells B14 through H14 should look like this.
Notice that when the AutoFill copied the formula, it updated the relative reference
(B12) to reflect the new column position, but it did not change the absolute
reference ($B$4).
Table EX 1.5 displays the relative and absolute references for the remaining days in the timesheet.
B
C
D
E
F
G
H
14
=B12*$B$4
=C12*$B$4
=D12*$B$4
=E12*$B$4
=F12*$B$4
=G12*$B$4
=H12*$B$4
16. 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 B14:H14 as the argument.
a. Click cell B15.
b. Type: =SUM(B14:H14)
c. Press Enter.
17. Use the Recommended Charts feature to insert a stacked column chart representing hours
worked for each day for the week of August 1.
a. Select cells A7:H11. Click cell A7, hold down the left mouse button, and drag the
mouse to cell H11. Release the mouse button. The cell range A7 through H11
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 Charts.
d. Click Stacked Column to insert a stacked column chart.
18. Change the chart title.
a. Click the Chart Title placeholder once to select it.
b. Type: Week of 8/1/2022
c. Press Enter.
19. Preview how the worksheet will look when printed.
a. Click cell A1 to deselect the chart.
b. Click the File tab to open Backstage view.
c. Click Print to display the print preview.
d. Click the left arrow to close the Backstage view.
20. Save and close the workbook.
21. Upload and save your project file.
22. Submit project for grading.
