20 Excel Interview Questions for Data Analyst (2026 Practice Set)

Say you have an Excel round tomorrow. The interviewer shares a sheet with five hundred sales rows, some duplicate customer names and one blank column. The first task sounds simple: find each customer's city from a second table. The formula is rarely the hard part. The hard part is explaining each step out loud while someone watches, and spotting messy data such as a trailing space.

Data analyst Excel interviews mostly test five skills. Lookups and Pivot Tables carry the most weight, but this post builds from the basics up, so you start with cell references and counting, then move through logic, lookups, Pivot Tables and cleanup. Practise the 20 problems below by typing each formula yourself, and check the topic list against the GeeksforGeeks Excel interview questions for data analysis. Interviewers watch how you build an answer on a live sheet, so a memorised definition is not enough.

Cell references and counting formulas

Cell references decide whether a formula copies correctly, and the four counting functions each count something different. Both topics sit near the top of the GeeksforGeeks list of Excel interview questions for data analysis, so they are worth practising first, before the harder lookup and Pivot Table tasks.

Problem 1. Copy a total down a column. You have Quantity in column A and Price in column B. Type =A2*B2 in C2 and drag it down. Excel changes it to =A3*B3 in the next row. That is a relative reference: it moves with the formula.

Problem 2. Apply one tax rate to every row of the sheet. The rate sits in cell F1. If you write =B2*F1 and drag down, the next row looks at F2, which is empty. Write =B2*$F$1 instead. The dollar signs lock both the column and the row, so every row uses the same cell.

Problem 3. Build a grid with one formula. Put numbers down column A and across row 1, then type =$A2*B$1 in B2 and fill the grid. $A2 locks the column only. B$1 locks the row only. These are mixed references, and interviewers ask you to explain which part each dollar sign locks.

Problem 4. Count the right thing in a column. Take one sample column, A2:A7, that holds 250 and 400, then Pune and Delhi, then two empty cells. The four counting functions give four different answers:

Problem 5. Spoken question: explain a function versus a formula. A formula is anything you type that starts with =, such as =A1+A2. A function is a named tool Excel already has, such as SUM. So =SUM(A1:A2) is a formula that uses a function.

Logic formulas with AND and IF, plus the NOW stamp

Logic questions check whether you can turn a plain-English rule into a formula that returns TRUE or FALSE. The GeeksforGeeks Excel list uses =AND(A1>1, B1<5) as its example, which is a good place to start.

Problem 6. Test two conditions at once with AND. AND returns TRUE only when every condition inside it is true. If A1 holds 3 and B1 holds 2, both tests pass. If B1 holds 7, the whole result turns FALSE.

Problem 7. Label each student Pass or Fail. The rule: a student passes only with at least 40 in both Maths and English. Wrap AND inside IF so the output is a word, not TRUE or FALSE. Try it first with Maths in B2 and English in C2, then check: =IF(AND(B2>=40, C2>=40), "Pass", "Fail") gives Pass for 55 and 62, and Fail for 55 and 31.

Problem 8. Stamp the current date and time in a cell. =NOW() takes no arguments. The follow-up question is usually "what happens when you reopen the file?" The answer: it updates every time the sheet recalculates. For a stamp that stays fixed, press Ctrl+; for the date.

=AND(A1>1, B1<5)        → TRUE  (A1 = 3, B1 = 2)
=AND(A1>1, B1<5)        → FALSE (A1 = 3, B1 = 7)
=IF(AND(B2>=40, C2>=40), "Pass", "Fail")
                        → "Pass" (B2 = 55, C2 = 62)
                        → "Fail" (B2 = 55, C2 = 31)
=NOW()                  → today's date and current time
=TODAY()                → today's date only

Say the rule out loud before you type. "Both marks at least 40" tells you it is AND. "Either mark" would tell you it is OR.

VLOOKUP and HLOOKUP

VLOOKUP finds a value in the first column of a table and returns something from a column to its right, so it breaks when the answer sits on the left. The GeeksforGeeks guide describes VLOOKUP and HLOOKUP as the older lookup tools, but you will still see them in almost every company file.

Problem 9. Find each customer's city. This is the task from the opening. The Customers sheet has Customer ID in column A, Name in B and City in C. Your sales sheet has the Customer ID in A2.

=VLOOKUP(A2, Customers!$A:$C, 3, FALSE)
// lookup value, table, column number to return, exact match
// Broken case: City is in column A, Customer ID is in column C
=VLOOKUP(A2, Customers!$A:$C, 1, FALSE)   → #N/A
// VLOOKUP only searches the first column, so it never sees the ID

Explain why FALSE matters (Problem 10). FALSE asks for an exact match. TRUE asks for an approximate match and expects the first column sorted in order. With unsorted customer IDs, TRUE can return the wrong city without any error. That silent mistake is worse than #N/A, and interviewers like to hear you say so.

Problem 11. Fix a #N/A error, then use HLOOKUP. The usual causes of #N/A:

• The value does not exist in the lookup column.
• Extra spaces, such as "Ravi " with a space at the end. Clean them with TRIM.
• An ID stored as text in one sheet and as a number in the other.
• The lookup column is not the first column of the range.

HLOOKUP is the same idea turned sideways. Use it when months run across row 1 and figures sit below: =HLOOKUP("Mar", A1:M4, 3, FALSE) returns the third row under March.

INDEX-MATCH and XLOOKUP

INDEX-MATCH looks in any direction, and XLOOKUP does the same job in one function, which is why interviewers now ask you to pick between them. GeeksforGeeks calls INDEX+MATCH the flexible option and XLOOKUP the modern, recommended one.

