Excel questions in an interview come in two forms. Some are spoken: “What’s the difference between VLOOKUP and XLOOKUP?” Others are practical: you are handed a laptop and a workbook and given fifteen minutes. In both cases the interviewer is checking the same thing: will your spreadsheets be correct, and will someone else be able to use them after you?
Below are the questions that come up most, split into two levels. Each has a model answer and a note on what makes it strong. The model answers are illustrative, and the facts about functions are from Microsoft’s own support pages. Function names and examples assume an English-language version of Excel.
Excel interview questions for freshers
For graduate, administrative and junior roles, interviewers want to see that you can build a reliable formula and tidy a list without breaking it.
1. “What is the difference between a relative and an absolute reference?”
Model answer: “A relative reference such as A1 changes when I copy the formula. An absolute reference such as $A$1 stays fixed. A mixed reference fixes only the column ($A1) or only the row (A$1). I use absolute references for things like a tax rate or exchange rate in one cell that every row refers to. In the formula bar, F4 cycles through the reference types.”
Why it is strong: it defines all three types and gives a real use. Microsoft’s example shows that a relative A1 copied two cells down and two across becomes C3, while $A$1 stays the same (Microsoft Support).
2. “How would you add up sales for one region only?”
Model answer: “SUMIF if there’s one condition, SUMIFS if there’s more than one. For example, this adds column D where the region is North and the product is Laptops:”
=SUMIFS(D:D, B:B, "North", C:C, "Laptops")Why it is strong: it picks the right function and shows correct syntax. Bonus point: mention the trap Microsoft itself warns about, which is that the sum range comes first in SUMIFS but third in SUMIF, so arguments get mixed up when people switch between them. Text criteria need quotation marks, and each criteria range must be the same size as the sum range (Microsoft Support).
3. “How do you find a value from another table?”
Model answer: “If XLOOKUP is available, I’d use it:”
=XLOOKUP(A2, Products[Code], Products[Price], "Not found")“It does an exact match by default, can return a column to the left of the lookup column, and lets me set a message instead of #N/A. In older versions I’d use VLOOKUP with FALSE for an exact match, or INDEX and MATCH.”
Why it is strong: it is accurate about defaults and versions. Microsoft states that XLOOKUP’s default is an exact match, that VLOOKUP can only look from left to right, and that XLOOKUP is not available in Excel 2016 or 2019 (Microsoft Support). For VLOOKUP, leaving the last argument blank or entering TRUE returns an approximate match, which is a common cause of wrong results (Microsoft Support).
4. “How would you remove duplicates from a list?”
Model answer: “First I’d decide what counts as a duplicate: the same email, or the same email and date? Then I’d copy the original sheet so nothing is lost, and use Data > Remove Duplicates on the right columns. If I needed to keep the original list and see the unique values, I’d use the UNIQUE function in newer versions.”
Why it is strong: the definition and the backup come before the button. That is the habit interviewers want.
5. “What does #N/A mean, and what would you do about it?”
Model answer: “It usually means a lookup didn’t find a match. Before hiding it, I’d check why: extra spaces, numbers stored as text, or a code that really is missing. Once I know the cause, I’d fix the data or use XLOOKUP’s if_not_found argument or IFNA so the report shows something clearer.”
Why it is strong: it diagnoses before it suppresses. Wrapping every formula in IFERROR to hide errors is a red flag to experienced interviewers.
6. “How would you make a list easier to read and use?”
Model answer: “Convert it to an Excel table with Ctrl+T so it gets filters, banded rows and formulas that fill down automatically. Freeze the header row, use consistent number and date formats, and add data validation drop-downs where people type in categories so spellings stay consistent.”
Excel interview questions for experienced candidates
For analyst, finance, operations and senior admin roles, the questions move from “can you” to “how would you build it so it doesn’t break”.
7. “Walk me through building a PivotTable from raw data.”
Model answer: “First I check the source: one header row, unique non-blank column names, no merged cells, data in columns. I format it as a table so new rows are picked up. Then Insert > PivotTable, drag categories to Rows, dates to Columns and numbers to Values. If a numeric field shows as a count, it’s usually because Excel has read it as text, so I’d fix the source. I use Show Values As for percentages of total, and I refresh after the data changes.”
Why it is strong: it starts with data preparation and includes the details that catch people out. Microsoft’s guidance asks for a single row of unique, non-blank headers, no double header rows or merged cells, and notes that values default to SUM but are shown as COUNT if Excel interprets the data as text. PivotTables must be refreshed after the source data changes (Microsoft Support).
8. “Why might you use INDEX and MATCH instead of VLOOKUP?”
Model answer: “VLOOKUP uses a column number, so inserting a column can silently break it, and it can’t look left. With INDEX and MATCH, MATCH finds the position and INDEX returns the value from whichever column I choose, so the formula survives column changes. Today, if everyone has a current version, XLOOKUP does the same job more readably.”
Microsoft describes INDEX using the result of MATCH as its argument: MATCH finds the row, and INDEX returns the value from a chosen column in that row (Microsoft Support).
9. “You receive the same messy export every week. How would you automate the clean-up?”
Model answer: “Power Query. I’d connect to the file or folder, apply the transformations once (remove blank rows, split columns, fix data types, unpivot if needed) and load the result to a table. Next week I just click Refresh and the same steps run again. The source file isn’t changed.”
Why it is strong: it names the right tool and explains the benefit. Microsoft describes Power Query in four phases (connect, transform, combine and load), recording each transformation as a step that runs automatically when the query is refreshed, while the original source remains unchanged (Microsoft Support).
10. “How do you make sure a workbook you hand over is reliable?”
Model answer: “Separate inputs, calculations and outputs on different sheets. Keep assumptions in labelled cells and refer to them, rather than typing numbers into formulas. Use tables and named ranges. Add check totals that should equal zero or match a control figure, and colour-code input cells if the team has a convention. Protect formula cells, and include a short notes sheet explaining sources and refresh steps.”
Why it is strong: this is what separates someone who uses Excel from someone whose files others trust.
11. “When would you not use Excel?”
Model answer: “When the data is too big to work with comfortably, when many people need to edit it at the same time, or when it needs a proper audit trail. Then I’d suggest a database, a BI tool or the company’s system of record, and use Excel for analysis on extracts.”
Interviewers like this question because it shows judgement rather than loyalty to a tool.
12. “Tell me about a spreadsheet you built that made a difference.”
Use the STAR structure. Describe the problem, what you built, and the result in time saved or errors avoided. Keep the function names to one or two; the panel cares about the outcome.
Practical test: what to expect and how to prepare
A practical Excel test usually gives you a small dataset and a list of tasks. Typical tasks, with the skill each one tests:
| Task | Likely approach | Common mistake |
|---|---|---|
| Bring prices in from a second sheet | XLOOKUP, or VLOOKUP with FALSE | Approximate match left on by default |
| Total sales by region and month | SUMIFS or a PivotTable | Sum range in the wrong position |
| Calculate commission at a fixed rate | Formula with an absolute reference to the rate cell | Relative reference that shifts when copied |
| Flag orders over a threshold | IF, plus conditional formatting | Threshold typed into the formula instead of a cell |
| Clean names and codes | TRIM, PROPER, Text to Columns or Power Query | Overwriting the original data |
| Summarise and chart the result | PivotTable and PivotChart | Forgetting to refresh after changes |
Practise on a version close to the one the employer uses, and ask in advance whether it is Microsoft 365 or an older version. It decides whether XLOOKUP and dynamic arrays are available. During the test, keep the original data untouched, label your answers and check one result by hand.
How to describe your Excel level honestly
Avoid “advanced” unless you can back it up. A clearer approach is to name what you have actually done: “I build PivotTable reports and SUMIFS summaries weekly, and I’ve automated two monthly reports with Power Query.” The same applies on your CV. If you are applying for office roles, an administrative assistant resume builder can help you list those skills where screening software and recruiters will see them. To see how others phrase spreadsheet skills, browse resume examples for office and finance roles.
Frequently asked questions
What Excel questions are asked in an interview for freshers?
Expect relative versus absolute references, SUMIF and SUMIFS, lookups with XLOOKUP or VLOOKUP, removing duplicates, handling #N/A errors and formatting a list as a table.
What Excel questions are asked for experienced candidates?
Expect PivotTables from raw data, INDEX and MATCH, automating clean-up with Power Query, building reliable workbooks and judging when Excel is the wrong tool.
Is XLOOKUP available in all versions of Excel?
No. Microsoft states it is not available in Excel 2016 or Excel 2019. It is available in Microsoft 365 and in later perpetual versions.
How do I prepare for a practical Excel test?
Practise lookups, SUMIFS, absolute references, IF with conditional formatting, text clean-up and PivotTables on a version close to the employer’s. Keep the original data untouched and check one result by hand.
Should I say I have advanced Excel skills?
Only if you can demonstrate it. Naming specific tasks, such as PivotTable reports or Power Query automation, is more convincing than a label.
Sources
- XLOOKUP function (Microsoft Support)
- Switch between relative, absolute, and mixed references (Microsoft Support)
- SUMIFS function (Microsoft Support)
- Create a PivotTable to analyze worksheet data (Microsoft Support)
- Look up values with VLOOKUP, INDEX, or MATCH (Microsoft Support)
- About Power Query in Excel (Microsoft Support)