Excel Interview Questions for Data Analysts: 23 Questions With Answers

Last updated: 13 October 2026 · By the Asuraa Team

Quick answer: Excel interviews for data analyst roles test lookups (VLOOKUP, XLOOKUP, INDEX-MATCH), conditional formulas such as SUMIFS, PivotTables, data cleaning and Excel's limits. Microsoft's documentation notes that VLOOKUP defaults to approximate match when the last argument is omitted, that XLOOKUP is not available in Excel 2016 or 2019, and that a worksheet holds 1,048,576 rows.

Key takeaways

• Microsoft's VLOOKUP documentation states that omitting the range_lookup argument defaults to TRUE, an approximate match, so analysts should pass FALSE for exact matches.
• Microsoft states that XLOOKUP is not available in Excel 2016 or Excel 2019, so INDEX with MATCH remains the portable lookup for older versions.
• XLOOKUP defaults to an exact match and accepts an optional if_not_found value, wildcard match mode and reverse search mode.
• When a PivotTable's source is an Excel Table, refreshing it picks up new and updated rows automatically, according to Microsoft's PivotTable overview.
• An Excel worksheet holds up to 1,048,576 rows and 16,384 columns, and functions can be nested up to 64 levels deep.

Excel is still the first tool tested in most fresher data analyst interviews in India, often through a live task on a sample sheet. Interviewers check lookups, conditional aggregation, PivotTables, data cleaning and whether you know when Excel is the wrong tool. Below are 23 questions with model answers and formulas, a weighting table and a 7-day plan. For other subjects, see the interview questions by subject guide.

Which Excel topics carry the most weight in data analyst interviews?

Lookups, conditional formulas and PivotTables cover most of the marks; cleaning and charts come up in the practical task.

What formula basics are asked first?

Openers check that your formulas will not break when you copy them down 10,000 rows.

1. What is the difference between relative, absolute and mixed references?

A relative reference such as A2 shifts when you copy the formula, so it becomes A3 one row down. An absolute reference such as $A$2 stays fixed, which is what you want for a tax rate or a lookup range. A mixed reference such as $A2 or A$2 locks only the column or only the row, useful for multiplication tables or growth grids. Press F4 to cycle through them.

2. How do IF and IFERROR work, and when would you use IFS?

IF returns one value when a condition is true and another when it is false, such as =IF(C2>=50000,"High","Low"). IFERROR replaces an error with a value you choose, such as =IFERROR(B2/C2,0) to avoid #DIV/0!. When you have several bands, IFS is easier to read than nested IFs. Avoid using IFERROR to hide errors you have not understood.

=IFS(C2>=100000,"Gold",C2>=50000,"Silver",TRUE,"Bronze")

3. What is the difference between COUNT, COUNTA and COUNTBLANK?

COUNT counts cells containing numbers only. COUNTA counts all non-empty cells, including text and errors. COUNTBLANK counts empty cells. If COUNT and COUNTA differ on a column that should be numeric, some numbers are probably stored as text, which is a common data quality check in interviews.

What lookup questions should you expect?

Lookup questions appear in almost every Excel round, often with a trap.

4. How does VLOOKUP work, and what are its limitations?

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches the first column of a range and returns a value from the column number you give. Microsoft's VLOOKUP help page notes that the lookup value must be in the first column and that omitting the last argument defaults to TRUE, an approximate match. That is why freshers get wrong answers: always pass FALSE for an exact match. It also cannot look to the left, and inserting a column breaks the hard-coded index.

=VLOOKUP(A2,Products!$A$2:$D$500,3,FALSE)

5. What is XLOOKUP and why is it better?

XLOOKUP takes a lookup array and a separate return array, so it can look left or right, and it defaults to an exact match. Per Microsoft's XLOOKUP page, it has an optional if_not_found value, match modes including wildcards, and search modes including last-to-first. Microsoft states it is not available in Excel 2016 or Excel 2019, so check the version a company uses before relying on it.

=XLOOKUP(A2,Products!A:A,Products!C:C,"Not found")

6. How does INDEX-MATCH work, and why do analysts still use it?

MATCH finds the position of a value in a range, and INDEX returns the value at that position from another range. Together they look in any direction and do not break when columns are inserted, and they work in every Excel version, including the older ones many Indian offices still use.

=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))

What conditional aggregation questions are common?

Expect a question like "total sales for the North region in March" on a sample sheet.

7. How do SUMIFS and COUNTIFS work?

SUMIFS adds values that meet one or more conditions, and COUNTIFS counts rows that meet them. The sum range comes first in SUMIFS, followed by pairs of criteria range and criteria. Criteria can use operators and cell references, such as ">="&F1.

=SUMIFS(Sales[Amount], Sales[Region], "North", Sales[Date], ">="&DATE(2026,3,1), Sales[Date], "<="&DATE(2026,3,31))
=COUNTIFS(Sales[Region], "North", Sales[Amount], ">50000")