Do a left lookup (Problem 12). The Customers sheet now has City in column A and Name in column B. You have a name in F2 and need the city. VLOOKUP cannot do this. MATCH finds the row number of the name, and INDEX returns the city from that row.

=INDEX(Customers!A:A, MATCH(F2, Customers!B:B, 0))
// MATCH(..., 0) means exact match
=XLOOKUP(F2, Customers!B:B, Customers!A:A, "Not found")
// lookup value, where to search, what to return, text if missing

Problem 13. Rewrite it with XLOOKUP. The second formula above does the same task. It reads in plain order: find this, search here, return that. The fourth argument replaces the #N/A error with your own text, so you do not need to wrap it in IFERROR.

Choose one and defend it (Problem 14). Here is XLOOKUP measured against INDEX-MATCH:

In the interview, a good answer sounds like this: "I would use XLOOKUP if everyone has a newer version such as Microsoft 365. If the file goes to people on older Excel, I would use INDEX-MATCH." That shows you think about who opens the file next.

Pivot Tables from raw sales rows

A Pivot Table turns a long list of rows into a summary by dragging fields into Rows, Columns and Values. The GeeksforGeeks walkthrough gives the click path as Insert, then PivotTable, then drag the fields.

The three problems below use one shared sheet, the kind a fresher gets in a service-company or startup interview. It has five hundred rows and four columns: Region (North, South, East, West), Product (Laptop, Mouse, Keyboard, Monitor), Order Date and Amount. Build it once and reuse it.

1. Select the data

   Click any cell inside the table. Excel picks up the whole range if there are no empty rows in the middle.

2. Insert the Pivot Table

   Go to Insert, then PivotTable, and choose New Worksheet.

3. Drag Region into Rows

   Each region becomes one row of the summary.

4. Drag Amount into Values

   Check it says "Sum of Amount", not "Count of Amount". If it says Count, some cells in that column hold text.

5. Add Product to Columns

   Now each region shows a split by product.

Problem 15. "Show me total sales by region." This is the exact prompt in many rounds. Steps one to four answer it. Sort the result from largest to smallest before you hand it back, without being asked.

"Which product sold most in each region?" (Problem 16) Add step five. Then read the grid out loud: "In the South, Monitor has the highest total." The interviewer wants the answer, not just the table.

Problem 17. "Now group it by month and show only the East." This is the usual follow-up. Drag Order Date into Rows, right-click any date, choose Group, and pick Months. Then drag Region into the Filters area and select East. If Group is greyed out, some dates are stored as text, and you should say that.

Data cleanup: duplicates, Text to Columns and UNIQUE

Cleanup problems test data wrangling, meaning you turn messy raw data into a table you can analyse. The GeeksforGeeks data analyst interview guide defines wrangling as cleaning, transforming and organising raw data, and lists Excel among the main analyst tools. Every lookup and Pivot Table above gives wrong answers on dirty data, which is why this round exists.

Extract the unique customer names (Problem 18). The GeeksforGeeks Excel list names three ways. Know when each one fits:

• Remove Duplicates (Data tab): deletes repeated rows from the original data. Fast, but you lose the duplicates, so work on a copy.
• UNIQUE: =UNIQUE(B2:B501) returns a fresh list in new cells and updates when the data changes. The original stays untouched.
• Advanced Filter (Data, then Advanced, then "Unique records only"): copies unique rows to another place. Use it on older versions without UNIQUE.

Problem 19. Split a full name into first and last name. Select the name column, go to Data, then Text to Columns. Choose Delimited, tick Space and click Finish. On Microsoft 365 you can instead type =TEXTBEFORE(A2, " ") to get the first name, which returns Ravi from Ravi Kumar. Insert an empty column to the right first, or Excel will overwrite your next column. Names with three parts, like "Mohammed Arif Khan", split into three cells, so mention how you would handle them.

Show East-region orders, largest first (Problem 20). FILTER and SORT are dynamic array functions: one formula spills its results into as many cells as it needs.

=FILTER(A2:D501, A2:A501 <> "")        // drop rows where column A is blank (needs Excel 2021 or Microsoft 365)
=FILTER(A2:D501, A2:A501 = "East")           // only East orders
=SORT(FILTER(A2:D501, A2:A501 = "East"), 4, -1)
// 4 = sort by the fourth column (Amount), -1 = largest first

Business analyst rounds use these same cleanup tasks. The skills list for business analyst freshers shows where Excel sits next to SQL in that role.

How to practise these Excel interview questions

A PDF list helps you revise, but typing each formula into a blank sheet is what builds speed. Instead of hunting for a PDF download, copy these 20 problems into your own notes and add your answer under each one. Your own file is the most useful PDF you will find.

For basics that interviewers ask in the first five minutes, use two longer question banks. FITA Academy lists 101 Excel interview questions, covering file formats such as .xlsx, .xlsm and .xlsb. It notes that .xlsb gives the smallest file size, that F9 helps debug part of a formula, and that F12 opens Save As. The GeeksforGeeks Excel list adds a classic trivia question: .xls supports about 4,000 cell formats, while .xlsx supports up to 64,000.

If your target role leans toward data science, the Excel round is often followed by a modelling round. Read the machine learning interview questions for freshers and the data scientist fresher skills list to see what comes next.

Build the sales sheet today: type =RANDBETWEEN(1,1000) down 500 rows for Amount and pick the regions from a short list, which takes a few minutes. Then solve problems 9, 12 and 15 (VLOOKUP, INDEX-MATCH, Pivot Table) from memory, and time yourself. To check your work, the Pivot Table grand total should match =SUM of the Amount column.