Advanced Formulas and Macros Flashcards
6 cards from real Microsoft Excel practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 6 Advanced Formulas and Macros flashcards as text
What is AGGREGATE's advantage over SUM and AVERAGE?
Answer: It can ignore errors, hidden rows, and nested SUBTOTAL results
AGGREGATE performs 19 different functions while optionally ignoring error values, hidden rows, and nested functions.
In VBA, when would you use a Collection over an Array?
Answer: Collections dynamically grow, support keys, and don't need dimension management
Collections grow automatically, support string key lookups, and don't require managing dimensions.
What does the SWITCH function do?
Answer: Evaluates an expression against a list of values and returns the first match
SWITCH compares an expression against value-result pairs, returning the result for the first match.
How does CHOOSECOLS work in Excel 365?
Answer: Returns specific columns from an array by column number
CHOOSECOLS extracts specific columns from an array by their column numbers.
What does Application.ScreenUpdating control in VBA?
Answer: Screen refresh during macro execution for performance
Setting it to False prevents screen redrawing during macro execution, speeding up macros significantly.
What does the MAP function do in Excel 365?
Answer: Applies a LAMBDA function to each array element
MAP applies a LAMBDA to every element in one or more arrays and returns the results.
Written and edited by Nathan Beja, founder of Practice Test Geeks, with AI assistance. Exam facts are checked against the official sources linked below. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- store.certiport.com — Fees, retake fee · verified Nov 2026
- certiport.pearsonvue.com — Renewal, score validity, score release, exam format · verified Nov 2026
- learn.microsoft.com — Test delivery, time limit, eligibility · verified Nov 2026