Excel Functions, Charts, and Data Analysis: Comprehensive Quiz for Students

1. What error will you get if you type a function name incorrectly? A) #FUNCTION? B) #VALUE? C) #ERROR? D) #NAME?

D) #NAME?

2. Which of the following will not return the system clock's date? A) NOW() B) TODAY() C) Ctrl+; D) DATE()

D) DATE()

3. Which of the following is not a valid test in an IF statement? A) A4-B4 B) A4A4 D) B4<=B4

A) A4-B4

4. Which axis displays descriptive labels for data points? A) Category axis B) Value axis C) Z-axis D) Y-axis

A) Category axis

5. Which chart element cannot be chosen from the Chart Elements dialog box? A) Axis Titles B) Chart Title C) Format Back Wall D) Error Bars

C) Format Back Wall

6. What can you not do from the Select Data Source dialog box? A) Change chart data range B) Delete a data series C) Change chart type D) Add a data series

C) Change chart type

7. Which is not a characteristic of a table? A) Can contain multiple ranges B) Helps manage information C) Helps analyze information D) Contains related data

A) Can contain multiple ranges

8. What is not true about creating a data table? A) No duplicate column labels B) Leave one blank row below labels C) Format field names visually D) Keep field names short

B) Leave one blank row below labels

9. Which filter option is not on the date filter submenu? A) First Quarter B) Tomorrow C) Between D) Last Month

A) First Quarter

10. Which filter option is not on the text filter submenu? A) Begins With B) Includes C) Contains D) Custom Filter

B) Includes

11. Which is not a conditional formatting option? A) Data Bars B) Color Scales C) Icon Sets D) Table Format

D) Table Format

12. Which conditional formatting option applies formatting to values between two numbers? A) Top/Bottom Rules B) Highlight Cells Rules C) Between Values Rules D) Color Scales

B) Highlight Cells Rules

13. If a cell fails a conditional formatting test, what happens? A) Formatted for FALSE B) Default FALSE format C) Not formatted D) Green triangle appears

C) Not formatted

14. What cannot be found on the Manage Rules dialog box? A) Delete Rule B) Copy Rule C) New Rule D) Edit Rule

B) Copy Rule

15. What does a + button indicate in an outline? A) Expand details B) Row modified C) Collapse details D) Calculation exists

A) Expand details

16. Which function is not a Subtotal function? A) MAX B) SUM C) AGGREGATE D) AVERAGE

C) AGGREGATE

17. A(n) ________ is an interactive table used to consolidate and summarize information. A) List B) PivotTable C) Table D) Range

B) PivotTable

18. In a Recommended PivotTable, which column is most likely used as values? A) Discipline B) Area C) Units Sold D) Both A & B

C) Units Sold

19. Which is true about creating a blank PivotTable? A) Only from a table B) Not from a range C) Not from external data D) Can be on same sheet as source

D) Can be on same sheet as source

20. Moving a field from ROWS to COLUMNS would ________. A) Divide values B) Consolidate data C) Have no impact D) Make table twice as long

A) Divide values

21. How do you change SUM to AVERAGE in VALUES? A) Type =AVERAGE B) Click AVERAGE on Analyze tab C) Right-click and choose AVERAGE D) Value Field Settings

D) Value Field Settings

22. How do you add a date filter to a PivotTable? A) Remove Date B) Drag Date to ROWS and group C) Drag Date to FILTERS D) Must use slicer

C) Drag Date to FILTERS

23. Which is not true about the PivotTable Fields pane? A) Displays fields B) Table named Table1 C) Displays areas D) Allows layout creation

B) Table named Table1

24. A PivotTable timeline can filter by all except ________. A) Decade B) Day C) Quarter D) Year

A) Decade

25. % of Grand Total displays values as ________. A) % of grand total B) % of column total C) % of row total D) % of parent row

A) % of grand total

26. Which is not controlled by a PivotTable style? A) Shading B) Borders C) Bold fonts D) Subtotal calculations

D) Subtotal calculations

27. PivotTable styles are selected from ________. A) PivotTable wizard B) Mini-toolbar C) Style gallery D) Font group

C) Style gallery

28. Banded rows ________. A) Band subtotals B) Shade odd/even rows C) Keep totals together D) Add row lines

B) Shade odd/even rows

29. The slicer ________ is text in the slicer header. A) Title B) Name C) Caption D) Label

C) Caption

30. Which character is not allowed in a range name? A) _ B) z C) ! D) 1

C) !

31. Benefit of range names? A) Easier to remember B) More accurate C) More portable D) Required for financial functions

A) Easier to remember

32. If Quantity=6 and Unit_Price=3, =QuantityUnit_Price returns: A) QuantityUnit_Price B) 18 C) 9 D) #ERROR