8. How would you find the average order value for each city?

Use AVERAGEIFS with the amount column as the average range and the city column as the criteria, or build a PivotTable with City in Rows and Amount in Values set to Average. In an interview, mention that the PivotTable is faster for a one-off summary while formulas are better when the result feeds another calculation.

How do you clean data in Excel?

Cleaning questions test whether you check data before analysing it.

9. How do you remove extra spaces and fix text?

TRIM removes leading, trailing and repeated spaces, and CLEAN removes non-printing characters that often come from exported systems. PROPER, UPPER and LOWER fix case. LEFT, RIGHT, MID and FIND extract parts of text, such as the domain from an email. Microsoft's VLOOKUP page also lists stray spaces and numbers stored as text as common causes of lookup failures.

=PROPER(TRIM(CLEAN(A2)))
=MID(B2, FIND("@",B2)+1, 100)

10. How do you find and remove duplicates?

For a quick fix, use Data > Remove Duplicates and choose the key columns, after making a copy of the raw data. To flag duplicates first, use =COUNTIFS(A:A,A2)>1 or conditional formatting's Duplicate Values rule. In Microsoft 365, =UNIQUE(A2:A1000) returns a distinct list without changing the source.

11. How do you split one column into several?

Use Data > Text to Columns with a delimiter such as a comma, or fixed width. Flash Fill (Ctrl+E) learns a pattern from one or two examples you type, such as extracting first names. For repeatable cleaning on files that arrive every month, use Power Query instead, because it records the steps and re-runs them on refresh.

12. How do you handle #N/A, #VALUE! and #DIV/0! errors?

First find the cause: #N/A usually means a lookup found no match, #VALUE! means a wrong data type such as text in a sum, and #DIV/0! means division by zero or blank. Fix the data where possible, for example converting text to numbers. Then use IFERROR or XLOOKUP's if_not_found argument only for expected cases, such as new products without a price yet.

13. What is data validation?

Data validation restricts what users can enter in a cell, such as a dropdown list of regions, whole numbers within a range or dates after today. It prevents bad data at entry rather than cleaning it later. Set it under Data > Data Validation, and add an input message and error alert so users know the rule.

What analysis and reporting questions are asked?

These check whether you can turn a sheet into an answer quickly.

14. What is a PivotTable and how do you keep it up to date?

A PivotTable summarises large data by dragging fields into Rows, Columns, Values and Filters, so you can total, average, count and group without formulas. Microsoft's PivotTable overview notes that when the source is an Excel Table, refreshing picks up new and updated rows automatically. So convert the data to a Table first, then right-click and Refresh after adding data.

15. Why convert a range into an Excel Table?

A Table (Ctrl+T) expands automatically when you add rows, so formulas, charts and PivotTables that use it stay current. It gives structured references such as Sales[Amount], which are easier to read than C2:C5000, plus banded rows, filters and a total row. It is the simplest habit that makes analyst workbooks reliable.

16. How do you use conditional formatting?

Conditional formatting changes a cell's look based on its value, such as highlighting sales below target in red, adding data bars or colour scales, or flagging duplicates. For custom rules, use a formula such as =$E2<$F2 to colour a whole row when actual is below target. Keep it purposeful; too many colours hide the message.

17. What chart would you use for different questions?

Use a line chart for trends over time, a clustered column or bar chart to compare categories, a stacked column only when the parts and total both matter, and a scatter plot to show the relationship between two numbers. Avoid pie charts with more than four or five slices. Always title the chart with the insight, such as "North sales fell 12% in Q2".

18. What is Goal Seek?

Goal Seek, under Data > What-If Analysis, changes one input cell until a formula reaches the target you set. For example, it can find the monthly sales needed to reach an annual target of Rs 50 lakh. For several inputs with constraints, Solver is the right tool.

What modern Excel and scenario questions come up?

These questions check whether you know current Excel and its limits.

19. What are dynamic array functions?

In Microsoft 365, functions such as FILTER, SORT, UNIQUE and SEQUENCE return a range of results that "spills" into neighbouring cells. If something blocks the spill range, you get a #SPILL! error. They replace many helper columns and array formulas.

=SORT(FILTER(Sales[[Customer]:[Amount]], Sales[Region]="North"), 2, -1)

20. What is Power Query in Excel?

Power Query, under Data > Get & Transform, connects to files, folders, databases and web sources, applies cleaning steps such as type changes, splits, merges and unpivots, and loads the result to a sheet or the data model. Each step is recorded, so next month you just click Refresh. It is the same engine used in Power BI.

21. What are Excel's row and column limits, and when should you move to SQL or Power BI?

Microsoft's Excel specifications page lists 1,048,576 rows and 16,384 columns per worksheet. Long before that limit, large files become slow and fragile. Move to SQL when you need to join big tables or query a database directly, and to Power BI when many people need a refreshed, interactive dashboard.

