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 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.
=SUMPRODUCT
Shift + Ctrl + }
F4
SUM

2. Conditional Sum
Stimulation Model
=SUMIF
Alt + T + O + F
Alt + T + U + T

3. Insert AutoSum formula
Alt + T + U + D
Relative Ranges
Alt + D + P
ALT + "="

4. Delete Comment
Alt + E + A + M
PMT
Alt + S + C
FV

5. Trace all precedents (indirect)
FV
SHIFT + CTRL + {
=AVERAGE
MAX

6. Show formulas/values
=MAXIMUM
CTRL + ~
CTRL + ]
CTRL + ' (apostrophe)

7. Go to Constants
F5
=MINIMUM
Alt + E + A + M
Ctrl + R

8. Recalculate all workbooks
=CHOOSE
ALT + "="
Ctrl + R
F9

9. Highlight Dependents
MIN
Ctrl + }
ALT + M + P
Alt + D + P

10. Sum Numbers
Ctrl + {
=Sum
=MATCH
=SUMIF

11. Conditional Count
FV
=COUNTIF
Absolute Ranges
=HLOOKUP

12. Displays the date in a variety of formats.
ALT + W + G
=IF
=OFFSET
DATE

13. Trace Precedents
=AND
MAX
Alt + T + U + T
=CHOOSE

14. Absolute Value
=HLOOKUP
Alt + S + C
Alt + D + L
=ABS

15. Highlight Precedents
Ctrl + {
FV
Alt + D + F + A
=AVERAGE

16. Jump Back to Original Cell
=AVERAGE
FV
=COUNTIF
F5 + Enter

17. Displays the variance for the numbers contained in the range of the argument.
VAR
F5
NOW
Alt + W + M + R

18. Show all Dependents
Shift + Ctrl + }
Alt + E + S + L
Alt + E + S + R
ALT + M + A + A

19. Find Based on Column
Ctrl + D
=VLOOKUP
SHIFT + CTRL + {
=MINIMUM

20. Create Data Table
Two Variable data table
ALT + M + D
Alt + D + T
Alt + S + C

21. Show Formulas
Alt + E + S + L
Ctrl + }
Ctrl + {
Ctrl + ~ + =

22. Select From List
F9
F9
=CHOOSE
Shift + Ctrl + {

23. Zoom to selection
Shift + F2
Alt + W + M + U
Alt + E + A + M
ALT + W + G

24. Copy Formula Right
=AND
Ctrl + R
=
ALT + "="

25. Maximum
CTRL + [
Alt + D + P
VAR
=MAXIMUM

26. Use Relative References
Alt + W + M + U
F4
=COUNT
FV

27. A quantitative representation - or approximation - of a real object or system. Analytical models - statistical models - financial model.
Mathematical Models
CTRL + [
NOW
=SUMPRODUCT

28. Find Based on Row
=HLOOKUP
ALT + M + D
F9
Alt + D + T

29. Specify one input cell and any number of result cells - often used to explore how altering one value can impact several result cell
=SUMIF
One Variable data table
Shift + Ctrl + }
F5

30. Go to Formulas
=AVERAGE
Mathematical Models
Alt + T + O + F
COUNT

31. 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
F9
=OR
Relative Ranges
Two Variable data table

32. Displays the present value of a series of equal payments at a fixed rate for a specified number of payments.
Alt + S + C
CTRL + ~
PV
Alt + W + M + V

33. Trace immediate precedents
NOW
COUNT
F4
ALT + M + P

34. Advanced Data Filter
Alt + D + F + A
Alt + T + U + T
=Sum
DATE

35. Displays the future value of a series of equal payments at a fixed rate for a specified number of periods.
=HLOOKUP
FV
STDE
Alt + T + O + F

36. Paste Formats & Formulas
F5
=MATCH
Alt + E + S + R
=MAXIMUM

37. Go to precedent cells
Absolute Ranges
=CHOOSE
CTRL + [
Alt + E + A + M

38. Go to dependent cells
Alt + T + U + T
MIN
CTRL + ]
ALT + R + G

39. Remove tracing arrows
ALT + M + A + A
Alt + =
=INDEX
Ctrl + }

40. Create Pivot Table
F9
Alt + D + P
=HLOOKUP
MIN

41. 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
Shift + Ctrl + {
Alt + T + U + D
Ctrl + R

42. Add/Edit Comment
ALT + M + D
Shift + F2
CTRL + [
VAR

43. Erase Traces
=IF
Alt + S + C
Shift + Ctrl + }
Alt + T + U + A

44. 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 + M + D
Alt + T + U + T
MAX
DATE

45. Trace immediate dependents
Stimulation Model
CTRL + ' (apostrophe)
STDE
ALT + M + D

46. View Macros
Alt + W + M + V
CTRL + ' (apostrophe)
Alt + W + M + U
Stimulation Model

47. Paste Link
=INDEX
F9
=
Alt + E + S + L

48. Show all Precedents
F9
Shift + Ctrl + {
=AVERAGE
CTRL + ~

49. Move # of Rows/Columns
Relative Ranges
=
=OFFSET
LN

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
=OFFSET
F9
F5 + Enter