Excel Interview Questions for Data Analysts

All the examples use one fictional dataset, a month of sales from a college canteen in Pune. The sales sheet has these columns, with data in rows 2 to 201:

A small price list sits in K2:L40 (item in K, price in L). Build this in a blank workbook and type every formula yourself instead of copying and pasting.

Lookup Excel interview questions

1. What does VLOOKUP do, and what does FALSE mean at the end?

VLOOKUP searches the first column of a range for a value and returns something from the same row, a set number of columns to the right.

=VLOOKUP(C2, $K$2:$L$40, 2, FALSE)

This finds the item from C2 in the price list and returns its price from column 2 of that range. FALSE means exact match. Leave it out and Excel assumes an approximate match, which returns wrong prices without showing any error. Plenty of bad VLOOKUPs come down to that one missing word.

2. What are the limits of VLOOKUP?

It only looks right, so it can't return anything from a column to the left of the lookup column. You type the column number by hand, which means inserting a column inside the range breaks it quietly. It also stops at the first match. Interviewers ask this to see whether you know why INDEX MATCH and XLOOKUP exist.

3. How does XLOOKUP work, and which Excel versions have it?

=XLOOKUP(C2, $K$2:$K$40, $L$2:$L$40, "Not found")

You hand it the value, the column to search and the column to return, so looking left is as easy as looking right. Exact match is the default, and the fourth argument replaces an IFERROR wrapper. XLOOKUP, FILTER and UNIQUE need Excel 2021 or later, or Microsoft 365, and Excel for the web has XLOOKUP too. Google Sheets also supports it. Bring up the version point in the interview. It tells them you've worked on more than one machine.

4. Write the INDEX MATCH version of the same lookup

=INDEX($L$2:$L$40, MATCH(C2, $K$2:$K$40, 0))

MATCH finds the row position of the item, with 0 meaning exact match, and INDEX returns the value at that position in the price column. Every version of Excel has both functions, which is why so many company files still use them.

5. How do you handle #N/A from a lookup?

=IFNA(VLOOKUP(C2, $K$2:$L$40, 2, FALSE), "Not in price list")

IFNA catches only #N/A. IFERROR catches every error, including the #REF! from a broken range, so a real problem gets hidden. Prefer IFNA for lookups and say why.

6. When would you use an approximate match?

Use it to place a number into bands. Say a discount table in P2:Q5 lists bill amounts from which a discount starts: 0, 100, 250 and 500, with 0%, 5%, 8% and 10% beside them.

=VLOOKUP(F2, $P$2:$Q$5, 2, TRUE)

A ₹300 bill gets 8%, because 250 is the largest value not above 300. Sort the first column in ascending order first, or the results come out wrong.

7. How do you look up using two conditions?

Suppose a marks table has student in R, subject in S and marks in T, and you want one student's marks in one subject. In Excel 2021 or later, or Microsoft 365:

=XLOOKUP(1, ($R$2:$R$500=W2)*($S$2:$S$500=X2), $T$2:$T$500, "Not found")

Each comparison gives TRUE or FALSE for every row, multiplying turns them into 1s and 0s, and XLOOKUP finds the first row where both are 1. In older versions, add a helper column with =R2&"|"&S2 and look up W2&"|"&X2 against it.

Conditional formula questions

8. What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition. SUMIFS takes several, and the range to add comes first. Total snack sales from 1 September 2026:

=SUMIFS(F2:F201, D2:D201, "Snacks", A2:A201, ">="&DATE(2026,9,1))

Watch the ">="&DATE(...) pattern. Writing ">=1/9/2026" as text depends on the computer's date settings and can fail.

9. Count the bills above ₹100

=COUNTIF(F2:F201, ">100")

COUNTIFS works the same way with more conditions, for example =COUNTIFS(D2:D201, "Beverages", F2:F201, ">100").

10. Find the average amount for snack bills only

=AVERAGEIFS(F2:F201, D2:D201, "Snacks")

When no rows match you get a #DIV/0! error. Catch it with IFERROR.

11. Nested IF or IFS: which would you use?

=IF(F2>=500, "Large", IF(F2>=100, "Medium", "Small"))
=IFS(F2>=500, "Large", F2>=100, "Medium", TRUE, "Small")

The result is identical. IFS reads more easily, and the final TRUE is the "everything else" case. IFS works in Excel 2019 and later and in Microsoft 365; the one-time-purchase Excel 2016 doesn't have it, so you still need to know the nested IF.

12. What is the difference between relative and absolute references?

A2 shifts when you copy the formula down. $A$2 stays fixed. $A2 fixes only the column, A$2 only the row. Press F4 while editing a reference to cycle through them. The lookup examples above use $K$2:$L$40 so the price list doesn't slide down as you fill.