22. How would you find the top five customers by revenue in each region?

Build a PivotTable with Region and Customer in Rows and Sum of Amount in Values, then use Value Filters > Top 10 set to 5 on Customer and sort descending. In Microsoft 365 you could use SORT and FILTER per region and TAKE the first five rows. Explain both and say which you would hand to a manager.

23. How do you check that your analysis is correct?

Reconcile totals: the PivotTable grand total should match SUM of the source column. Spot-check a few rows by hand, check for blanks, duplicates and text-numbers, and confirm date ranges are complete. Interviewers like this answer because it shows you treat accuracy as part of the job.

What do most guides on Excel interview questions get wrong?

Most guides present VLOOKUP as the main lookup and skip its default behaviour. Microsoft's documentation is clear that leaving out the last argument means approximate match, which silently returns wrong values on unsorted data; this is the most common Excel mistake freshers make in live tasks. Guides also push XLOOKUP everywhere without noting Microsoft's statement that it is not available in Excel 2016 or 2019, which many offices still use, so know INDEX-MATCH as a fallback. Finally, guides rarely mention reconciliation; analysts are judged on whether totals tie out, not on formula tricks.

How should you revise Excel in 7 days?

Practise on one realistic sales dataset with at least a few thousand rows.

1. Day 1: References, IF, IFS, IFERROR, COUNT family. Convert data to a Table.
2. Day 2: VLOOKUP with FALSE, XLOOKUP, INDEX-MATCH. Join a products sheet to sales.
3. Day 3: SUMIFS, COUNTIFS, AVERAGEIFS with date and text criteria.
4. Day 4: Cleaning: TRIM, CLEAN, Text to Columns, Flash Fill, duplicates, error handling.
5. Day 5: PivotTables with grouping, top-N filters and slicers; conditional formatting.
6. Day 6: Dynamic arrays, Power Query on a folder of monthly files, two clear charts.
7. Day 7: Timed mock task: answer five business questions in 30 minutes, then all 23 questions aloud.

For a broader skills view, read Excel skills for jobs, and build portfolio work from Excel projects for a data analyst portfolio. The data analyst interview questions cover the rest of the round, and how to prepare for technical interview questions explains how these rounds are run.

Related guides

• Interview Questions by Subject: Java, Python, SQL, DSA, Cloud, AI
• Generative AI and LLM Interview Questions (2026): 24 With Answers
• Power BI Interview Questions (2026): 22 Questions With Answers
• DSA Interview Questions for Freshers: 25 by Topic With Approaches

FAQ

What Excel skills are required for a data analyst fresher?

Expect to be tested on absolute references, IF and IFERROR, lookups (VLOOKUP with exact match, XLOOKUP, INDEX-MATCH), SUMIFS and COUNTIFS, PivotTables, Excel Tables, basic cleaning with TRIM and Text to Columns, conditional formatting and clear charts. Knowing Power Query and dynamic array functions such as FILTER and UNIQUE is a strong plus for analyst roles.

Should I learn VLOOKUP or XLOOKUP for interviews?

Learn both, plus INDEX-MATCH. XLOOKUP is simpler and defaults to an exact match, but Microsoft states it is not available in Excel 2016 or 2019, which some offices still use. VLOOKUP appears in nearly every interview, so know its approximate-match default and always pass FALSE. INDEX-MATCH works in every version and can look left.

What is asked in an Excel practical test for data analysts?

A typical test gives you a sales or HR sheet and 30 to 60 minutes to answer business questions: total sales by region and month, top customers, lookups from a second sheet, cleaning names or dates, a PivotTable summary and one or two charts. Interviewers watch for correct references, exact-match lookups and totals that reconcile with the source.

Is Excel still important if I know SQL and Power BI?

Yes. Many Indian analyst teams still receive data and share results in Excel, managers often ask for a quick PivotTable, and interview rounds frequently start with an Excel task. SQL handles large joins and Power BI handles shared dashboards, but Excel remains the fastest tool for ad hoc analysis on moderate data.

How many rows can Excel handle?

Microsoft's specifications list 1,048,576 rows and 16,384 columns per worksheet. In practice, workbooks become slow well before that, especially with many lookup formulas. For larger data, load it into the Excel data model through Power Query, or move the analysis to SQL or Power BI.

How do I explain a PivotTable in an interview?

Say it is a tool that summarises large data without formulas by dragging fields into Rows, Columns, Values and Filters, then give an example: region in rows, month in columns, sum of sales in values. Add that you convert the source to an Excel Table so a refresh picks up new rows, and that you reconcile the grand total with the source.

Final thoughts

Excel interviewers reward analysts whose numbers are right and whose formulas survive being copied, not those who know the most obscure functions. Practise on one real dataset, time yourself on the mock task, and then browse data analyst openings on Asuraa jobs.