D388 BUS 2250
Fundamentals of Spreadsheets & Data
Presentation
3.0 Credits
Objective Assessment Review (Qns &
Ans)
2025
©2025
, Question 1:
_A data analyst needs to build a dashboard that dynamically updates when new
data are added. Which Excel feature is best suited to ensure that the source data
ranges automatically expand?_
A. Static cell references
B. Excel Tables (List Objects)
C. Manual range adjustments
D. Fixed named ranges
Correct ANS: B. Excel Tables (List Objects)
Rationale:
Excel Tables automatically expand when new rows or columns are added. They
allow structured referencing, which enables charts, PivotTables, and formulas to
update dynamically as the underlying data changes.
---
Question 2:
_Which combination of functions is most effective for retrieving a value based on a
lookup in both row and column directions within a data matrix?_
A. VLOOKUP and HLOOKUP
B. INDEX and MATCH
C. SUMIF and COUNTIF
D. IF and CONCATENATE
Correct ANS: B. INDEX and MATCH
Rationale:
The INDEX/MATCH combination provides a flexible lookup that works in both
directions. Unlike VLOOKUP (which requires the lookup column to be the first
column) and HLOOKUP (which is limited to horizontal searches), INDEX and MATCH
together enable retrieval from any part of the matrix.
---
Question 3:
©2025
, _When comparing data visualization techniques in spreadsheets, which conditional
formatting tool is best for displaying data gradients?_
A. Data bars
B. Icon sets
C. Color scales
D. Custom cell styles
Correct ANS: C. Color scales
Rationale:
Color scales apply a continuous color gradient based on cell values, which is
excellent for quickly visualizing ranges, trends, and data distribution. Data bars and
icon sets provide visual cues but do not display a continuous gradation.
---
Question 4:
_For aggregating and modeling complex datasets from multiple sources within
Excel, which advanced feature allows users to create relationships between
different tables?_
A. Power Query
B. PivotTables
C. Power Pivot
D. Data Validation
Correct ANS: C. Power Pivot
Rationale:
Power Pivot enables advanced data modeling by allowing users to import data from
multiple sources, establish relationships, and perform complex calculations using
Data Analysis Expressions (DAX). This feature greatly enhances analysis beyond
what standard PivotTables can offer.
---
Question 5:
_An analyst employs an array formula that processes multiple values and returns a
range of results. What defines an array formula compared to a conventional
©2025
Fundamentals of Spreadsheets & Data
Presentation
3.0 Credits
Objective Assessment Review (Qns &
Ans)
2025
©2025
, Question 1:
_A data analyst needs to build a dashboard that dynamically updates when new
data are added. Which Excel feature is best suited to ensure that the source data
ranges automatically expand?_
A. Static cell references
B. Excel Tables (List Objects)
C. Manual range adjustments
D. Fixed named ranges
Correct ANS: B. Excel Tables (List Objects)
Rationale:
Excel Tables automatically expand when new rows or columns are added. They
allow structured referencing, which enables charts, PivotTables, and formulas to
update dynamically as the underlying data changes.
---
Question 2:
_Which combination of functions is most effective for retrieving a value based on a
lookup in both row and column directions within a data matrix?_
A. VLOOKUP and HLOOKUP
B. INDEX and MATCH
C. SUMIF and COUNTIF
D. IF and CONCATENATE
Correct ANS: B. INDEX and MATCH
Rationale:
The INDEX/MATCH combination provides a flexible lookup that works in both
directions. Unlike VLOOKUP (which requires the lookup column to be the first
column) and HLOOKUP (which is limited to horizontal searches), INDEX and MATCH
together enable retrieval from any part of the matrix.
---
Question 3:
©2025
, _When comparing data visualization techniques in spreadsheets, which conditional
formatting tool is best for displaying data gradients?_
A. Data bars
B. Icon sets
C. Color scales
D. Custom cell styles
Correct ANS: C. Color scales
Rationale:
Color scales apply a continuous color gradient based on cell values, which is
excellent for quickly visualizing ranges, trends, and data distribution. Data bars and
icon sets provide visual cues but do not display a continuous gradation.
---
Question 4:
_For aggregating and modeling complex datasets from multiple sources within
Excel, which advanced feature allows users to create relationships between
different tables?_
A. Power Query
B. PivotTables
C. Power Pivot
D. Data Validation
Correct ANS: C. Power Pivot
Rationale:
Power Pivot enables advanced data modeling by allowing users to import data from
multiple sources, establish relationships, and perform complex calculations using
Data Analysis Expressions (DAX). This feature greatly enhances analysis beyond
what standard PivotTables can offer.
---
Question 5:
_An analyst employs an array formula that processes multiple values and returns a
range of results. What defines an array formula compared to a conventional
©2025
