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 the purpose of IFERROR in Excel?
Answer: Returns a specified value if a formula evaluates to an error
IFERROR returns an alternative value if the formula results in any error type.
Which VBA statement redirects execution when an error occurs?
Answer: On Error GoTo
On Error GoTo directs VBA to jump to a labeled line when a runtime error occurs.
What does TEXTJOIN do that CONCATENATE cannot?
Answer: Joins text with a delimiter and can ignore empty cells
TEXTJOIN joins text using a specified delimiter and can skip empty cells.
In VBA, what is the difference between 'ByRef' and 'ByVal'?
Answer: ByRef passes the reference allowing modification, ByVal passes a copy
ByRef (default) passes a reference so changes affect the original; ByVal passes a copy.
What is SUMPRODUCT commonly used for beyond multiplication?
Answer: Conditional counting and summing with multiple criteria without Ctrl+Shift+Enter
SUMPRODUCT evaluates multiple Boolean conditions in arrays and sums results without CSE entry.
How do you create a named range that automatically expands?
Answer: Use OFFSET and COUNTA in the name definition
Combining OFFSET with COUNTA creates a dynamic named range that grows with your data.
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