IT: C268 BUIT 2200 Spreadsheets
Final Assessment Review
Questions & Solutions
2025
©2025
, Question 1 (Multiple Choice)
Case Study:
A business analyst is developing an interactive dashboard that
consolidates data from multiple sources. To improve data retrieval across
large datasets, she needs a function that can dynamically search and
return matching information with enhanced flexibility.
Question:
Which function best replaces the traditional combination of INDEX and
MATCH by offering dynamic array support and simplified syntax?
a) VLOOKUP
b) HLOOKUP
c) XLOOKUP
d) OFFSET
Correct ANS: c) XLOOKUP
Rationale:
XLOOKUP is a modern, versatile function designed to overcome the
limitations of older lookup methods. It supports searches in any
direction, dynamic arrays, and simplified error handling. This makes it
ideal for integrating data across complex environments, especially when
compared to functions like VLOOKUP or HLOOKUP.
---
Question 2 (Fill in the Blank)
Case Study:
An analyst is tasked with extracting multiple matching values from a
dataset without repeatedly copying formulas into adjacent cells. The new
©2025
,dynamic array capabilities in Excel can "spill" results automatically.
Question:
In Excel, the ______ function introduced with dynamic arrays allows
users to filter a range based on specified criteria and automatically spill
the results into adjacent cells.
Correct ANS: FILTER
Rationale:
The FILTER function takes advantage of Excel’s dynamic array capabilities
by returning multiple records that meet the criteria, without requiring
individual formulas for each extracted value. This improves efficiency and
reduces errors in data extraction tasks common in advanced spreadsheet
applications.
---
Question 3 (True/False)
Case Study:
A data analyst creates a pivot table to summarize monthly sales data and
then adds a slicer to provide interactive filtering options.
Question:
True or False: Slicers in Excel can be connected to only one pivot table at
a time.
Correct ANS: False
Rationale:
Modern versions of Excel allow slicers to control multiple pivot tables
simultaneously—as long as those pivot tables share the same data
source—thereby enhancing interactivity and consistency across multiple
data summaries.
©2025
, ---
Question 4 (Multiple Response)
Case Study:
A financial controller is designing a robust spreadsheet for financial
models that may produce errors during complex calculations. To improve
the integrity and readability of the workbook, the controller looks to
integrate error-handling functions.
Question:
Which of the following functions are available in Excel for handling errors
in formulas? (Select all that apply.)
a) IFERROR
b) ISERROR
c) NVL
d) IFNA
e) CATCHERROR
Correct ANSs: a) IFERROR, b) ISERROR, d) IFNA
Rationale:
Excel provides multiple error-handling functions: IFERROR returns an
alternate result when an error is found; ISERROR checks if a cell contains
an error; and IFNA specifically handles the N/A error. Functions like NVL
(from SQL) and CATCHERROR do not exist in Excel.
---
Question 5 (Multiple Choice)
Case Study:
A business analyst needs to extract a specific value from a two-
dimensional data range where the desired value lies at the intersection
©2025
Final Assessment Review
Questions & Solutions
2025
©2025
, Question 1 (Multiple Choice)
Case Study:
A business analyst is developing an interactive dashboard that
consolidates data from multiple sources. To improve data retrieval across
large datasets, she needs a function that can dynamically search and
return matching information with enhanced flexibility.
Question:
Which function best replaces the traditional combination of INDEX and
MATCH by offering dynamic array support and simplified syntax?
a) VLOOKUP
b) HLOOKUP
c) XLOOKUP
d) OFFSET
Correct ANS: c) XLOOKUP
Rationale:
XLOOKUP is a modern, versatile function designed to overcome the
limitations of older lookup methods. It supports searches in any
direction, dynamic arrays, and simplified error handling. This makes it
ideal for integrating data across complex environments, especially when
compared to functions like VLOOKUP or HLOOKUP.
---
Question 2 (Fill in the Blank)
Case Study:
An analyst is tasked with extracting multiple matching values from a
dataset without repeatedly copying formulas into adjacent cells. The new
©2025
,dynamic array capabilities in Excel can "spill" results automatically.
Question:
In Excel, the ______ function introduced with dynamic arrays allows
users to filter a range based on specified criteria and automatically spill
the results into adjacent cells.
Correct ANS: FILTER
Rationale:
The FILTER function takes advantage of Excel’s dynamic array capabilities
by returning multiple records that meet the criteria, without requiring
individual formulas for each extracted value. This improves efficiency and
reduces errors in data extraction tasks common in advanced spreadsheet
applications.
---
Question 3 (True/False)
Case Study:
A data analyst creates a pivot table to summarize monthly sales data and
then adds a slicer to provide interactive filtering options.
Question:
True or False: Slicers in Excel can be connected to only one pivot table at
a time.
Correct ANS: False
Rationale:
Modern versions of Excel allow slicers to control multiple pivot tables
simultaneously—as long as those pivot tables share the same data
source—thereby enhancing interactivity and consistency across multiple
data summaries.
©2025
, ---
Question 4 (Multiple Response)
Case Study:
A financial controller is designing a robust spreadsheet for financial
models that may produce errors during complex calculations. To improve
the integrity and readability of the workbook, the controller looks to
integrate error-handling functions.
Question:
Which of the following functions are available in Excel for handling errors
in formulas? (Select all that apply.)
a) IFERROR
b) ISERROR
c) NVL
d) IFNA
e) CATCHERROR
Correct ANSs: a) IFERROR, b) ISERROR, d) IFNA
Rationale:
Excel provides multiple error-handling functions: IFERROR returns an
alternate result when an error is found; ISERROR checks if a cell contains
an error; and IFNA specifically handles the N/A error. Functions like NVL
(from SQL) and CATCHERROR do not exist in Excel.
---
Question 5 (Multiple Choice)
Case Study:
A business analyst needs to extract a specific value from a two-
dimensional data range where the desired value lies at the intersection
©2025
