Test your basic knowledge |

Microsoft Excel Functions Formulas Shortcuts

Subject : it-skills

Instructions:

Answer 50 questions in 15 minutes.
If you are not ready to take this test, you can study here.
Match each statement with the correct term.
Don't refresh. All questions and answers are randomly picked and ordered every time you load a test.

This is a study tool. The 3 wrong answers for each question are randomly chosen from answers to other questions. So, you might find at times the answers obvious, but you will see it re-enforces your understanding as you take the test each time.

1. Displays the maximum or largest of the range identified in the argument. For example - =MAX(E4:E9) displays the maximum or largest of the numbers contained in the range E4:E9.
Alt + =
=VLOOKUP
MAX
ALT + W + G

2. Paste Link
One Variable data table
Alt + E + S + L
=INDEX
ALT + M + A + A

3. Displays the average of the range identified in the argument. For example - =AVERAGE(E4:E9) displays the average of the numbers contained in the range E4:E9.
Shift + Ctrl + {
MAX
=COUNT
AVERAGE

4. Copy Formula Down
Ctrl + D
CTRL + ' (apostrophe)
Stimulation Model
Alt + S + C

5. View Macros
Alt + W + M + V
=COUNT
=COUNTIF
F5 + Enter

6. Conditional Sum
CTRL + ' (apostrophe)
SHIFT + CTRL + {
COUNT
=SUMIF

7. Show all Precedents
COUNT
Alt + S + C
Shift + Ctrl + {
F9

8. A model used to investigate dynamic behavior
=AND
FV
Stimulation Model
=OR

9. Evaluate formula
ALT + M + V
LN
F5 + Enter
Shift + F2

10. Used for basic calculations - stored by the excel document within specific cells or across different ranges of cells
=SUMIF
Mathematical Models
Formulas
=MATCH

11. Refresh Worksheet / Calculate Formulas
=OFFSET
Ctrl + D
ALT + W + G
F9

12. Record Macros
Alt + W + M + R
PMT
=ABS
Ctrl + R

13. Ranges that have a $ character before the column portion of the reference and/or the row portion of the reference.
=COUNTIF
Absolute Ranges
Alt + =
=HLOOKUP

14. Highlight Dependents
Alt + T + U + T
=SUMIF
Ctrl + }
CTRL + ~

15. Multiply/Sum Range
Shift + F2
Alt + E + A + M
Alt + S + C
=SUMPRODUCT

16. At Least 1 Must Be True
COUNT
F5
=COUNTIF
=OR

17. Specify one input cell and any number of result cells - often used to explore how altering one value can impact several result cell
Mathematical Models
=OR
One Variable data table
Relative Ranges

18. Paste Formats & Formulas
=SUMPRODUCT
F4
Shift + F3
Alt + E + S + R

19. Add/Edit Comment
Two Variable data table
Shift + Ctrl + {
Shift + F2
=SUMIF

20. Highlight Comments
Alt + S + C
F4
AVERAGE
Ctrl + }

21. Minimum
=AND
SHIFT + CTRL + {
Stimulation Model
=MINIMUM

22. Insert AutoSum formula
ALT + "="
Shift + F3
NOW
Alt + D + L

23. All Must Be True
=AND
SUM
F4
=OFFSET

24. Remove tracing arrows
FV
ALT + M + A + A
Alt + W + M + R
=AVERAGE

25. Trace Dependents
Alt + E + S + L
Alt + S + C
=Sum
Alt + T + U + D

26. Trace Precedents
Alt + T + U + T
=HLOOKUP
CTRL + ]
LN

27. Find Based on Row
Ctrl + }
=HLOOKUP
Alt + E + A + M
Alt + D + P

28. Displays the current date or time based on the computer's clock.
SHIFT + CTRL + }
NOW
=MATCH
CTRL + ' (apostrophe)

29. Displays the number of cells with numerical values in the argument range. For example - =COUNT(D6:D21) displays 16 if all the cells are filled.
Alt + =
Stimulation Model
COUNT
ALT + R + G

30. Maximum
CTRL + [
=MAXIMUM
Alt + E + A + M
ALT + M + D

31. Conditional Count
=CHOOSE
=MATCH
STDE
=COUNTIF

32. Go to Constants
Ctrl + }
CTRL + ' (apostrophe)
F5
Alt + D + P

33. Highlight Precedents
Stimulation Model
Alt + E + A + M
Ctrl + {
=VLOOKUP

34. Displays the natural logarithm of a number. For example - =LN(50) will display 1.69897.
=COUNTIF
Shift + F3
LN
CTRL + ' (apostrophe)

35. Go to Formulas
PV
F5
AVERAGE
=SUMIF

36. Displays the payment per period needed to repay a loan at a specified interest rate for a specified number of periods.
=IF
PMT
ALT + W + G
ALT + M + P

37. Zoom to selection
=AND
ALT + W + G
AVERAGE
CTRL + ]

38. Anchor Cell
F5 + Enter
ALT + R + G
F4
=AVERAGE

39. Create Pivot Table
Alt + W + M + U
VAR
CTRL + [
Alt + D + P

40. Copy formula from above cell

41. Copy Formula Right
Alt + T + U + A
=AND
=OR
Ctrl + R

42. Advanced Data Filter
Alt + D + P
SHIFT + CTRL + {
Alt + D + F + A
ALT + M + D

43. Track changes
MIN
ALT + R + G
COUNT
ALT + W + G

44. Jump Back to Original Cell
=COUNT
=SUMIF
F5 + Enter
F5

45. Trace immediate precedents
ALT + M + P
Alt + W + M + U
PV
=INDEX

46. Displays the future value of a series of equal payments at a fixed rate for a specified number of periods.
F9
Alt + D + L
FV
Ctrl + ~ + =

47. Erase Traces
Alt + T + U + A
=INDEX
Alt + W + M + R
Alt + D + F + A

48. Find Item's Position
Alt + W + M + R
ALT + M + P
LN
=MATCH

49. Delete Comment
Alt + T + O + F
Alt + E + A + M
Shift + F3
=OFFSET

50. Displays the minimum or smallest of the range identified in the argument. For example - =MIN(E4:E9) displays the minimum or smallest of the numbers contained in the range E4:E9.
MIN
ALT + M + P
=
=HLOOKUP