Premium
PDF
PDF
Download Download
EXAMS
Practice Test | Questions & Answers (Verified Answers) With Rationales
( Update)
This Document Contains:
100 Questions with Correct, Detailed and Verified Answers
2026/2027 Actual Practice Exam Testbank
Questions & Answers (Verified Answers) With Rationales
100% Guaranteed Pass
Complete A+ Guide
Page 1
,Question 1
Which dynamic array function returns the intersection of two arrays?
A) FILTER
B) UNIQUE
C) SORT
D) XLOOKUP
Answer: A) FILTER
Explanation: FILTER returns rows or columns that meet a condition, effectively an intersection when
applied to two arrays with matching criteria. UNIQUE returns distinct values, SORT
orders data, and XLOOKUP retrieves a single value, not an intersection.
Question 2
In XLOOKUP, which argument specifies the value to return if no match is found?
A) lookup_value
B) lookup_array
C) return_array
D) if_not_found
Answer: D) if_not_found
Explanation: The if_not_found argument in XLOOKUP allows you to specify a value or action when
no match is found. lookup_value is the search term, lookup_array is the range to search,
and return_array is the range from which to return the result.
Question 3
What is the primary benefit of using LET in a complex financial formula?
A) It enables recursive calculations.
B) It reduces redundancy by naming intermediate results.
C) It automatically converts formulas to macros.
D) It forces array evaluation.
Answer: B) It reduces redundancy by naming intermediate results.
Explanation: LET assigns names to intermediate calculations, improving readability and
performance by computing each result only once. It does not enable recursion, convert
to macros, or force array evaluation.
Page 2
,Question 4
When building a LAMBDA function for a financial model, what must be defined?
A) A name and a formula using parameters.
B) A VBA module and a reference.
C) A Power Query connection.
D) A data table and a scenario.
Answer: A) A name and a formula using parameters.
Explanation: LAMBDA requires a name (via Name Manager) and a formula that uses defined
parameters to create a reusable custom function. It does not require VBA, Power Query,
or data tables.
Question 5
Which Power Query transformation is best for unpivoting columns to normalize data?
A) Transpose
B) Unpivot Columns
C) Group By
D) Merge Queries
Answer: B) Unpivot Columns
Explanation: Unpivot Columns converts wide data into a tall format, which is essential for
normalizing data for analysis. Transpose swaps rows and columns, Group By
aggregates, and Merge Queries combines tables.
Question 6
In a two-variable data table, what does Excel vary to produce multiple outcomes?
A) Two input cells
B) One input cell
C) Three input cells
D) No input cells
Answer: A) Two input cells
Explanation: A two-variable data table allows you to see how changing two input cells affects a
result. One-variable tables vary only one input, and three or zero inputs are not
supported for data tables.
Page 3
, Question 7
Which Excel feature is most suitable for creating a dynamic dashboard that updates
automatically?
A) PivotChart with slicers
B) Static charts
C) Conditional formatting
D) Data validation
Answer: A) PivotChart with slicers
Explanation: PivotCharts connected to slicers allow interactive filtering and automatic updates as
data changes. Static charts, conditional formatting, and data validation do not provide
dynamic, interactive visualization.
Question 8
In VBA, which object is used to store a value that can be referenced throughout a module?
A) Variable
B) Constant
C) Function
D) Sub
Answer: A) Variable
Explanation: A variable declared at module level (e.g., with Dim at the top of a module) can store a
value accessible throughout that module. Constants are fixed, functions and subs are
procedures, not storage containers.
Question 9
Which Excel tool helps identify cells that feed into a formula for auditing?
A) Trace Precedents
B) Trace Dependents
C) Evaluate Formula
D) Watch Window
Answer: A) Trace Precedents
Explanation: Trace Precedents draws arrows to cells that are referenced by the active formula, aiding
in auditing. Trace Dependents shows cells that depend on the active cell, Evaluate
Formula steps through calculation, and Watch Window monitors values.
Page 4
Download Download
EXAMS
Practice Test | Questions & Answers (Verified Answers) With Rationales
( Update)
This Document Contains:
100 Questions with Correct, Detailed and Verified Answers
2026/2027 Actual Practice Exam Testbank
Questions & Answers (Verified Answers) With Rationales
100% Guaranteed Pass
Complete A+ Guide
Page 1
,Question 1
Which dynamic array function returns the intersection of two arrays?
A) FILTER
B) UNIQUE
C) SORT
D) XLOOKUP
Answer: A) FILTER
Explanation: FILTER returns rows or columns that meet a condition, effectively an intersection when
applied to two arrays with matching criteria. UNIQUE returns distinct values, SORT
orders data, and XLOOKUP retrieves a single value, not an intersection.
Question 2
In XLOOKUP, which argument specifies the value to return if no match is found?
A) lookup_value
B) lookup_array
C) return_array
D) if_not_found
Answer: D) if_not_found
Explanation: The if_not_found argument in XLOOKUP allows you to specify a value or action when
no match is found. lookup_value is the search term, lookup_array is the range to search,
and return_array is the range from which to return the result.
Question 3
What is the primary benefit of using LET in a complex financial formula?
A) It enables recursive calculations.
B) It reduces redundancy by naming intermediate results.
C) It automatically converts formulas to macros.
D) It forces array evaluation.
Answer: B) It reduces redundancy by naming intermediate results.
Explanation: LET assigns names to intermediate calculations, improving readability and
performance by computing each result only once. It does not enable recursion, convert
to macros, or force array evaluation.
Page 2
,Question 4
When building a LAMBDA function for a financial model, what must be defined?
A) A name and a formula using parameters.
B) A VBA module and a reference.
C) A Power Query connection.
D) A data table and a scenario.
Answer: A) A name and a formula using parameters.
Explanation: LAMBDA requires a name (via Name Manager) and a formula that uses defined
parameters to create a reusable custom function. It does not require VBA, Power Query,
or data tables.
Question 5
Which Power Query transformation is best for unpivoting columns to normalize data?
A) Transpose
B) Unpivot Columns
C) Group By
D) Merge Queries
Answer: B) Unpivot Columns
Explanation: Unpivot Columns converts wide data into a tall format, which is essential for
normalizing data for analysis. Transpose swaps rows and columns, Group By
aggregates, and Merge Queries combines tables.
Question 6
In a two-variable data table, what does Excel vary to produce multiple outcomes?
A) Two input cells
B) One input cell
C) Three input cells
D) No input cells
Answer: A) Two input cells
Explanation: A two-variable data table allows you to see how changing two input cells affects a
result. One-variable tables vary only one input, and three or zero inputs are not
supported for data tables.
Page 3
, Question 7
Which Excel feature is most suitable for creating a dynamic dashboard that updates
automatically?
A) PivotChart with slicers
B) Static charts
C) Conditional formatting
D) Data validation
Answer: A) PivotChart with slicers
Explanation: PivotCharts connected to slicers allow interactive filtering and automatic updates as
data changes. Static charts, conditional formatting, and data validation do not provide
dynamic, interactive visualization.
Question 8
In VBA, which object is used to store a value that can be referenced throughout a module?
A) Variable
B) Constant
C) Function
D) Sub
Answer: A) Variable
Explanation: A variable declared at module level (e.g., with Dim at the top of a module) can store a
value accessible throughout that module. Constants are fixed, functions and subs are
procedures, not storage containers.
Question 9
Which Excel tool helps identify cells that feed into a formula for auditing?
A) Trace Precedents
B) Trace Dependents
C) Evaluate Formula
D) Watch Window
Answer: A) Trace Precedents
Explanation: Trace Precedents draws arrows to cells that are referenced by the active formula, aiding
in auditing. Trace Dependents shows cells that depend on the active cell, Evaluate
Formula steps through calculation, and Watch Window monitors values.
Page 4