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. Highlight Precedents
One Variable data table
Alt + T + O + F
Absolute Ranges
Ctrl + {

2. Find Based on Row
ALT + R + G
Ctrl + {
VAR
=HLOOKUP

3. Show all Precedents
F5
Shift + Ctrl + {
=CHOOSE
=OFFSET

4. Go to Formulas
F5
SHIFT + CTRL + " (quote)
Alt + T + O + F
F4

5. Advanced Data Filter
DATE
Alt + D + F + A
=SUMPRODUCT
Alt + T + U + A

6. Paste Link
F9
Alt + E + S + L
CTRL + ~
Alt + T + U + T

7. Trace all dependents (indirect)
FV
SHIFT + CTRL + }
SHIFT + CTRL + {
CTRL + ' (apostrophe)

8. Conditional Sum
=HLOOKUP
Alt + D + F + A
=SUMIF
Alt + D + P

9. Trace immediate dependents
ALT + M + D
Alt + E + S + R
PV
ALT + M + A + A

10. Trace Dependents
F9
One Variable data table
ALT + W + G
Alt + T + U + D

11. Remove tracing arrows
=HLOOKUP
Shift + Ctrl + }
ALT + M + A + A
PV

12. Go to dependent cells
=VLOOKUP
CTRL + ]
Ctrl + {
MAX

13. Find Based on Column
SHIFT + CTRL + " (quote)
PV
=VLOOKUP
Alt + D + L

14. Go to Formulas
=OFFSET
=SUMPRODUCT
CTRL + ]
F5

15. Minimum
=MINIMUM
=CHOOSE
ALT + M + P
MIN

16. Used for basic calculations - stored by the excel document within specific cells or across different ranges of cells
SHIFT + CTRL + " (quote)
Ctrl + D
Formulas
AVERAGE

17. Trace Precedents
NOW
Alt + T + U + T
=Sum
FV

18. Create Pivot Table
Alt + S + C
SHIFT + CTRL + }
Alt + D + P
Absolute Ranges

19. Record Macros
SHIFT + CTRL + {
ALT + M + D
Alt + W + M + R
Ctrl + R

20. Conditional
=IF
Alt + E + S + R
PMT
SUM

21. 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.
PV
CTRL + [
COUNT
Alt + S + C

22. Sum Numbers
NOW
Alt + W + M + V
=CHOOSE
=Sum

23. Displays the standard deviation of the numbers contained in the range of the argument.
STDE
Alt + W + M + U
=SUMIF
ALT + M + D

24. Find Item
Shift + F3
ALT + M + V
=VLOOKUP
=INDEX

25. Zoom to selection
Alt + W + M + V
F5 + Enter
=
ALT + W + G

26. 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.
SUM
Alt + T + U + T
AVERAGE
PV

27. Copy Formula Right
SQRT
MIN
Alt + T + O + F
Ctrl + R

28. Displays the natural logarithm of a number. For example - =LN(50) will display 1.69897.
F4
LN
=OR
Shift + F2

29. Maximum
Alt + T + U + A
=MAXIMUM
Alt + T + U + D
LN

30. Validate Data
Alt + D + L
CTRL + ~
Alt + W + M + V
=ABS

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
Relative Ranges
F5 + Enter
Two Variable data table
Mathematical Models

32. Select From List
=MATCH
Ctrl + R
=CHOOSE
=MINIMUM

33. All Must Be True
F4
AVERAGE
Ctrl + {
=AND

34. Displays the payment per period needed to repay a loan at a specified interest rate for a specified number of periods.
PMT
Alt + T + U + D
Two Variable data table
Alt + T + O + F

35. Displays the square root of the number identified in the argument. For example =SQRT(C4) will display the square root of the value in C4.
F4
ALT + M + P
=AVERAGE
SQRT

36. Enter Built-In Formula
Shift + F3
Alt + T + U + D
=Sum
F5

37. 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 + D + F + A
MAX
F9
STDE

38. Displays the variance for the numbers contained in the range of the argument.
DATE
VAR
Alt + W + M + R
SHIFT + CTRL + {

39. Find Item's Position
F5
=MATCH
CTRL + ]
Shift + Ctrl + }

40. Displays the future value of a series of equal payments at a fixed rate for a specified number of periods.
CTRL + ~
MIN
=INDEX
FV

41. Evaluate formula
COUNT
ALT + M + V
Alt + D + P
F9

42. Ranges that do not have a $ before the column portion of the reference.
Shift + F2
Alt + T + O + F
STDE
Relative Ranges

43. View Macros
=
Two Variable data table
ALT + M + V
Alt + W + M + V

44. Enter Formula
CTRL + ]
=
Alt + T + O + F
Alt + T + U + A

45. 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.
=INDEX
=IF
SUM
PV

46. Delete Comment
SHIFT + CTRL + " (quote)
Alt + E + A + M
=MAXIMUM
F5

47. Recalculate all workbooks
Shift + Ctrl + {
=VLOOKUP
F9
NOW

48. Refresh Worksheet / Calculate Formulas
Alt + D + T
F9
MAX
Ctrl + R

49. Count Number of Entries
Ctrl + {
Alt + E + S + R
=COUNT
CTRL + ' (apostrophe)

50. Go to precedent cells
=
ALT + M + V
CTRL + [
Ctrl + D