|
|
Tasks
|
Correct Answers
|
|
|
1.
|
Insert
cells above row 13 for a new category and four columns of numbers.
[1]
|
1. Select cells
A13:E13.
2. Click Insert on the
menu bar
3. Click Cells
4. Click Shift cells
down
5. Confirm the dialog
box
1. Select cells
A13:E13
2. Press CTRL+PLUS (+)
on the keypad
3. Click Shift cells
down
4. Confirm the dialog
box
- Using a Mouse Right-Click
1. Select cells
A13:E13
2. Right-click
3. Click Insert
4. Click Shift cells
down
5. Confirm the dialog
box
1. Select cells
A13:E13.
2. Press
CTRL+SHIFT+PLUS SIGN(+)
3. Click Shift cells
down
4. Confirm the dialog
box
|
|
 
|
|
|
2.
|
Remove
the cells containing Lobbying and all the Lobbying expenses (A13:E13). Do
not leave blank cells there.
[2]
|
1. Select cells
A13:E13
2. Click Edit on the
menu bar
3. Click Delete
4. Click Shift cells
up
5. Confirm the dialog
box
1. Select cells
A13:E13
2. Press CTRL+ HYPHEN
(or MINUS on the keypad)
3. Click Shift cells
up
4. Confirm the dialog
box
- Using a Mouse Right-Click
1. Select cells
A13:E13
2. Right-click
3. Click Delete on the
shortcut menu
4. Click Shift cells
up
5. Confirm the dialog
box
|
|
|
|
|
3.
|
Move
the title Assumptions and all the contents in the box down to cell G8.
[5]
|
1. Select cells
G2:I12.
2. Click Edit on the
menu bar.
3. Click Cut.
4. Move the active
cell to G8.
5. Press the ENTER key
(or click Paste on the Edit menu).
1. Select cells G2:I12
2. Press CTRL+X
3. Move the active
cell to G8
4. Press the ENTER key
(or press CTRL+V)
1. Select cells G2:I12
2. Click the Cut
button on the Standard toolbar
3. Make G8 the active
cell
4. Press the ENTER key
(or click the Paste button)
- Using a Mouse Right-Click
1. Select cells G2:I12
2. Right-click and
click Cut
3. Make G8 the active
cell
4. Press the ENTER key
(or right-click and click Paste)
1. Select cells G2:I12
2. Place the mouse
pointer over the border of the selection so that the mouse pointer turns
into a four-headed arrow
3. Drag downward until
the selected cells reach G8:I18
1. Select cells G2:I12
2. Place the mouse
pointer over the border of the selection so that the mouse pointer turns
into a four-headed arrow
3. Right-click and
drag downward until the selected cells reach G8:I18
4. Click Move Here
|
|
|
|
|
4.
|
Edit
the text in cell A2 so that the year is 2004.
[6]
|
1. Select A2
2. Press F2
3. Press the BACKSPACE
key
4. Type 4
5. Press the ENTER key
1. Select cell A2
2. Click to the right
of 2002 in the formula bar
3. Press the BACKSPACE
key
4. Type 4
5. Click the Enter
button on the formula bar (or press the ENTER key)
1. Double-click cell
A2 to the right of 2002.
2. Press the BACKSPACE
key
3. Type 4
4. Click any other
cell on the worksheet. (or press the ENTER key)
|
|
|
|
|
5.
|
Change
the Grants for Q1 to 30,000.
[7]
|
1. Select cell B7
2. Press the F2 key
3. Move the insertion
point to the right of 5
4. Press the BACKSPACE
key twice
5. Type "30"
6. Press the ENTER key
(or click the Enter button on the formula bar)
1. Select cell B7
2. Double-click the
formula bar
3. Type 30000
4. Click the Enter
button on the formula bar (or press the ENTER key)
1. Select cell B7
2. Type
"30,000"
3. Press the ENTER key
1. Double-click cell
B7
2. Delete 25000
3. Enter 30000
4. Press the ENTER key
(or click the Enter button on the formula bar)
1. Select cell B7
2. Press the F2 key
3. Move the insertion
point to the left of 2
4. Press the Delete
key twice
5. Type "30"
6. Press the ENTER key
(or click the Enter button on the formula bar)
|
|
|
|
|
6.
|
Create
a formula in cell B18 that will subtract Total Expenses (B16) from Total
Revenue (B8).
[297]
|
1. Select cell B18
2. Type
"=B8-B16"
3. Confirm the formula
1. Select cell B18
2. Type "="
3. Select cell B8
4. Type "-"
5. Select cell B16
6. Confirm the formula
|
|
|
|
|
7.
|
The
formula in cell B18 is incorrect. Edit the formula so that it refers to
Total Revenue in cell B8 rather than B6.
[298]
|
1. Select cell B18
2. Click the formula
bar just to the right of B6
3. Press the Backspace
or the Delete key to erase the 6
4. Type 8
5. Confirm the formula
1. Double-click cell
B18
2. Click the formula
just to the right of B6
3. Press the Backspace
or Delete key to erase the 6
4. Type 8
5. Confirm the formula
1. Select cell B18
2. Press the F2 key
3. Click just to the
right of B6
4. Press the Backspace
or Delete key to erase the 6
5. Type 8
6. Confirm the formula
1. Select cell B18
2. Change to edit mode
(Press F2, double-click the cell, or click the formula bar)
3. Drag the blue Range
Finder box down to cell B8
4. Confirm the formula
|
|
|
|
|
8.
|
As
shown in cell H7, Administrative expenses are 20% of Total Revenue. Create
a formula in cell B11 to display the Administrative expense for Q1. Use an
absolute reference to cell H7 in the formula.
[300]
|
1. Select cell B11
2. Type =
3. Click cell H7
4. Press the F4 key
5. Type *
6. Click cell B8
7. Confirm the formula
1. Select cell B11
2. Enter =B8*$H$7
3. Confirm the formula
1. Select cell B11
2. Type =
3. Select cell B8
4. Type *
5. Select cell H7
6. Press the F4 key
7. Confirm the formula
|
|
|
|
|
9.
|
In
cell B18, create a formula using relative cell references that will
subtract Total Expenses from Total Revenue.
[301]
|
1. Select cell B18
2. Type = (either
directly in the cell or after clicking the formula bar)
3. Click cell B8
4. Type -
5. Click cell B16
6. Confirm the formula
1. Select cell B18
2. Enter
"=B8-B16" (either directly in the cell or after clicking the
formula bar)
3. Confirm the formula
|
|
|
|
|
10.
|
Total
the endowments listed in cells C4:C6. Place the total in cell C7.
[302]
|
1. Select cell C7
2. Click Insert on the
menu bar
3. Click Function
4. Click SUM in the
Select a function box
5. Confirm the dialog
box (or double-click Sum in step 4)
6. Enter C4:C6 in the
Number1 box (or collapse the dialog and drag to select c4:c6 on the sheet)
7. Confirm the dialog
box
1. Select cell C7
2. Enter =sum(
3. Drag to select
cells C4:C6
4. Confirm the formula
1. Select cell C7
2. Type
"=sum"
3. Press Ctrl+A
4. Confirm the formula
1. Select cell C7
2. Click the AutoSum
button on the Standard toolbar
3. Confirm the formula
(press Enter or click the check mark)
1. Select cell C7
2. Click the Insert
Function button on the formula bar
3. Click Sum in the
Select a function box
4. Confirm the dialog
box (or double-click in step 3)
5. Enter C4:C6 in the
Number1 box (or collapse the dialog and drag to select on the sheet)
6. Confirm the dialog
box
1. Select cell C7
2. Enter =sum(c4:c6)
3. Confirm the formula
1. Select cell C7
2. Enter =c4+c5+c6
3. Confirm the formula
1. Select cell C7
2. Enter =
3. Click cell C4
4. Enter +
5. Click cell C5
6. Enter +
7. Click cell C6
8. Confirm the formula
1. Select cells C4:C6
(or C4:C7)
2. Click the AutoSum
button
|
|
|
|
|
11.
|
Use
the MIN function to find the lowest program cost. Place the answer in cell
D19.
[303]
|
1. Select cell D19
2. Click Insert on the
menu bar
3. Click Function
4. Click All in the
Select a category box (or enter "lowest" in the Search for a
function box) (or click the Statistical category)
5. Click MIN
6. Confirm the dialog
box (or double-click in step 5)
7. Confirm the formula
(confirm the Function Arguments dialog box)
1. Select cell D19
2. Type
"=min"
3. Press CTRL+A
4. Confirm the dialog
box
1. Select cell D19
2. Click the AutoSum
button arrow on the Standard toolbar
3. Click MIN
4. Confirm the formula
1. Select cell D19
2. Click the Insert
Function button on the formula bar
3. Click All in the
Select a category box (or enter "lowest" in the Search for a
function box)
4. Click MIN
5. Confirm the dialog
box (or double-click in step 5)
6. Confirm the formula
(confirm the Function Arguments dialog box)
1. Select cell D19
2. Enter =min(D4:D18)
3. Confirm the formula
1. Select cell D19
2. Enter =min(
3. Select cells D4:D18
4. Confirm the formula
|
|
|
|
|
12.
|
In
cell D17, use the MAX function to find the most expensive program.
[304]
|
1. Select cell D17
2. Click Insert on the
menu bar
3. Click Function
4. Click MAX in the
Select a function list (or search for max, or click Statistical category,
or click All category to find MAX)
5. Confirm the dialog
box (or double-click in Step 4)
6. Confirm the
Function Arguments dialog box
1. Select cell D17
2. Type
"=max("
3. Select cells D3:D16
4. Confirm the formula
(press Enter key or click Enter button on the formula bar)
1. Select cell D17
2. Type
"=max"
3. Press CTRL+A
4. Confirm the dialog
box
1. Select cell D17
2. Click the AutoSum
button arrow on the Standard toolbar
3. Click MAX
4. Confirm the formula
1. Select cell D17
2. Click the Insert
Function button on the formula bar
3. Click MAX in the
Most Recently Used box
4. Confirm the dialog
box (or double-click in Step 2)
5. Confirm the
Function Arguments dialog box
1. Select cell D17
2. Type
"=max(D3:D16)"
3. Confirm the formula
(press Enter key or click Enter button on the formula bar)
1. Select cell D17
2. Type
"=max("
3. Drag to select the
range D3:D16.
4. Press the ENTER
key. (or type ')' and press and confirm the formula)
|
|
|
|
|
13.
|
Use
the DATE function to create a formula in cell D2 that will take the
information in cells A2:C2 and turn it into the date.
[305]
|
1. Select cell D2
2. Click Insert on the
menu bar
3. Click Function
4. Enter Date in the
Search for a function box (or click Date&Time
in Select a category, or click the ALL category)
5. Start the search
(click the Go button or press the ENTER key)
6. Click DATE
7. Confirm the dialog
box (or double-click in Step 6)
8. Enter A2 in the
Year box (or click A2 on sheet, or collapse dialog, click A2 and expand
dialog)
9. Enter B2 in the
Month box (or click B2 on sheet, or collapse, click B2, and expand)
10. Enter C2 in the Day
box (or click C2 on sheet, or collapse-click C2-and expand)
11. Confirm the dialog
box
1. Select cell D2
2. Type
"=date"
3. Press CTRL+A
4. Enter A2 in the
Year box (or click A2 on sheet, or collapse dialog, click A2 and expand
dialog)
5. Enter B2 in the
Month box (or click B2 on sheet, or collapse, click B2, and expand)
6. Enter C2 in the Day
box (or click C2 on sheet, or collapse-click C2-and expand)
7. Confirm the dialog
box
1. Select cell D2
2. Click the AutoSum
button arrow on the Standard toolbar
3. Click More
Functions
4. See steps 4-7 under
Main Menu Pathway-1 (search for Date, look in All category, look in Date
& Time category)
5. Click DATE
6. Confirm the dialog
box (or double-click in step 5)
7. Enter A2 in the
Year box (or click A2 on sheet, or collapse dialog, click A2 and expand
dialog)
8. Enter B2 in the
Month box (or click B2 on sheet, or collapse dialog, click A2 and expand
dialog)
9. Enter C2 in the Day
box (or click C2 on sheet, or collapse dialog, click A2 and expand dialog)
10. Confirm the dialog
box
1. Select cell D2
2. Click Insert
Function button on the formula bar
3. (all following
steps the same as Main Menu pathway starting in Step 4)
1. Select cell D2
2. Type
"=date(A2,B2,C2)"
3. Confirm the formula
|
|
|
|
|
14.
|
Use
the PMT function to calculate a monthly payment in cell F15 based on the
loan amount in cell C15. The loan term is 10 years; the annual interest
rate is 6%. Refer to the cells on the worksheet in the formula and do not
include values for FV or Type. The payment value will display as a negative
number.
[307]
|
1. Select cell F15
2. Click Insert on the
menu bar
3. Click Function
4. Click PMT in the
Most Recently Used box (or search for "PMT", look in ALL
category, look in Financial category)
5. Confirm the dialog
box (or double-click in Step 4)
6. In the Rate box,
click C17 (or type C17) or type 6%
7. In the Rate box,
type "/12" to the right of C17
8. In the Nper box click C16 (or type C16) or type 10
9. In the Nper box, type "*12" to the right of C16
10. In the PV box,
click C15 (or type C15)
11. Confirm the dialog
box
1. Select cell F15
2. Type =PMT(
3. Press CTRL+A
4. In the Rate box,
click C17 (or type C17) or type 6%
5. In the Rate box,
type "/12" to the right of C17
6. In the Nper box click C16 (or type C16)
7. In the Nper box, type "*12" to the right of C16
8. In the PV box,
click C15 (or type C15)
9. Confirm the dialog
box
1. Select cell F15
2. Click the AutoSum
button arrow on the Standard toolbar
3. Click More
functions
4. Click PMT in the
Most Recently Used box (or search for "PMT", look in ALL
category, look in Financial category)
5. Confirm the dialog
box (or double-click in Step 4)
6. In the Rate box,
click C17 (or type C17) or type 6%
7. In the Rate box,
type "/12" to the right of C17
8. In the Nper box click C16 (or type C16) or type 10
9. In the Nper box, type "*12" to the right of C16
10. In the PV box,
click C15 (or type C15)
11. Confirm the dialog
box
1. Select cell F15
2. Click the Insert
Function button on the formula bar
3. Click PMT in the
Most Recently Used list
4. Confirm the dialog
box (or double-click in Step 3)
5. In the Rate box,
click C17 (or type C17) or type 6%
6. In the Rate box,
type "/12" to the right of C17
7. In the Nper box click C16 (or type C16) or type 10
8. In the Nper box, type "*12" to the right of C16
9. In the PV box,
click C15 (or type C15)
10. Confirm the dialog
box
1. Select cell F15
2. Type
"=PMT(C17/12,C16*12,C15)
3. Confirm the formula
|
|
|
|
|
15.
|
In
cell G3, use the IF function to determine whether the number of attendees
at the Alaska program was greater than 25. If so, the result should display
"Yes" in the cell. If not, "No" should be displayed.
[308]
|
1. Select cell G3
2. Click Insert on the
menu bar
3. Click Function
4. Click IF (using the
Most Recently Used, All, or Logical categories or by searching)
5. Confirm the dialog
box (or double-click in Step 4)
6. In the Logical test
box, click F3 (or type F3)
7. In the Logical test
box, type ">25" to the right of F3
8. In the Value_if_true box, type "Yes" (with or
without quotes)
9. In the Value_if_false box, type "No" (with or
without quotes)
10. Confirm the dialog
box
1. Select cell G3
2. Type
"=IF("
3. Press CTRL+A
4. continue with Step
6 of Using the Menu
1. Select cell G3
2. Click the AutoSum
button arrow on the Standard toolbar
3. Click More
Functions
4. Continue with Step
4 of Using the Menu
1. Select cell G3
2. Click the Insert
Function button on the formula bar
3. Continue with step
4 of Using the Menu
1. Select cell G3
2. Enter
'=IF(F3>25,"Yes","No")' (Note: the quotation marks
around Yes and No are required)
3. Confirm the formula
|
|
|
|
|
16.
|
Copy
the numbers from Qtr 1 (B14:D17) up to the top of the sheet, beginning in
cell B5.
[373]
|
1. Select cells
B14:D17
2. Click Edit on the
menu bar
3. Click Copy
4. Select cell B5
5. Press the ENTER key
1. Select cells
B14:D17
2. Click Edit on the
menu bar
3. Click Copy
4. Select cell B5
5. Click Edit on the
menu bar
6. Click Paste
1. Select cells
B14:D17
2. Press CTRL+C
3. Select cell B5
4. Press the ENTER key
(or press CTRL+V)
1. Select cells
B14:D17
2. Press the Copy
button on the Standard toolbar
3. Select cell B5
4. Press the ENTER key
(or click the Paste button)
- Using a Mouse Right-Click
1. Select cells
B14:D17
2. Right-click
3. Click Copy
4. Select cell B5
5. Press the ENTER key
(or right-click and click Paste on the shortcut menu)
1. Select cells
B14:D17
2. Display the
Clipboard taskpane
3. Copy (using edit
menu, button, Ctrl+C, or right-click)
4. Select cell B5
5. Click the top item
in the task pane
1. Select cells
B14:D17
2. Hold down the CTRL
key
3. Drag the border of
the selected cells until the highlighted range covers cells B5:D8
4. Release the CTRL
key and mouse button
|
|
|
|
|
17.
|
Open
the Task Pane and use the Office Clipboard to copy the data for January,
February, and March from the QTR 1 sheet (range: B5:D16) and April, May,
June from the QTR 2 sheet (range: B5:D16). Place the information in the
appropriate cells on the Annual sheet. Be sure to paste the January to
March range first (B5:D16) followed by the April to June range (B5:D16).
[625]
|
1. Press ALT+V.
2. Press K.
3. Click the QTR 1
tab.
4. Select cells
B5:D16.
5. Press CTRL+C twice.
6. Click the QTR 2
tab.
7. Select cells
B5:D16.
8. Press CTRL+C.
9. Click the Annual
tab.
10. Select cell B5.
11. Click the second
item listed in the task pane (or right-click the item in the task pane and
select Paste, or by click the arrow and select Paste).
12. Select cell E5.
13. Click the first
item listed in the task pane (or right-click the item in the task pane and
select Paste, or by click the arrow and select Paste).
1. Click View on the
menu bar.
2. Click Task Pane (or
press CTRL+F1 in step 1).
3. If necessary, click
the Other Task Panes arrow on the task pane.
4. Click Clipboard.
5. Click the QTR 1
tab.
6. Select cells
B5:D16.
7. Copy (Copy button,
CTRL+C, right-click and click Copy, or click Edit on the menu and click CopyI).
8. Click the QTR 2
tab.
9. Select cells
B5:D16.
10. Copy (Copy button,
CTRL+C, right-click and click Copy, or click Edit on the menu and click CopyI).
11. Click the Annual
tab.
12. Select cell B5.
13. Click the second
item listed in the task pane (or right-click the item in the task pane and
select Paste, or by click the arrow and select Paste).
14. Select cell E5.
15. Click the first
item listed in the task pane (or right-click the item in the task pane and
select Paste, or by click the arrow and select Paste).
1. Click the QTR 1
tab.
2. Access the Task
Pane.
3. Select cells
B5:D16.
4. Copy (Copy button,
CTRL+C, right-click and click Copy, or click Edit on the menu and click CopyI).
5. Click the QTR 2
tab.
6. Select cells
B5:D16.
7. Press CTRL+C.
8. Click the Annual
tab.
9. Select cell B5.
10. Click the second
item listed in the task pane (or right-click the item in the task pane and
select Paste, or by click the arrow and select Paste)
11. Select cell E5.
12. Click the first
item listed in the task pane (or right-click the item in the task pane and
select Paste, or by click the arrow and select Paste).
|
|
|
|
|
18.
|
Use
the fill handle to copy the formula in B16 (Net Funds) across for the rest
of the months (C16:H16). Do not copy each cell individually.
[626]
|
1. Select cell B16
2. Drag the fill
handle to the right across cells C16:H16
|
|
|
|
|
19.
|
Use
a series to fill the months Jan to Dec across the top of the sheet,
beginning in cell B3 and finishing in cell M3. Complete this in one
operation (i.e., do not fill in the series one month at a time).
[627]
|
1. Select cells B3:M3
2. Click Edit on the
menu bar
3. Point to Fill
4. Click Series
5. Click AutoFill
6. Confirm the dialog
box
1. Select cell B3
2. Drag the fill
handle to the right until it reaches column M.
|
|
|
|
|
20.
|
Show
the formulas in the cells.
[654]
|
1. Click Tools on the
menu bar.
2. Click Options.
3. Click the View tab,
if necessary.
4. Click Formulas.
5. Confirm the dialog
box.
1. Click Tools on the
menu bar.
2. Press O.
3. Click the View tab,
if necessary.
4. Press ALT+R.
5. Press ENTER.
1. Press CTRL+`
(accent mark).
|
|
|
|
|
21.
|
Use
the TODAY function to insert the date three days in the future to cell A22.
[1049]
|
1. Click Insert on the
menu bar.
2. Click Function on
the Insert menu.
3. Type
"today" in the Search for a function text box.
4. Click the Go
button.
5. Click the TODAY
function in the results list, if necessary.
6. Click the OK button
(or press ENTER).
7. Click the OK button
(or press ENTER) again.
8. Click to the right
of the function in the Formula bar and type "+3".
9. Press ENTER.
1. Click Insert on the
menu bar.
2. Click Function on
the Insert menu.
3. Click the select a
category drop-down list.
4. Select Date &
Time from the function category list (or select All).
5. Scroll down the
list until the TODAY function is visible.
6. Click TODAY in the
function list.
7. Click the OK button
(or press ENTER).
8. Click the OK button
(or press ENTER) again.
9. Click to the right
of the TODAY() function in the Formula bar and type "+3".
10. Press ENTER.
1. Click the AutoSum
button list arrow.
2. Click More
Functions.
3. Type
"today" in the Search for a function text box.
4. Click the Go
button.
5. Click the TODAY
function in the results list, if necessary.
6. Click the OK button
(or press ENTER).
7. Click the OK button
(or press ENTER) again.
8. Click to the right
of the function in the Formula bar and type "+3".
9. Press ENTER.
1. Type
"=TODAY()+3" in cell A22.
2. Click the Enter
button on the Formula bar.
1. Click the Insert
Function button on the Formula bar.
2. Click the select a
category drop-down list arrow.
3. Select Date &
Time from the function category list.
4. Scroll down the
list until the TODAY function is visible.
5. Click TODAY in the
function list.
6. Click the OK
button.
7. Click the OK button
again.
8. Click to the right
of the TODAY() function in the Formula bar and type "+3".
9. Press ENTER.
|
|
|
|
|
22.
|
Use
the AVERAGE function to determine the average Program Costs (range: D4:D19)
and display the result in cell B25.
[1183]
|
1. Select cell B25.
2. Click Insert on the
menu bar.
3. Click Function.
4. Click AVERAGE in
the Select a function list.
5. Confirm the dialog
box (or double-click AVERAGE in step 4).
6. Enter D4:D19 in the
Number1 box (or collapse the dialog and drag to select D4:D19 on the
sheet).
7. Confirm the dialog
box.
1. Select cell B25
2. Click the AutoSum
button list arrow on the Standard toolbar
3. Click Average in
the AutoSum list
4. Click and drag to
select the range D4:D19 (or type D4:D19)
5. Confirm the formula
(press Enter or click the check mark)
1. Select cell B25
2. Click the Insert
Function button on the formula bar
3. Click AVERAGE in
the Select a function list
4. Confirm the dialog
box (or double-click in step 3)
5. Enter D4:D19 in the
Number1 box (or collapse the dialog and drag to select on the sheet)
6. Confirm the dialog
box
1. Select cell B25
2. Enter =average(
3. Drag to select
cells D4:D19 (or type D4:D19)
4. Confirm the formula
|
|
|
|
|
23.
|
Using
Point mode, enter a formula in cell F4 to compute the Remaining Costs by
subtracting the Amount Underwritten from the Program Costs.
[1186]
|
1. Click cell F4
2. Click the Formula
bar.
3. Type =
4. Click cell D4
5. Type - (minus sign)
6. Click cell E4
7. Click the Enter
button on the Formula bar.
1. Click cell F4
2. Type =
3. Click cell D4
4. Type - (minus sign)
5. Click cell E4
6. Press ENTER
|
|
|
|
|
24.
|
Use
the Fill handle to copy the formula in cell G3 to the range G4:G18.
[1188]
|
1. Select cell G3.
2. Drag the fill
handle down until it reaches row 18.
|
|
|
|
|