Test your basic knowledge |

Microsoft Excel Functions Formulas Shortcuts

Subject : it-skills

Instructions:

Answer 50 questions in 20 minutes. 2 minutes extra for reading the instructions.
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 variance for the numbers contained in the range of the argument.
Alt + W + M + V
VAR
AVERAGE
Ctrl + ~ + =

2. Count Number of Entries
=COUNT
Alt + W + M + V
Ctrl + R
=MATCH

3. Uses two input cells - but only the value of a single result cell can be displayed. Used to explore the impact of changing two values on a single result
Ctrl + ~ + =
Two Variable data table
STDE
ALT + M + P

4. Find Based on Column
ALT + M + D
=VLOOKUP
=SUMIF
FV

5. Trace immediate precedents
ALT + M + P
ALT + R + G
Ctrl + ~ + =
F5 + Enter

6. Highlight Comments
Alt + E + A + M
MAX
Alt + S + C
Alt + W + M + R

7. Evaluate formula
MIN
STDE
ALT + M + V
Alt + D + F + A

8. Anchor Cell
F4
ALT + W + G
Shift + Ctrl + }
Ctrl + {

9. Go to dependent cells
CTRL + ]
SHIFT + CTRL + }
=ABS
Alt + E + A + M

10. Zoom to selection
Alt + =
=COUNTIF
F5 + Enter
ALT + W + G

11. Enter Built-In Formula
Alt + W + M + R
=MINIMUM
Shift + F3
=Sum

12. Minimum
Ctrl + R
F5
ALT + "="
=MINIMUM

13. Displays the date in a variety of formats.
MAX
DATE
F9
F4

14. Copy Formula Down
Alt + S + C
AVERAGE
Ctrl + ~ + =
Ctrl + D

15. Conditional Count
SHIFT + CTRL + {
=COUNTIF
PMT
CTRL + ' (apostrophe)

16. Copy value from above cell
=Sum
Alt + T + O + F
SHIFT + CTRL + " (quote)
=AND

17. 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.
COUNT
Alt + D + F + A
Alt + D + P
Ctrl + }

18. Displays the future value of a series of equal payments at a fixed rate for a specified number of periods.
FV
=COUNT
Relative Ranges
LN

19. Displays the present value of a series of equal payments at a fixed rate for a specified number of payments.
PV
=OR
NOW
=MATCH

20. Paste Formats & Formulas
Alt + E + S + R
F9
Ctrl + D
=OR

21. View Macros
Alt + W + M + V
VAR
=AVERAGE
One Variable data table

22. Show Formulas
CTRL + [
Alt + D + P
Ctrl + ~ + =
F9

23. 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.
Alt + D + F + A
PMT
MIN
=MAXIMUM

24. Highlight Dependents
=AND
=COUNTIF
F9
Ctrl + }

25. Validate Data
Alt + E + S + L
Alt + D + L
ALT + R + G
=AND

26. Average
Ctrl + ~ + =
Alt + E + S + R
FV
=AVERAGE

27. Used for basic calculations - stored by the excel document within specific cells or across different ranges of cells
Formulas
Ctrl + ~ + =
NOW
F5

28. Find Item's Position
PV
Alt + D + P
Shift + F2
=MATCH

29. 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.
Stimulation Model
=CHOOSE
Alt + W + M + V
MAX

30. Enter Formula
Mathematical Models
Shift + F2
=
=COUNT

31. Ranges that do not have a $ before the column portion of the reference.
=SUMIF
Alt + W + M + R
ALT + M + A + A
Relative Ranges

32. Trace Precedents
DATE
ALT + "="
STDE
Alt + T + U + T

33. Recalculate all workbooks
=HLOOKUP
Relative Ranges
F9
Ctrl + {

34. 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.
AVERAGE
=COUNTIF
Ctrl + {
COUNT

35. Insert AutoSum formula
One Variable data table
SQRT
ALT + "="
LN

36. A model used to investigate dynamic behavior
Stimulation Model
ALT + R + G
VAR
PMT

37. Delete Comment
=COUNTIF
LN
Alt + D + L
Alt + E + A + M

38. Refresh Worksheet / Calculate Formulas
Alt + E + S + R
=MINIMUM
F9
=COUNTIF

39. All Must Be True
Alt + D + L
=AND
LN
DATE

40. Paste Link
Alt + E + S + L
Alt + T + U + A
CTRL + ~
One Variable data table

41. Sum Adjacent Cells
Alt + =
PMT
SHIFT + CTRL + {
=SUMPRODUCT

42. Displays the standard deviation of the numbers contained in the range of the argument.
STDE
=MAXIMUM
=AVERAGE
Ctrl + R

43. Displays the payment per period needed to repay a loan at a specified interest rate for a specified number of periods.
F5
=HLOOKUP
PMT
LN

44. Sum Numbers
Alt + W + M + U
=Sum
LN
Alt + E + A + M

45. Maximum
ALT + W + G
Shift + Ctrl + }
Alt + S + C
=MAXIMUM

46. Highlight Precedents
Ctrl + {
=INDEX
CTRL + ~
PMT

47. Trace all dependents (indirect)
Alt + =
=MAXIMUM
=SUMIF
SHIFT + CTRL + }

48. Displays the natural logarithm of a number. For example - =LN(50) will display 1.69897.
Alt + W + M + R
ALT + W + G
LN
ALT + M + D

49. Copy formula from above cell

50. Go to Constants
CTRL + ' (apostrophe)
=SUMIF
ALT + "="
F5