Excel Interview Questions That Actually Get Asked (With Answers)

I’ve sat on both sides of the Excel screening interview, and the questions barely change from company to company. What changes is how candidates answer them - and most people lose points not on the facts, but on the follow-up they didn’t see coming. Below are 17 questions that actually get asked, with a model answer for each and the trap the interviewer is listening for. (For handling the conversation itself - nerves, phrasing, the live test - see our guide to acing the Excel interview.)

Basic Questions (Screening Round)

1. What’s the difference between relative, absolute, and mixed cell references?

Model answer: A relative reference like A1 shifts when you copy the formula. An absolute reference like $A$1 is locked in both directions. A mixed reference locks only the row (A$1) or only the column ($A1). Press F4 while editing to cycle through all four states.

The trap: Mixed references. Most candidates nail relative vs. absolute, then can’t explain when you’d lock only one dimension. Have an example ready: a multiplication table where the formula =$A2*B$1 fills both directions correctly.

2. What does the $ sign do in a formula?

Same territory as above, but interviewers use it to check whether you type dollar signs or use F4. Saying “I press F4 to toggle them” signals fluency. On a Mac, that’s Fn+F4 or Cmd+T depending on setup - knowing this tells them you’ve actually worked cross-platform.

3. How do you keep header rows visible while scrolling?

Model answer: View tab → Freeze Panes. Select the cell below and to the right of what you want locked, then Freeze Panes. Freeze Top Row and Freeze First Column are the one-click shortcuts.

The trap: The selection rule. Candidates who say “you click freeze panes” without explaining that Excel freezes everything above and left of the active cell reveal they’ve only ever used Freeze Top Row.

4. What’s the difference between a function and a formula?

Model answer: A formula is anything starting with =. A function is a named, built-in operation like SUM or VLOOKUP used inside a formula. =A1+A2 is a formula with no functions; =SUM(A1:A2) is a formula containing one.

5. How would you remove duplicate records from a dataset?

Model answer: Data tab → Remove Duplicates, choosing which columns define a “duplicate.” For a non-destructive approach, =UNIQUE(range) spills a deduplicated list without touching the source.

The trap: Destructiveness. Strong candidates mention copying the data first, or using UNIQUE/conditional formatting to inspect duplicates before deleting. Interviewers flinch at anyone who deletes data as step one.

6. What Excel keyboard shortcuts do you use daily?

Don’t recite Ctrl+C. Name workflow shortcuts: Ctrl+Shift+L (toggle filters), Ctrl+Arrow (jump to data edge), Ctrl+Shift+Arrow (select to edge), Alt+= (AutoSum), F4 (repeat last action / toggle references). Rehearse a handful the night before so they come out without pausing.

Intermediate Questions (The Real Filter)

7. Explain VLOOKUP. What are its limitations?

Model answer: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) finds a value in the first column of a range and returns a value from a column to its right. Limitations: it can’t look left, the column index breaks when columns are inserted, it’s slow on huge ranges, and the fourth argument defaults to approximate match - a silent data killer.

The trap: The fourth argument. If you don’t volunteer that omitting FALSE gives approximate match and can return wrong values without an error, the interviewer will ask - and your hesitation answers for you.

8. INDEX/MATCH vs. VLOOKUP - which do you prefer and why?

Model answer: INDEX/MATCH: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). It looks in any direction, survives column insertions because it references ranges instead of a hardcoded column number, and recalculates faster because it only touches two columns instead of the whole table.

The trap: Saying “INDEX/MATCH is better” without mentioning XLOOKUP. Since Excel 2021/365, XLOOKUP does everything INDEX/MATCH does with cleaner syntax and exact match by default. The best answer compares all three - here’s our full XLOOKUP vs. VLOOKUP vs. INDEX/MATCH breakdown. Knowing XLOOKUP exists but explaining why a legacy-Excel shop still needs INDEX/MATCH is the senior answer.

9. What’s the difference between COUNT, COUNTA, COUNTIF, and COUNTIFS?

Function	Counts
COUNT	Numeric values only
COUNTA	Any non-blank cell
COUNTIF	Cells meeting one condition
COUNTIFS	Cells meeting multiple AND conditions

The trap: “How would you count rows matching condition A or condition B?” COUNTIFS is AND-only; the answer is two COUNTIFs added together (minus the overlap), or SUMPRODUCT.

10. How do you handle errors like #N/A in lookup formulas?

Model answer: Wrap the lookup in IFERROR: =IFERROR(VLOOKUP(...), "Not found"). Use IFNA when you only want to catch missing lookups - IFERROR also swallows #REF! and #DIV/0!, which can hide genuine formula bugs.

The trap: The IFNA distinction. Reaching for IFERROR everywhere is the mark of someone who silences errors rather than handles them.