D) #ERROR

33. To add a column header in a one-variable data table: A) Type in cell B) Custom format C) Custom format with quotes D) Use analysis dialog

C) Custom format with quotes

34. A one-variable data table can be built ________. A) Vertically B) Horizontally C) In column A D) Either A or B

D) Either A or B

35. ________ enables experimenting with variables. A) PivotTables B) What-if analysis C) Goal Seek D) Solver

B) What-if analysis

36. Where do you set up substitution values in a two-variable data table? A) First row & column B) Series dialog C) Bottom row & last column D) Excel does it

A) First row & column

37. Which is not part of Goal Seek? A) Set cell B) To value C) By changing cell D) Function

D) Function

38. What is a scenario? A) Two-variable goal seek B) Any what-if analysis C) Tool that changes one variable D) Set of possible situations

D) Set of possible situations

39. What is an Excel Add-in? A) All what-if tools B) Printer drivers C) Program that enhances Excel D) Default feature

C) Program that enhances Excel

40. A(n) ________ specifies the formula cell optimized by Solver. A) Objective cell B) Set value C) Target cell D) Variable cell

A) Objective cell

41. A $50,000 down-payment limit is a ________. A) Variable B) Set value C) Constraint D) Objective

C) Constraint

42. Best tool to maximize profit? A) Goal Seek B) Solver C) Two-variable table D) Scenario Manager

B) Solver

43. Solver does not support which calculation? A) Maximize B) Range C) Minimize D) Exact value

B) Range

44. Which formula returns the day of the month? A) DAYS B) DAYMONTH C) IF(DAYMONTH...) D) DAY

D) DAY

45. If B3 = 9/3/2021, =MONTH(B3) returns: A) 44442 B) September C) 9/30/2021 D) 9

D) 9

46. Function that averages with multiple criteria: A) AVERAGEIF B) AVERAGEIFS C) IFAVERAGE D) IFSAVERAGE

B) AVERAGEIFS

47. What is a nested function? A) More than one argument B) Any IF C) Function inside another D) More than two arguments

C) Function inside another

48. Which is not true about COUNTIF? A) As many arguments as conditions B) Statistical function C) Counts cells meeting condition D) Two arguments

A) As many arguments as conditions

49. Function that returns TRUE only if all conditions are true: A) NOT B) OR C) NOR D) AND

D) AND

50. Function that sums with multiple criteria: A) SUMIFA B) SUMIFS C) TOTALIF D) TOTALIFS

B) SUMIFS

51. Function that returns present value: A) PV B) CV C) PMT D) VALUE

A) PV

52. A ________ chart compares values across regions. A) GPS B) Location C) Google Earth D) Map

D) Map

53. COUNTIF takes how many arguments? A) 1 B) 2 C) 3 D) Depends

B) 2

54. If no logical tests in IFS are TRUE, Excel displays: A) #NAME? B) #VALUE! C) #ERR D) #NA

D) #NA

55. Function returning total present value of future payments: A) PV B) PMT C) IPMT D) FV

A) PV

56. Function calculating number of payment periods: A) NPER B) RATE C) PV D) IPMT

A) NPER

57. Function calculating periodic rate: A) IPMT B) RATE C) NPER D) PV

B) RATE

58. Paying extra principal on a mortgage: A) No impact on interest B) Saves interest & shortens loan C) Increases interest D) No impact at all

B) Saves interest & shortens loan

59. Relative Standing measures ________. A) Median B) Quartile rank C) Standard deviation D) Rank among values

D) Rank among values

60. Function for sample standard deviation: A) STDEVA B) VARA C) STDEV.P D) STDEV.S

D) STDEV.S

61. Strongest positive correlation: A) 0 B) 0.5 C) -1 D) 1

D) 1

62. Function returning population stdev including text: A) STDEVA B) STDEV.P C) STDEVPA D) STDEV.S

C) STDEVPA

63. Square root of variance is ________. A) Correlation B) P-value C) Standard deviation D) T-test

C) Standard deviation

64. Function measuring simultaneous variation: A) STDEV.S B) COVARIANCE.S C) CORREL D) STDEV.P

B) COVARIANCE.S

65. Simplest way to create a histogram: A) Frequency chart B) Histogram C) Standard deviation D) Distribution chart

B) Histogram

66. A ________ extrapolates future values. A) ANOVA B) Forecast worksheet C) Standard deviation D) Frequency

B) Forecast worksheet

67. ________ returns forecasted values using exponential smoothing. A) FORECAST.ETS.CONFINT B) FORECAST.ETS C) FORECAST.ETS.SEASONALITY D) FORECAST.LINEAR

B) FORECAST.ETS

