Excel Interview Questions for Experienced Candidates

Excel interview questions for experienced candidates go beyond "what does SUMIF do?". Interviewers expect you to write the formula, and then explain why you chose it, what could break it and how you would check the result. This guide covers twelve questions that often come up for roles where Excel is a daily tool, with model answers worked on a small table you can type in.

How interviews for experienced candidates differ#

For a junior role, getting the right number is usually enough. With a few years of experience, the interviewer listens for three more things:

• Robustness. Will your formula still work when someone adds a column, a row or a new month?
• Judgment. Do you know when a pivot table, a formula or a different tool is the better choice?
• Communication. Can you explain a workbook to someone who didn't build it?

If you are earlier in your career, the Excel interview questions for freshers or the general list of Excel interview questions are a better starting point. Most answers below use this stock table in A1:D7:

Lookup and formula questions (1–4)#

1. A VLOOKUP started returning the wrong column after someone inserted a column. Why, and how do you stop it? VLOOKUP uses a fixed column number, such as 4. Insert a column inside the range and column 4 is now a different field. Use INDEX MATCH or XLOOKUP, which point at the return column directly, so they move with it. =INDEX(D2:D7,MATCH("K-212",A2:A7,0)) returns 220, the first K-212 row. The INDEX function guide and MATCH function guide cover both parts.

2. How do you look up a value with two conditions? Multiply the conditions inside MATCH and look for 1:

=INDEX(D2:D7,MATCH(1,(B2:B7="Bristol")*(C2:C7="Desk"),0))

The result is 18. Each test returns TRUE or FALSE per row, and multiplying gives 1 only where both are true. In Microsoft 365 and Excel 2021 this works with plain Enter; in older versions confirm it with Ctrl+Shift+Enter. Mention the alternative too: a helper column that joins Warehouse and Item, which any lookup can use.

3. How do you return every row that matches, not just the first? Use FILTER, which spills all matching rows:

=FILTER(A2:D7,D2:D7<50,"None")

This returns both desk rows, with stock of 35 and 18. The third argument shows "None" instead of #CALC! when nothing matches. FILTER needs Excel 2021 or Microsoft 365. See the FILTER function guide.

4. A workbook with lookups over 200,000 rows has become slow. What do you check? A good answer lists causes in order. Whole-column references in thousands of formulas, volatile functions such as OFFSET and INDIRECT that recalculate on every change, and the same lookup repeated in many columns. Fixes include one MATCH in a helper column reused by several INDEX formulas, exact ranges or Excel tables, and switching calculation to manual while building.

Data and reporting questions (5–7)#

5. How do you make a pivot table pick up new rows automatically? Turn the source data into an Excel table first (Ctrl+T on Windows, ⌘T on a Mac) and build the pivot from the table. New rows become part of the table, so Data > Refresh All includes them. A pivot built on a fixed range like A1:D7 ignores row 8.

6. Your pivot shows a count instead of a sum. Why? The value column contains text or blank cells, so Excel defaults to Count. Fix the source data, often numbers stored as text, then change Value Field Settings to Sum. Also be ready to explain grouping dates by month and Show Values As > % of Column Total. The pivot table interview questions go deeper.

7. You receive a messy export every week. How do you clean it? For a one-off, use TRIM for spaces, VALUE or Text to Columns for numbers stored as text, and Remove Duplicates. For a weekly job, say you would build the steps once in Power Query and refresh it each week, so the cleaning is repeatable and documented.

Modelling and review questions (8–10)#

8. How do you structure a workbook that other people will use? Separate inputs, calculations and outputs, ideally on different sheets. Keep every assumption, such as a tax rate, in one labelled input cell, never typed inside a formula. Colour input cells, add check cells that should equal zero, and write a short notes sheet.

9. How do you check a workbook someone else built? Show formulas (Ctrl+` on Windows, or Formulas > Show Formulas) to spot hard-coded numbers in formula areas. Use Trace Precedents and Trace Dependents to follow the logic, and Evaluate Formula for long formulas. Then reconcile: do totals match the source system, and do subtotals add up to the grand total?

10. When would you not use Excel? Interviewers like an honest answer. Excel struggles when many people edit the same data at once, when data grows past what a sheet handles comfortably, or when a process needs a full audit trail. A database, a BI tool or a script may then be better, with Excel kept for analysis and reporting.

Scenario questions (11–12)#

11. Tell me about a spreadsheet you built that made a difference. Use a simple structure: the situation, what you built, how it worked and the result. Pick a real example and only quote numbers you can explain, such as "the monthly report went from a full day of copying to a refresh". Be ready for follow-up questions on the formulas you used.

12. A manager says a number in your report is wrong. What do you do? Stay calm and trace it. Find the cell, follow its precedents back to the source, and check filters, date ranges and duplicates. If you made a mistake, say so, fix it and add a check so it can't happen again. This question is about trust as much as Excel.

How to answer well#

Talk while you work. Say what you are about to do, why, and how you will check it. When there are two good methods, name both and explain your choice. If you don't know something, say how you would find out. For the skills employers list most often at this level, see advanced Excel skills for jobs and the harder advanced Excel interview questions.

Practise it#

Knowing the answers is not the same as typing them under pressure. Practise INDEX MATCH in a live spreadsheet: you type the formula and it checks it instantly. Then work through the FILTER function lesson and the advanced pivot table lesson. When you are ready, time yourself on the advanced Excel mock test, or take the Excel level test to see where you stand.