Data Analyst Excel Interview Questions and Answers
· 8 min read
Most Excel interview questions for data analyst roles test the same five skills: lookups (VLOOKUP, XLOOKUP, INDEX MATCH), conditional totals (SUMIFS, COUNTIFS), IF logic, pivot tables, and checking your own numbers. If you can answer a question like this one in under a minute, and explain why it is correct, you are in good shape:
=SUMIFS(E2:E7, B2:B7, "West")Below are the questions that come up most, with short answers, then a small sample test you can try yourself.
Sample data for the questions
Several answers refer to this table of six orders. Column F and the answers in I hold the formulas from questions 5, 6, 8 and 14:
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Region | Product | Units | Revenue | Q8: West and over 100 | Question | Answer | |
| 2 | 2025-01-06 | North | Paper | 10 | 68.00 | No | Q5: West revenue | 251.20 | |
| 3 | 2025-01-09 | West | Mugs | 4 | 50.00 | No | Q6: February revenue | 153.00 | |
| 4 | 2025-01-15 | West | Paper | 25 | 170.00 | Yes | Q14: West share of total | 57.0% | |
| 5 | 2025-02-02 | East | Soap | 12 | 46.80 | No | |||
| 6 | 2025-02-11 | West | Soap | 8 | 31.20 | No | |||
| 7 | 2025-02-20 | North | Mugs | 6 | 75.00 | No |
VLOOKUP interview questions
1. What does the last argument of VLOOKUP do?
It sets the match type. FALSE (or 0) means exact match. TRUE, or leaving it out, means approximate match, which only works on a column sorted from smallest to largest and returns the nearest smaller value when there is no exact hit. For IDs, codes and names you almost always want FALSE. Interviewers ask this because leaving it out is the most common VLOOKUP bug.
2. What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP defaults to exact match, can return a column to the left of the search column, does not use a typed column number, and has a built-in "if not found" argument. It needs Excel 2021, Microsoft 365 or Excel for the web. VLOOKUP works in every version but can only look to the right. Our VLOOKUP vs XLOOKUP vs INDEX MATCH guide compares all three.
3. How do you look up a value to the left?
Use XLOOKUP, or INDEX MATCH in older versions: =INDEX(return_column, MATCH(value, search_column, 0)). MATCH finds the row position and INDEX returns the value at that position.
4. What does #N/A mean and how do you handle it?
The lookup value was not found. First check why: a trailing space, a number stored as text, or a value that really is missing. Then, if "not found" is a valid outcome, handle it with IFNA or XLOOKUP's fourth argument. Using IFERROR for this hides other errors too, which interviewers like to hear you mention.
Formula questions
5. What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition, with the sum range last. SUMIFS takes one or more, with the sum range first. On the sample table, West revenue is =SUMIFS(E2:E7, B2:B7, "West"), which returns 251.20.
6. How do you add up revenue between two dates?
Use the date column twice in SUMIFS. February revenue:
=SUMIFS(E2:E7, A2:A7, ">="&DATE(2025,2,1), A2:A7, "<"&DATE(2025,3,1))Result: 153.00 (46.80 + 31.20 + 75.00). Using "before the 1st of next month" avoids month-length mistakes. See SUMIFS with multiple criteria for more patterns.
7. What is the difference between a relative and an absolute reference?
B2 moves when the formula is copied. $B$2 stays fixed. B$2 and $B2 lock only the row or only the column. Press F4 while editing to cycle through them. A good follow-up answer: you lock a total cell when calculating each row's share of the total.
8. How do you write an IF with two conditions?
Wrap them in AND (both must be true) or OR (one is enough): =IF(AND(B2="West", E2>100), "Yes", "No"). On the sample table only row 4 returns Yes. Mention that you test it on a row that meets just one condition, to make sure AND and OR are not swapped.
9. How do you calculate percent change?
=(new-old)/old, formatted as a percentage. Interviewers often follow up with "what if the old value is zero?" The honest answer is that percent change is undefined, so you show "n/a" or the absolute change instead.
10. How do you count unique values?
In Microsoft 365 or Excel 2021: =COUNTA(UNIQUE(B2:B7)), which returns 3 regions on the sample table. In older versions, use Remove Duplicates on a copy, or a pivot table with Distinct Count (tick "Add this data to the Data Model" when creating it).
Pivot table interview questions
11. When would you use a pivot table instead of formulas?
To summarise quickly by one or two fields, explore the data, or build a report that someone will refresh. Formulas are better when the result must sit in a fixed layout, feed other calculations, or follow a rule a pivot cannot express.
12. Why does a pivot table show "Count of Revenue" instead of "Sum"?
The column contains text or blank cells, so Excel defaults to Count. Fix the data (numbers stored as text are a common cause), or change the value field setting to Sum.
13. Why did the pivot not pick up new rows?
Pivot tables do not update by themselves. Click Refresh. If the new rows are outside the source range, change the data source, or better, format the data as a table (Ctrl+T) so the range grows automatically.
14. How do you show each region as a percentage of the total?
Right-click a value, choose Show Values As, then % of Grand Total. On the sample table West would show 57.0% (251.20 of 441.00).
15. How do you group dates by month?
Put the date field in Rows, right-click a date and choose Group, then Months (and Years, if the data covers more than one year, so January 2025 and January 2026 are not merged).
Data cleaning questions
16. How do you find duplicates?
Conditional Formatting, then Highlight Cells Rules, then Duplicate Values, to see them. =COUNTIF($A$2:$A$100, A2)>1 in a helper column to filter them. Remove Duplicates on the Data tab to delete them, ideally on a copy. If two values look identical but are not flagged, one probably has a trailing space: clean it with =TRIM(A2).
The "find the mistake" question
Some interviews hand you a sheet where something is already wrong. It tests whether you check work before you trust it, including work from an AI assistant. A typical prompt: "An assistant built this summary. Is the total right?"
The AI wrote this in E8 for total revenue:
=SUM(E2:E6)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Region | Product | Units | Revenue |
| 2 | 2025-01-06 | North | Paper | 10 | 68.00 |
| 3 | 2025-01-09 | West | Mugs | 4 | 50.00 |
| 4 | 2025-01-15 | West | Paper | 25 | 170.00 |
| 5 | 2025-02-02 | East | Soap | 12 | 46.80 |
| 6 | 2025-02-11 | West | Soap | 8 | 31.20 |
| 7 | 2025-02-20 | North | Mugs | 6 | 75.00 |
| 8 | AI's formula | 366.00 | |||
| 9 | Fixed | 441.00 |
It returns 366.00. The correct total is 441.00: the range stops one row early and misses the 75.00 order in row 7. There is no error message, and 366 looks like a plausible number. How to spot it: click the cell, read the range in the formula bar, and check that it starts on the first data row and ends on the last one. Then sanity-check with the status bar, which shows the sum of whatever cells you select.
Other planted mistakes to look for: a VLOOKUP with no FALSE, a total that includes refunded orders, a growth rate divided by the new value instead of the old one, and AND written where the rule says OR.
What an Excel interview test looks like
Formats vary by company, but an Excel interview test usually gives you a dataset and a time limit, often under an hour. Tasks tend to look like:
- Add a calculated column (revenue = units × price).
- Total or count by a category with SUMIFS or COUNTIFS.
- Look up a value from a second table.
- Build a pivot table and a chart.
- Answer two or three business questions in plain words.
Try these on the sample table before you read the checks:
- Total units sold. Check: 65.
- West orders with revenue over 100. Check: 1.
- Unit price for each row. Check: Paper 6.80, Mugs 12.50, Soap 3.90 on every row.
You can practise tasks like these on one company's data on the practice page, or work through our Excel practice exercises.
Common mistakes in interviews
Not checking the answer. Interviewers watch whether you verify. Say "let me check this adds up" and compare with a quick SUM or the status bar.
Hard-coding numbers. =E3/441 breaks when the data changes. Point at the total cell and lock it.
Trusting the first formula an AI gives you. If you use an assistant to prepare, remember it often drops the exact-match flag, forgets a status filter or uses a range that ends early. Read every range and argument before you rely on it.
Silent rounding. Formatting to two decimals does not change the stored value. If the test asks for rounded values, use ROUND.
FAQ
What Excel skills do data analysts need for interviews?
Lookups, SUMIFS and COUNTIFS, IF with AND and OR, absolute references, pivot tables, basic charts and data cleaning (TRIM, removing duplicates, fixing text numbers).
Are VLOOKUP interview questions still asked?
Yes. Even where XLOOKUP is available, many companies still have VLOOKUP in older files, and the exact-match question is a quick way to test understanding.
How do I prepare for an Excel interview test?
Practise timed tasks on a realistic dataset, and check every answer with a second method. Practise reading other people's formulas too, since many tests ask you to find a mistake.
What pivot table interview questions are common?
Why a pivot shows Count instead of Sum, how to refresh it, how to group dates by month, and how to show values as a percentage of the total.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- The AI’s total looks lowAudit
- Prices pulled by the AI look oddAudit
- West revenue, completed only
- Which orders need a follow-up?