68. In y = mx + b, slope is ________. A) m B) x C) b D) y

A) m

69. If worksheets are grouped, how do you ungroup? A) Click any tab B) Ungroup on Data tab C) Right-click → Ungroup Sheets D) Select any range

C) Right-click → Ungroup Sheets

70. If Sheet1 & Sheet2 are grouped and you enter a formula in B3 of Sheet1: A) No change B) Sheet2 B3 gets same formula C) Warning appears D) Only formatting changes

B) Sheet2 B3 gets same formula

71. A hyperlink can do all except ________. A) Open workbook B) Switch worksheets C) Open webpage D) Launch Word

D) Launch Word

72. A visited hyperlink is ________. A) Purple & underlined B) Red & bold C) Green & underlined D) Blue & underlined

A) Purple & underlined

73. Which is not an Arrange All option? A) Tiled B) Horizontal C) Side by Side D) Cascade

C) Side by Side

74. Worksheet name in a 3-D reference is enclosed in ________. A) " " B) ' ' C) D) \ \

B) ' '

75. If you move an endpoint sheet in a 3-D reference: A) Must update reference B) Excel prompts C) Nothing changes D) Excel adjusts reference

D) Excel adjusts reference

76. Which contains a correct 3-D reference? A) =B4+1st Quarter:'B3 B) =Sales-Expenses C) =B4+'1st Quarter'!B3 D) =[Sales]-[Expenses]

C) =B4+'1st Quarter'!B3

77. If source file is edited while destination is open: A) Source error B) Destination asks to update C) Destination updates D) Antivirus blocks

C) Destination updates

78. Character separating sheet name from cell reference: A) ! B) [] C) '' D) :/

A) !

79. Tool that walks through nested formula steps: A) Evaluate Formula B) Trace Dependents C) Trace Precedents D) IFERROR

A) Evaluate Formula

80. Green triangle means ________. A) Precedent B) Source lost C) Syntax error D) Logic error

D) Logic error

81. Which function has no syntax error? A) =AVERAGE(A1:A9) B) =avg(A1:A9) C) =AVERAG(A1,A9) D) =AVERAGE(A1;A9)

A) =AVERAGE(A1:A9)

82. In =B2*B7 in C7, dependent cell is ________. A) B2 B) C7 C) B3 D) B7

B) C7

83. Blue arrows in tracing precedents indicate ________. A) Errors B) Validation rules C) No errors D) Watch windows

C) No errors

84. Feature that warns users from entering invalid data: A) Watch Window B) Formula Auditing C) Data Validation D) Tracing Precedents

C) Data Validation

85. Which is not an Error Style? A) Stop B) Critical C) Warning D) Information

B) Critical

86. Method to validate pasted data: A) Post-input check B) Circle Invalid Data C) Error tracing D) Validity tool

B) Circle Invalid Data

87. Function returning value at row/column intersection: A) MATCH B) INDEX C) VLOOKUP D) IF

B) INDEX

88. Function returning relative position: A) INDEX B) MATCH C) HLOOKUP D) VLOOKUP

B) MATCH

89. Using =MATCH(66,A1:A4,0), result is: A) 4 B) 6 C) 5 D) 3

D) 3

90. Why nest MATCH inside INDEX? A) Handle multiple outcomes B) Find highest/lowest C) Use position from MATCH D) Use value from MATCH

C) Use position from MATCH

91. Criteria range is ________. A) Table being filtered B) Adjacent cells defining conditions C) Filtered range D) Copied range

B) Adjacent cells defining conditions

92. <> means ________. A) Equal B) Not equal C) Greater than D) Less than

B) Not equal

93. For exact match in VLOOKUP, range_lookup should be ________. A) FALSE B) TRUE C) EXACT D) Blank

A) FALSE

94. =RIGHT("Frog Legs",6) returns ________. A) Frog L B) FrogLe C) g Legs D) og Legs

C) g Legs

95. A partially completed workbook used as a model is a ________. A) Theme B) Sub Procedure C) Macro D) Template

D) Template

96. Which is not checked by Accessibility Checker? A) Alt Text B) Merged Cells C) Print Area D) Table Headers

C) Print Area

97. File extension for macro-enabled workbook: A) .xlsb B) .xlsm C) .xltm D) .xlm

B) .xlsm

98. Which is false about recording a macro? A) Absolute references can be recorded B) Pause button exists C) Recorder captures everything D) Plan steps before recording

B) Pause button exists

99. Where store macros for use in any workbook? A) Template B) Normal Document C) Current workbook D) Personal Macro Workbook

D) Personal Macro Workbook

100. Where do you edit a macro? A) Macro editor B) Must create from scratch C) VBA Editor D) Notepad

C) VBA Editor