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. Refresh Worksheet / Calculate Formulas
Alt + T + U + D
F9
F5 + Enter
F4

2. Highlight Comments
CTRL + ' (apostrophe)
ALT + "="
Alt + S + C
CTRL + ~

3. Sum Numbers
=COUNT
=MATCH
=HLOOKUP
=Sum

4. Ranges that have a $ character before the column portion of the reference and/or the row portion of the reference.
Ctrl + R
F4
=INDEX
Absolute Ranges

5. Add/Edit Comment
ALT + M + D
Alt + D + F + A
Ctrl + {
Shift + F2

6. Conditional
Stimulation Model
=VLOOKUP
=IF
Alt + T + U + T

7. Use Relative References
ALT + M + D
Alt + W + M + U
=MINIMUM
MAX

8. Go to Formulas
=AVERAGE
Alt + T + O + F
Alt + S + C
=SUMIF

9. Minimum
=MINIMUM
Alt + W + M + V
F9
Alt + D + F + A

10. Maximum
Alt + D + P
=MAXIMUM
Relative Ranges
SQRT

11. Average
SHIFT + CTRL + " (quote)
SQRT
=AVERAGE
Absolute Ranges

12. Sum Adjacent Cells
Alt + T + U + A
LN
Alt + E + S + L
Alt + =

13. Conditional Sum
=MINIMUM
Alt + W + M + R
Ctrl + {
=SUMIF

14. Move # of Rows/Columns
CTRL + ' (apostrophe)
Stimulation Model
DATE
=OFFSET

15. Displays the future value of a series of equal payments at a fixed rate for a specified number of periods.
FV
AVERAGE
CTRL + ' (apostrophe)
ALT + R + G

16. 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.
=AND
MAX
MIN
SHIFT + CTRL + }

17. All Must Be True
=AND
CTRL + [
=MINIMUM
Two Variable data table

18. Displays the standard deviation of the numbers contained in the range of the argument.
STDE
SUM
ALT + M + A + A
Ctrl + ~ + =

19. Trace all dependents (indirect)
SHIFT + CTRL + }
Stimulation Model
CTRL + ]
PV

20. Erase Traces
Alt + W + M + V
Alt + T + U + A
Mathematical Models
PV

21. Record Macros
Alt + E + S + R
Alt + W + M + R
=AND
ALT + M + A + A

22. Specify one input cell and any number of result cells - often used to explore how altering one value can impact several result cell
CTRL + ~
One Variable data table
ALT + W + G
SUM

23. Displays the payment per period needed to repay a loan at a specified interest rate for a specified number of periods.
ALT + M + V
Shift + F3
PMT
Alt + W + M + R

24. 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
Alt + T + U + T
F9
Alt + E + S + L

25. Ranges that do not have a $ before the column portion of the reference.
Shift + Ctrl + }
Relative Ranges
Alt + S + C
FV

26. Find Based on Row
=HLOOKUP
=
FV
ALT + M + V

27. Conditional Count
SQRT
=COUNTIF
Alt + T + U + D
=IF

28. Trace immediate dependents
=HLOOKUP
ALT + M + D
Shift + F2
PV

29. Displays the sum of cells with numerical values in the argument range. For example - =SUM(D6:D21) displays the total added together if all the cells are filled.
=SUMIF
SUM
=COUNTIF
VAR

30. 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.
=OR
F5
COUNT
SHIFT + CTRL + }

31. Displays the natural logarithm of a number. For example - =LN(50) will display 1.69897.
LN
=AVERAGE
=COUNTIF
F5

32. Paste Link
Alt + T + U + A
=VLOOKUP
=OFFSET
Alt + E + S + L

33. Highlight Dependents
Alt + D + L
SHIFT + CTRL + {
Ctrl + }
Ctrl + R

34. Copy value from above cell
=OR
=ABS
SHIFT + CTRL + " (quote)
=MAXIMUM

35. Copy Formula Right
Ctrl + R
F9
=INDEX
ALT + "="

36. Jump Back to Original Cell
=CHOOSE
Alt + W + M + R
F5 + Enter
Formulas

37. Recalculate all workbooks
Formulas
=INDEX
F9
Ctrl + ~ + =

38. Go to precedent cells
Shift + Ctrl + {
CTRL + [
VAR
ALT + R + G

39. Count Number of Entries
Shift + Ctrl + }
=CHOOSE
Alt + D + P
=COUNT

40. Create Data Table
NOW
=INDEX
Formulas
Alt + D + T

41. Enter Built-In Formula
F9
Shift + F3
CTRL + [
F9

42. Copy formula from above cell

43. Absolute Value
Mathematical Models
=ABS
F5 + Enter
Alt + W + M + V

44. Multiply/Sum Range
DATE
Shift + F2
=CHOOSE
=SUMPRODUCT

45. Enter Formula
Alt + W + M + V
=
SUM
SHIFT + CTRL + {

46. Show formulas/values
CTRL + ~
Alt + D + P
LN
F5

47. Advanced Data Filter
=OR
=AND
=CHOOSE
Alt + D + F + A

48. Find Item
=INDEX
ALT + M + A + A
Alt + D + L
MIN

49. View Macros
Alt + W + M + V
Alt + T + U + T
PV
=MATCH

50. 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
ALT + "="
Two Variable data table
ALT + W + G
Alt + E + S + R