Excel interview practice test with answers
Try an Excel interview practice test with 10 questions, model answers, and checked formula results. Spot plausible errors and rehearse your reasoning aloud.

Use this Excel interview practice test to rehearse references, formulas, lookups, filters, PivotTables, and error checks. The 10 questions include model answers and two complete worksheet exercises with solutions and expected results. Answer before reading each solution, then explain why a plausible wrong answer is wrong.
A test of results and reasoning
A useful practice test checks both your result and your explanation. These examples use Excel for Microsoft 365, English function names, and comma argument separators. A cell reference is a cell's address; a range is a group of cells.
Keep each model answer out of view during your first attempt. Predict the result, produce the result, and classify any mismatch using the categories below. These are practice questions, not an employer's test or an official proficiency score.
Four diagnostic categories
- Reference error: the formula points to the wrong cell or range.
- Criteria error: a condition is missing or written incorrectly.
- Data-type issue: Excel treats a value as text instead of a number, or vice versa.
- Business-rule error: the formula correctly implements the wrong instruction.
References and stored values
Correct calculations depend on both cell addresses and stored values. Explain what should move when a formula is copied, then distinguish the underlying value from its display.
1. "What changes when you copy a formula that uses A2, $A$2, $A2, or A$2?"
Model answer: A2 is relative: its row and column can move.
$A$2 is absolute: both are fixed.
$A2 and A$2 are mixed references: the first fixes the column;
the second fixes the row. Choose according to what should stay fixed.
Interviewer listens for: Reference choices tied to the calculation.
Common mistake: Saying a dollar sign fixes the stored value.
It fixes part of the address, not the value.
2. "A cell displays 12%. What value does Excel normally store, and does changing the number format change that value?"
Model answer: A numeric percentage displayed as 12% normally stores
0.12. Number formatting changes the display, not the stored value.
Displaying more decimal places can reveal hidden precision.
Interviewer listens for: Stored value versus presentation.
Common mistake: Assuming two matching displays prove the stored
values are equal.
Two worksheet tasks
These tasks test a fixed reference and a conditional total. SUMIFS adds values meeting all specified conditions; COUNTIFS counts matching rows. Use a separate blank worksheet for each exercise so earlier entries don't affect your checks.
Try each task before opening its solution. For another attempt, use Interview questions: essential spreadsheet tasks. The Work Gym provides browser-based working copies of tools, automatic checking, and a hint naming the mistake.
Practice what this post teaches
3. "Calculate each order's net amount after a fixed discount, and show how you'd copy the formula without moving the discount reference."
Fictional exercise specification
Platform: Excel for Microsoft 365, English functions, comma separators.
Business rule: Net amount = Units * Unit price * (1 - Discount).
Discount: 10%, stored as 0.10. No eligibility threshold applies.
Start on a blank worksheet. Enter this data in A1:C4:
Item Units Unit price
Mugs 4 15
Bags 3 20
Pens 10 2
Set D1 to Net amount.
Set F1 to Discount and F2 to 0.10.
Leave D2:D4, F3, and F4 empty before starting.
Model answer and exact steps:
Enter =B2*C2*(1-$F$2) in D2.
Copy D2 into D3:D4 using the fill handle.
D3 becomes =B3*C3*(1-$F$2).
D4 becomes =B4*C4*(1-$F$2).
Expected output:
Cell Calculation Value
D2 4*15*(1-0.10) 54
D3 3*20*(1-0.10) 54
D4 10*2*(1-0.10) 18
Verification instruction: Enter this formula in F4:
=SUM(D2:D4)
Expected F4: 126.
Interviewer listens for: The discount complement, a fixed discount
reference, and a total check.
Wrong-answer diagnosis: If you instead enter =B2*C2*(1-F2) in D2
and copy D2 to D3, D3 refers to empty F3 and returns 60, not 54.
Inspect D3 before changing the data. Restore the fixed-reference
formula in D2 and copy it into D3:D4 before checking F4 again.
The fill handle is the small square at the selected cell's lower-right corner; drag it down to copy the formula. In the discount task, a wrong second-row result points to the moving reference, not a different discount policy.
4. "Add the amounts for West orders marked Closed, and show how you'd check that both conditions were applied."
Fictional exercise specification
Platform: Excel for Microsoft 365, English functions, comma separators.
Target: Sum Amount only where Region is West AND Status is Closed.
Start on a blank worksheet. Enter this data in A1:C5:
Region Status Amount
West Closed 120
West Open 80
East Closed 90
West Closed 50
Leave E2 and E3 empty before starting.
Model answer and exact steps:
Enter =SUMIFS(C2:C5,A2:A5,"West",B2:B5,"Closed") in E2.
Model calculation: 120 + 50 = 170.
Expected E2: 170. Only worksheet rows 2 and 5 contribute.
Verification instruction: Enter this formula in E3:
=COUNTIFS(A2:A5,"West",B2:B5,"Closed")
Expected E3: 2.
Interviewer listens for: Sum range first, aligned ranges, AND logic,
and a count of matching rows.
Wrong-answer diagnosis: 250 includes the West order marked Open.
260 includes the East order marked Closed. Each result points to
an omitted condition.
Lookup choices
A lookup finds a matching identifier and returns an associated value. An identifier, such as a product code, distinguishes a record, but you still need to check whether the identifier column contains duplicates.
5. "You need to match a product code exactly. What would you check before choosing a lookup formula?"
Model answer: I'd check code uniqueness, consistent data types and
spacing, and how to show missing codes. In Microsoft 365, XLOOKUP
uses exact matching by default. With VLOOKUP, I'd explicitly use
FALSE for an exact match. A nearby code isn't a substitute.
Interviewer listens for: Match rules and duplicate awareness.
Common mistake: Assuming an exact match proves uniqueness.
A lookup can return the first match while duplicates remain.
6. "How would you choose between XLOOKUP, VLOOKUP, and INDEX with MATCH when sharing a workbook?"
Model answer: I'd check the recipient's Excel version first.
XLOOKUP can return values from either side of the lookup column.
VLOOKUP needs the lookup column first in its table range and
returns by column index number. INDEX with MATCH supports older
versions and flexible column positions. For identifiers, I'd
specify exact matching in MATCH.
Interviewer listens for: Compatibility and maintenance risks.
Common mistake: Choosing a function without checking whether the
recipient's version supports it.
Filters and summaries
Filters hide records, while a PivotTable groups source records into an interactive summary of totals or counts. Neither approach removes the need to check which records contribute.
7. "After filtering a worksheet, why might a SUM total still include rows you can't see?"
Model answer: Filtering hides rows; ordinary SUM still includes
their values. For a filter-sensitive total, I'd use a suitable
SUBTOTAL calculation. I'd clarify whether manually hidden rows
should count because SUBTOTAL's function-number choices handle
those rows differently.
Interviewer listens for: Visibility versus underlying values.
Common mistake: Assuming every calculation follows the filter.
8. "How would you summarize order amounts by region in a PivotTable, and what would you check before sharing it?"
Model answer: I'd use one header row and consistent column types,
insert a PivotTable, put Region in Rows, and Amount in Values.
I'd verify Sum rather than Count, refresh after source changes,
check source coverage, and reconcile the grand total with the
source total. Reconciliation means comparing totals and explaining
differences.
Interviewer listens for: Field placement, source checks, and refresh.
Common mistake: Assuming refresh includes records outside a fixed
source range.
Error checks and handoff
A reliable handoff makes errors visible and calculations repeatable. Explain the cause of an error before choosing how the workbook should display it.
9. "A lookup returns #N/A. Would you wrap it in IFERROR immediately?"
Model answer: No. I'd check whether the identifier exists, the
lookup range is correct, data types agree, and spaces affect the
match. Then I'd decide how to display genuinely missing matches.
IFNA handles #N/A specifically; IFERROR handles broader errors
and can hide unrelated faults.
Interviewer listens for: Diagnosis before suppression.
Common mistake: Replacing every error with zero, making a missing
record look like a genuine zero amount.
10. "Your workbook produces the expected total. What would you check before sending it to a colleague?"
Model answer: I'd check source coverage, copied formulas, lookup
exceptions, filters, and summary refreshes. I'd distinguish inputs
from calculated cells, label units, document business rules, and
provide a repeatable reconciliation. Separate errors can offset
one another, so a matching total doesn't prove every row is right.
Interviewer listens for: Reproducibility and row-level checks.
Common mistake: Treating one matching number as complete evidence.
Record your incorrect answer, classify its cause, and retry the affected question without the model answer. This short, formula-focused test doesn't assess large-workbook performance, macros, or every employer's workflow.
Common questions
How can I use this Excel interview practice test without reading the answers first? Ask someone to read each question aloud while keeping the answer out of view. For worksheet tasks, copy only the fictional exercise specification before attempting the calculation.
Which Excel functions should I practice before an interview? Start with SUM, SUMIFS, COUNTIFS, and an exact-match lookup. Practice explaining references and checking eligible rows before moving to error handling and filtered totals.
Can I use an older Excel version for these exercises? The two worksheet exercises use functions available in many older desktop versions. XLOOKUP isn't available in Excel 2016 or Excel 2019, so check the recipient's version before choosing a lookup.
How should I discuss an Excel mistake during an interview? State the intended business rule, describe the mismatch, and identify the cause without guessing. Explain the correction and the check you'd repeat to verify it.
For another rehearsal, use Excel interview practice. The Work Gym's Interview Hub also offers spoken and typed practice with a written report.