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. Erase Traces
Alt + T + U + T
COUNT
Alt + T + U + A
Alt + T + O + F

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

3. Show formulas/values
CTRL + ~
Alt + T + U + A
Alt + E + A + M
=AND

4. Displays the variance for the numbers contained in the range of the argument.
VAR
=Sum
Alt + D + F + A
One Variable data table

5. Trace all precedents (indirect)
SHIFT + CTRL + {
=MAXIMUM
Relative Ranges
Ctrl + }

6. Record Macros
F5
Alt + W + M + R
Alt + S + C
Shift + Ctrl + }

7. Enter Formula
=Sum
Shift + F2
=
PMT

8. Conditional
=IF
Alt + T + U + T
Alt + T + O + F
Ctrl + {

9. Refresh Worksheet / Calculate Formulas
=MINIMUM
F9
MAX
COUNT

10. Highlight Dependents
Ctrl + }
=Sum
One Variable data table
Alt + E + S + R

11. A model used to investigate dynamic behavior
Stimulation Model
=AND
NOW
CTRL + ]

12. Advanced Data Filter
Alt + W + M + U
F5
Alt + D + F + A
=COUNTIF

13. Track changes
Two Variable data table
ALT + R + G
CTRL + [
AVERAGE

14. Find Based on Row
Relative Ranges
=HLOOKUP
ALT + M + V
Alt + T + U + T

15. Validate Data
Alt + T + U + D
Alt + D + L
Alt + S + C
F9

16. 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
=MAXIMUM
Two Variable data table
SHIFT + CTRL + " (quote)
Mathematical Models

17. Trace immediate precedents
Alt + T + U + T
ALT + M + P
=HLOOKUP
=MAXIMUM

18. 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 + W + M + R
ALT + M + D
Shift + Ctrl + {
SUM

19. Maximum
Mathematical Models
FV
Two Variable data table
=MAXIMUM

20. Absolute Value
=ABS
MAX
=SUMIF
DATE

21. Used for basic calculations - stored by the excel document within specific cells or across different ranges of cells
Two Variable data table
Formulas
Alt + D + P
CTRL + ~

22. Highlight Precedents
SUM
LN
Ctrl + {
Alt + T + U + D

23. Sum Adjacent Cells
ALT + R + G
Alt + W + M + U
=OR
Alt + =

24. Find Based on Column
ALT + M + A + A
=VLOOKUP
Alt + E + S + R
SUM

25. Find Item
Two Variable data table
=INDEX
Shift + Ctrl + {
ALT + "="

26. 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.
AVERAGE
=SUMIF
MIN
Absolute Ranges

27. Ranges that have a $ character before the column portion of the reference and/or the row portion of the reference.
ALT + W + G
AVERAGE
Absolute Ranges
=COUNT

28. Count Number of Entries
=COUNT
Stimulation Model
Alt + S + C
=OFFSET

29. Trace Precedents
F9
=AND
Alt + E + S + L
Alt + T + U + T

30. Paste Link
F9
Alt + D + F + A
Alt + W + M + R
Alt + E + S + L

31. Insert AutoSum formula
Ctrl + ~ + =
SHIFT + CTRL + }
ALT + "="
Alt + D + F + A

32. Displays the standard deviation of the numbers contained in the range of the argument.
Absolute Ranges
STDE
Alt + E + S + L
F9

33. Use Relative References
Alt + W + M + U
Alt + W + M + V
F9
=VLOOKUP

34. Go to precedent cells
Alt + T + U + A
AVERAGE
Ctrl + R
CTRL + [

35. Go to dependent cells
LN
CTRL + ]
F4
ALT + W + G

36. Go to Constants
F5
Shift + F2
VAR
=IF

37. Conditional Count
=COUNTIF
CTRL + ]
Stimulation Model
PV

38. View Macros
ALT + M + A + A
Mathematical Models
Alt + W + M + V
Alt + T + U + T

39. Go to Formulas
DATE
=COUNTIF
F5
COUNT

40. Remove tracing arrows
Alt + D + P
ALT + M + A + A
Alt + E + S + L
Ctrl + {

41. Select From List
=ABS
CTRL + [
=CHOOSE
Formulas

42. Show all Dependents
Formulas
CTRL + ]
Shift + Ctrl + }
Alt + S + C

43. Displays the date in a variety of formats.
MAX
ALT + "="
DATE
FV

44. At Least 1 Must Be True
=OR
=OFFSET
Alt + D + T
SQRT

45. Zoom to selection
Alt + T + U + A
ALT + W + G
SUM
Alt + E + S + R

46. Displays the current date or time based on the computer's clock.
=MAXIMUM
ALT + M + V
Alt + W + M + U
NOW

47. Create Pivot Table
Alt + W + M + V
Alt + W + M + U
Stimulation Model
Alt + D + P

48. 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
One Variable data table
F5
Alt + E + S + L

49. 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.
MAX
Alt + D + T
=Sum
SHIFT + CTRL + {

50. Copy formula from above cell