13. Flag bulk snack orders using AND

=IF(AND(D2="Snacks", E2>=5), "Bulk", "")

Swap AND for OR to flag a row when either condition is true. Interviewers like this one because it puts two ideas in a single line.

Cleaning data in Excel

14. How do you remove extra spaces from names?

=TRIM(C2) removes leading and trailing spaces and squeezes repeated spaces between words into one. Data from other systems can carry non-printing characters, which =TRIM(CLEAN(C2)) also strips. Text copied from web pages often has non-breaking spaces that neither function removes, so swap them first with =TRIM(SUBSTITUTE(C2, CHAR(160), " ")). Until you clean it, "Vada pav " with a trailing space fails every lookup.

15. How do you remove duplicates?

Go to Data, then Remove Duplicates, and pick the columns that define a duplicate. The rows are deleted for good, so work on a copy. In Excel 2021 or later, or Microsoft 365, =UNIQUE(C2:C201) lists each item once without touching the source.

16. Split "Priya Sharma" into first and last name

=LEFT(G2, FIND(" ", G2)-1)
=MID(G2, FIND(" ", G2)+1, 100)

FIND gives the position of the space. LEFT takes everything before it, and MID takes everything after. Text to Columns (on the Data tab) and Flash Fill (Ctrl+E) do it without formulas, but expect the interviewer to ask for the formula.

17. Some numbers are stored as text. How do you fix them?

They sit on the left of the cell, often with a small green triangle, and SUM ignores them. Use the warning's "Convert to Number" option, or =VALUE(E2), or copy a cell holding 1 and use Paste Special, Multiply over the column. Add that a SUM coming out smaller than expected is how you tend to spot the problem in the first place.

18. A date column arrived as text like "15/09/2026". How do you convert it?

=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

This builds a real date from the year, month and day pieces, whatever the computer's regional setting. Then =TEXT(A2, "mmm yyyy") shows "Sep 2026" for monthly labels. Indian files put the day first while many tools default to month first, and mixing the two is how 3 September turns into 9 March.

19. How do you highlight duplicate bill numbers?

Select B2:B201, go to Conditional Formatting, choose New Rule, use a formula, and enter:

=COUNTIF($B$2:$B$201, B2)>1

Pick a fill colour, and every repeated bill number lights up. The built-in "Duplicate Values" rule does the same, but writing the formula proves you understand it.

Pivot table interview questions

20. What is a pivot table, and how would you show category sales by date?

It's a summary of a large table, built without formulas. Click inside the data, choose Insert, then PivotTable. Drag Category to Rows, Date to Columns and Amount to Values. Excel sums Amount by default; if it shows Count, the column probably has text or blank cells in it.

21. How do you show monthly totals and percentages?

Right-click any date in the pivot and choose Group, then Months (and Years if the data spans more than one). For percentages, right-click a value, pick Show Values As, then % of Grand Total or % of Column Total.

22. You added 50 new rows, and the pivot didn't change. Why?

A pivot table works from a snapshot. Press Alt+F5 to refresh it, or Ctrl+Alt+F5 to refresh everything. If the new rows fall outside the source range, refreshing won't help. Convert the data to an Excel Table first (Ctrl+T), and the pivot's source grows on its own as rows are added.

23. What are slicers?

Clickable filter buttons attached to a pivot table or Table. Insert one for Category, and a manager can click "Beverages" without touching the pivot's filter menus. Mention them whenever an interviewer asks how you'd make a report easy for someone else to use.

Chart questions

24. Which chart would you use for which job?

A line chart for a trend over time, like daily sales. A column or bar chart for comparing categories. A pie chart only when there are a few parts of one whole, and never with ten slices nobody can tell apart. If they push, say what the reader should notice in under five seconds, then pick the chart that makes that easiest.

25. How do you show sales and number of bills on one chart?

With a combo chart. Insert a chart, choose Combo, set Amount as clustered columns and Bill count as a line, and tick Secondary Axis for the line. Without the second axis, a count of 40 bills flattens to the bottom next to ₹12,000 of sales.

Getting ready for the Excel round

Build the canteen sheet, fill 200 rows with made-up data, and answer every question above from memory before checking. Then break things on purpose: delete the FALSE in question 1, add a trailing space in an item name, insert a column inside a VLOOKUP range. Fixing broken files is most of the job.

Analyst roles tend to pair the Excel round with a SQL round, so work through our SQL interview questions for freshers. And once you can do this work, say so properly on paper. Our guide to the data analyst resume for freshers covers how to describe Excel projects with numbers a recruiter will believe.

Speed is the last piece. A timed test on Interview Prep shows how you hold up against a clock, and the free challenges on Compete check your speed under real time pressure.

When the formulas feel easy, take a timed test on Interview Prep or the next free challenge on Compete.