IT – C268 BUIT 2200
Spreadsheets
3.0 Credits
Objective Assessment Review (Qns &
Ans)
2025
©2025
, Question 1:
A financial analyst is working with a large dataset in Excel and needs to
quickly summarize data with the ability to dynamically change the
grouping criteria. Which advanced feature is best suited for this task?
- A. Data Validation
- B. PivotTables
- C. Conditional Formatting
- D. Solver
Correct ANS: B. PivotTables
Rationale:
PivotTables allow users to quickly summarize, aggregate, and rearrange
large datasets. They offer dynamic grouping, enabling analysts to
experiment with different perspectives without altering the raw data.
---
Question 2:
Which function introduced in modern Excel (Office 365/Excel 2021)
returns a dynamic array based on specified criteria, and automatically
“spills” the results into neighboring cells?
- A. UNIQUE
- B. VLOOKUP
- C. IFERROR
- D. CONCATENATE
Correct ANS: A. UNIQUE
Rationale:
The UNIQUE function returns the unique values from a range and, as a
dynamic array function, automatically spills its results, simplifying data
cleansing and analysis.
---
©2025
, Question 3:
An analyst needs to perform a two-dimensional lookup that works both
horizontally and vertically without using multiple nested functions. Which
modern Excel function is designed for this advanced lookup?
- A. INDEX/MATCH
- B. XLOOKUP
- C. VLOOKUP
- D. HLOOKUP
Correct ANS: B. XLOOKUP
Rationale:
XLOOKUP is intended to replace VLOOKUP and HLOOKUP by providing a
single, more powerful function that can search in any direction, return
results from corresponding columns/rows, and handle errors gracefully.
---
Question 4:
When dealing with semi-structured data imported from external sources,
which Excel feature enables users to clean, transform, and reshape the
data before further analysis?
- A. Flash Fill
- B. Power Query
- C. Solver
- D. Data Validation
Correct ANS: B. Power Query
Rationale:
Power Query provides a robust set of transformation tools to parse,
clean, and reshape data from various sources, making it ideal for
preparing messy or semi‑structured data for analysis.
---
Question 5:
©2025
, Which Excel add-in is most effective for solving complex optimization
problems by changing variable values while enforcing specified
constraints?
- A. Solver
- B. Scenario Manager
- C. Goal Seek
- D. Data Analysis Toolpak
Correct ANS: A. Solver
Rationale:
Solver is an optimization tool that finds the optimal solution for a model
by adjusting variable inputs and adhering to defined constraints; it is
widely used for decision and financial models.
---
Question 6:
To automate repetitive tasks and enhance spreadsheet functionality,
which programming language is typically used within Excel?
- A. JavaScript
- B. Python
- C. VBA (Visual Basic for Applications)
- D. SQL
Correct ANS: C. VBA (Visual Basic for Applications)
Rationale:
VBA is the built‑in programming language in Excel that enables advanced
automation, custom functions, and the creation of user forms, which are
crucial for streamlining complex tasks.
---
Question 7:
Which advanced chart type is particularly effective for visualizing
hierarchical data, reflecting proportions within nested categories?
©2025
Spreadsheets
3.0 Credits
Objective Assessment Review (Qns &
Ans)
2025
©2025
, Question 1:
A financial analyst is working with a large dataset in Excel and needs to
quickly summarize data with the ability to dynamically change the
grouping criteria. Which advanced feature is best suited for this task?
- A. Data Validation
- B. PivotTables
- C. Conditional Formatting
- D. Solver
Correct ANS: B. PivotTables
Rationale:
PivotTables allow users to quickly summarize, aggregate, and rearrange
large datasets. They offer dynamic grouping, enabling analysts to
experiment with different perspectives without altering the raw data.
---
Question 2:
Which function introduced in modern Excel (Office 365/Excel 2021)
returns a dynamic array based on specified criteria, and automatically
“spills” the results into neighboring cells?
- A. UNIQUE
- B. VLOOKUP
- C. IFERROR
- D. CONCATENATE
Correct ANS: A. UNIQUE
Rationale:
The UNIQUE function returns the unique values from a range and, as a
dynamic array function, automatically spills its results, simplifying data
cleansing and analysis.
---
©2025
, Question 3:
An analyst needs to perform a two-dimensional lookup that works both
horizontally and vertically without using multiple nested functions. Which
modern Excel function is designed for this advanced lookup?
- A. INDEX/MATCH
- B. XLOOKUP
- C. VLOOKUP
- D. HLOOKUP
Correct ANS: B. XLOOKUP
Rationale:
XLOOKUP is intended to replace VLOOKUP and HLOOKUP by providing a
single, more powerful function that can search in any direction, return
results from corresponding columns/rows, and handle errors gracefully.
---
Question 4:
When dealing with semi-structured data imported from external sources,
which Excel feature enables users to clean, transform, and reshape the
data before further analysis?
- A. Flash Fill
- B. Power Query
- C. Solver
- D. Data Validation
Correct ANS: B. Power Query
Rationale:
Power Query provides a robust set of transformation tools to parse,
clean, and reshape data from various sources, making it ideal for
preparing messy or semi‑structured data for analysis.
---
Question 5:
©2025
, Which Excel add-in is most effective for solving complex optimization
problems by changing variable values while enforcing specified
constraints?
- A. Solver
- B. Scenario Manager
- C. Goal Seek
- D. Data Analysis Toolpak
Correct ANS: A. Solver
Rationale:
Solver is an optimization tool that finds the optimal solution for a model
by adjusting variable inputs and adhering to defined constraints; it is
widely used for decision and financial models.
---
Question 6:
To automate repetitive tasks and enhance spreadsheet functionality,
which programming language is typically used within Excel?
- A. JavaScript
- B. Python
- C. VBA (Visual Basic for Applications)
- D. SQL
Correct ANS: C. VBA (Visual Basic for Applications)
Rationale:
VBA is the built‑in programming language in Excel that enables advanced
automation, custom functions, and the creation of user forms, which are
crucial for streamlining complex tasks.
---
Question 7:
Which advanced chart type is particularly effective for visualizing
hierarchical data, reflecting proportions within nested categories?
©2025