11. SUMIF vs. SUMIFS - what’s the syntax gotcha?

Model answer: The argument order flips. SUMIF(range, criteria, sum_range) puts the sum range last; SUMIFS(sum_range, criteria_range1, criteria1, ...) puts it first. I just use SUMIFS for everything, single condition included - one syntax, no mistakes.

12. How would you restrict what users can enter in a cell?

Model answer: Data Validation - a dropdown from a list, whole-number ranges, date limits, or a custom formula. Pair it with input messages and error alerts.

The trap: “Is data validation security?” No - paste operations can overwrite it, and it doesn’t protect the sheet. Mention sheet protection as the complement and you’ve cleared the bar.

13. What is conditional formatting and where have you used it?

Tie it to a real scenario: highlighting overdue invoices, flagging variances over a threshold, duplicate detection. The strongest answers mention formula-based rules (=$D2>TODAY()) rather than just the preset color scales - that’s where conditional formatting becomes an analysis tool instead of decoration.

Advanced Questions (Analyst Roles)

14. Walk me through building a pivot table. What is the pivot cache?

Model answer: Select the data, Insert → PivotTable, drag fields into Rows, Columns, Values, and Filters. Behind the scenes Excel copies the source data into the pivot cache, a hidden in-memory snapshot. That’s why a pivot doesn’t update when the source changes until you hit Refresh - and why two pivots built from the same source share one cache, so grouping changes in one can affect the other. Deliberately creating separate caches costs memory but decouples the pivots.

The trap: The cache is the whole question. Anyone can drag fields; the interviewer wants to hear why Refresh exists and what shared caches imply. Our complete pivot table guide covers the mechanics end to end.

15. Your workbook has gotten slow. How do you diagnose it?

Model answer: Check for volatile functions (NOW, TODAY, OFFSET, INDIRECT, RAND) that recalculate on every change; full-column references like A:A inside SUMPRODUCT or array formulas; excessive conditional formatting rules; and a used range bloated far beyond the actual data (Ctrl+End reveals it). Switch to manual calculation (Formulas → Calculation Options) while diagnosing, and replace volatile lookups with INDEX, which is non-volatile.

The trap: Naming “volatile functions” as a category. That phrase alone separates power users from intermediate ones.

16. What are dynamic arrays and what does a #SPILL! error mean?

Model answer: In Excel 365, functions like UNIQUE, FILTER, SORT, and SEQUENCE return multiple values that spill into neighboring cells from one formula. A #SPILL! error means the spill range is blocked - usually by existing data or a merged cell. Clear the obstruction; don’t wrap it in error handling.

17. What-if analysis: when would you reach for Goal Seek vs. Data Tables vs. Scenario Manager?

Model answer: Goal Seek reverse-solves one input for one target output (“what sales volume hits break-even?”). Data Tables show output across a sweep of one or two inputs. Scenario Manager stores named sets of multiple inputs (best/base/worst case). All live under Data → What-If Analysis.

The trap: Goal Seek’s limit - one input variable only. If the interviewer asks “and for multiple variables?”, the answer is Solver.

How to Use These in Your Prep

Don’t memorize answers - reproduce them. Open a blank workbook and actually build the multiplication table from question 1, break a VLOOKUP by inserting a column, and watch two pivot tables share a cache. Interviewers can hear the difference between recalled text and recalled experience within one follow-up question, and the live Excel test that follows most of these screenings only rewards the second kind.

FAQ

What Excel level do most job postings actually require?

“Proficient” usually means questions 1-13 here: references, lookups, COUNTIF/SUMIFS, data validation, and basic pivot tables. “Advanced” adds pivot cache behavior, performance diagnosis, dynamic arrays, and usually Power Query or basic VBA.

Will I get a hands-on Excel test or just verbal questions?

Increasingly both. A 10-15 minute verbal screen filters candidates, then a timed practical test (clean a dataset, build a lookup, summarize with a pivot) confirms it. Practice the tasks under time pressure, not just the vocabulary.

Should I mention XLOOKUP if the company might use older Excel?

Yes - knowing XLOOKUP signals current skills. Frame it as “I’d use XLOOKUP in 365, INDEX/MATCH where I have to support Excel 2019 or earlier.” Version awareness is itself a point in your favor.

Is VBA still asked about in interviews?

For analyst and finance roles, occasionally - usually just “have you recorded or edited a macro?” Power Query and dynamic arrays have replaced most VBA questions, but being able to describe recording and editing a simple macro still earns easy credit.

How many of these questions come up in a single interview?

Typically five to eight, escalating until you miss one. Interviewers probe upward from your resume claims - if you wrote “advanced Excel,” expect them to start around question 7 and climb.