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.
=SUMPRODUCT
=CHOOSE
=MINIMUM
MAX

2. Displays the current date or time based on the computer's clock.
Shift + F3
F5 + Enter
NOW
Alt + D + F + A

3. Used for basic calculations - stored by the excel document within specific cells or across different ranges of cells
Alt + T + U + A
Formulas
CTRL + ' (apostrophe)
=IF

4. Displays the date in a variety of formats.
Relative Ranges
DATE
=OR
F9

5. View Macros
CTRL + ]
ALT + W + G
Alt + W + M + V
SQRT

6. Find Item
=INDEX
PV
ALT + "="
=MAXIMUM

7. Jump Back to Original Cell
F5 + Enter
=AVERAGE
COUNT
CTRL + ~

8. Go to precedent cells
One Variable data table
CTRL + [
ALT + "="
Alt + D + L

9. Recalculate all workbooks
F9
=MAXIMUM
AVERAGE
One Variable data table

10. Go to dependent cells
Shift + F2
Alt + E + A + M
=AVERAGE
CTRL + ]

11. Highlight Precedents
Stimulation Model
Formulas
=Sum
Ctrl + {

12. Specify one input cell and any number of result cells - often used to explore how altering one value can impact several result cell
=CHOOSE
Ctrl + R
ALT + M + A + A
One Variable data table

13. Evaluate formula
One Variable data table
ALT + M + V
=HLOOKUP
SHIFT + CTRL + {

14. Advanced Data Filter
ALT + M + A + A
Alt + D + F + A
CTRL + ' (apostrophe)
Alt + =

15. Find Based on Row
=HLOOKUP
Alt + E + S + L
=Sum
Shift + F2

16. Show formulas/values
Alt + D + F + A
CTRL + ~
Alt + E + A + M
Mathematical Models

17. Multiply/Sum Range
=VLOOKUP
=SUMPRODUCT
Alt + T + U + D
AVERAGE

18. Show all Dependents
Shift + Ctrl + }
=HLOOKUP
Formulas
CTRL + [

19. 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.
Absolute Ranges
Shift + Ctrl + }
Alt + D + P
AVERAGE

20. Trace all precedents (indirect)
Alt + W + M + U
SHIFT + CTRL + {
=HLOOKUP
Ctrl + ~ + =

21. Displays the present value of a series of equal payments at a fixed rate for a specified number of payments.
ALT + "="
PV
=AND
Ctrl + {

22. 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.
Formulas
MIN
PMT
Alt + S + C

23. Go to Formulas
Alt + D + P
Alt + E + S + R
Alt + =
F5

24. 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.
=OFFSET
=INDEX
F4
COUNT

25. 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.
Alt + T + U + A
=INDEX
SUM
Ctrl + {

26. Sum Numbers
Shift + Ctrl + {
F5 + Enter
=Sum
=MATCH

27. Select From List
=MAXIMUM
=CHOOSE
Alt + E + A + M
Shift + F2

28. Copy Formula Down
Ctrl + D
Formulas
SHIFT + CTRL + }
Alt + D + L

29. Go to Constants
=AND
=Sum
F5
CTRL + ]

30. Enter Built-In Formula
Shift + F3
F5
Mathematical Models
VAR

31. Highlight Comments
Alt + S + C
COUNT
SHIFT + CTRL + {
Absolute Ranges

32. Find Item's Position
=MATCH
COUNT
ALT + M + V
ALT + "="

33. Sum Adjacent Cells
SHIFT + CTRL + {
Alt + E + S + L
Alt + =
PMT

34. Paste Link
=VLOOKUP
Stimulation Model
Ctrl + ~ + =
Alt + E + S + L

35. Displays the variance for the numbers contained in the range of the argument.
VAR
Alt + T + O + F
=SUMPRODUCT
F9

36. Move # of Rows/Columns
=VLOOKUP
=OFFSET
ALT + M + A + A
Alt + W + M + U

37. Add/Edit Comment
Alt + =
SHIFT + CTRL + }
Shift + F2
One Variable data table

38. Erase Traces
=Sum
Alt + D + F + A
ALT + M + V
Alt + T + U + A

39. All Must Be True
Alt + T + U + D
=AND
Alt + D + P
Alt + E + A + M

40. Track changes
=
Stimulation Model
=VLOOKUP
ALT + R + G

41. Conditional
=ABS
Absolute Ranges
=IF
Alt + D + F + A

42. 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
Two Variable data table
Mathematical Models
MAX
ALT + M + P

43. Go to Formulas
Alt + T + O + F
CTRL + ' (apostrophe)
Alt + E + S + R
SHIFT + CTRL + }

44. Remove tracing arrows
MIN
SUM
MAX
ALT + M + A + A

45. Zoom to selection
CTRL + [
ALT + W + G
Alt + W + M + V
Alt + T + U + T

46. Displays the future value of a series of equal payments at a fixed rate for a specified number of periods.
=ABS
FV
=SUMPRODUCT
PMT

47. Delete Comment
=COUNT
Alt + W + M + U
CTRL + ]
Alt + E + A + M

48. Conditional Sum
=CHOOSE
=SUMIF
DATE
=MAXIMUM

49. Insert AutoSum formula
ALT + "="
Alt + E + A + M
Alt + T + U + A
STDE

50. At Least 1 Must Be True
=OR
Alt + W + M + U
Shift + Ctrl + {
CTRL + ]