• Wrong document? Swap it for free
  • Written by students who passed
  • Immediately available after payment
  • Read online or as PDF
Sell
Where do you study
Your language
Document preview thumbnail
Preview 4 out of 35 pages
Exam (elaborations)

Advanced Microsoft Excel Comprehensive Final Exam 100 Questions

Document preview thumbnail
Preview 4 out of 35 pages

Advanced Microsoft Excel Comprehensive Final Exam 100 Questions

Content preview

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

Document information

Uploaded on
November 14, 2026
Number of pages
35
Written in
2026/2027
Type
Exam (elaborations)
Contains
Questions & answers
$26.49

Wrong document? Swap it for free Within 14 days of purchase and before downloading, you can choose a different document. You can simply spend the amount again.
Written by students who passed
Immediately available after payment
Read online or as PDF

Seller avatar
Reputation scores are based on the amount of documents a seller has sold for a fee and the reviews they have received for those documents. There are three levels: Bronze, Silver and Gold. The better the reputation, the more your can rely on the quality of the sellers work.
NURSING2EXAM
4.5
(704)
Sold
1159
Followers
523
Items
5429
Last sold
1 day ago



Why students choose Stuvia

Created by fellow students, verified by reviews

Quality you can trust: written by students who passed their tests and reviewed by others who've used these notes.

Didn't get what you expected? Choose another document

No worries! You can instantly pick a different document that better fits what you're looking for.

Pay as you like, start learning right away

No subscription, no commitments. Pay the way you're used to via credit card and download your PDF document instantly.

Student with book image

“Bought, downloaded, and aced it. It really can be that simple.”

Alisha Student

Working on your references?

Create accurate citations in APA, MLA and Harvard with our free citation generator.

Working on your references?

Frequently asked questions