25 Excel Interview Questions (With Answers You Can Practise)

These 25 Excel interview questions cover what interviewers commonly ask: formulas, cell references, lookups, IF, conditional sums and pivot tables. Each answer is short, correct and something you can test yourself in a spreadsheet. Read the answer, then try it on real data until you can explain it without notes.

The examples use a simple roster: IDs in column A, names in B, departments in C, hire dates in D, salaries in E and ratings in F, with data in rows 2 to 13.

Basic Excel interview questions (1–6)#

Basic questions check that you understand how Excel calculates.

1. What's the difference between a formula and a function? A formula is anything that starts with =, like =B2*C2. A function is a named, ready-made calculation used inside a formula, like SUM in =SUM(B2:B6).
2. In what order does Excel calculate? Parentheses first, then powers (^), then * and /, then + and -, left to right. So =2+3*4 is 14 and =(2+3)*4 is 20.
3. What's the difference between COUNT, COUNTA and COUNTBLANK? COUNT counts cells with numbers. COUNTA counts cells that aren't empty, text included. COUNTBLANK counts empty cells.
4. Why doesn't formatting a cell to two decimals change the total? Formatting changes how a number looks, not the stored value. Formulas use the full value. To change the value itself, use =ROUND(B2,2).
5. How do you show text instead of an error? Wrap the formula in IFERROR: =IFERROR(VLOOKUP(H1,A2:E13,5,FALSE),"Not found"). Mention the limit: IFERROR hides every error, including real mistakes. IFNA only catches #N/A.
6. How can you see all the formulas on a sheet at once? Press Ctrl+ (the key left of 1) to toggle Show Formulas, or use **Formulas > Show Formulas**. On a Mac, press Control+.

Cell reference questions (7–10)#

Reference questions test whether your formulas survive being copied. The guide to Excel cell references goes deeper.

7. What's the difference between a relative and an absolute reference? A relative reference like A1 moves when you copy the formula. An absolute reference like $A$1 never moves.
8. What is a mixed reference? One part is locked: $A1 always reads column A, and A$1 always reads row 1. They let one formula fill a whole grid, such as =COUNTIFS($C$2:$C$13,$H2,$F$2:$F$13,I$1).
9. What's the fastest way to add $ signs? Put the cursor on the reference while editing and press F4. Each press cycles $A$1, A$1, $A1, A1. On a Mac, press ⌘T (or fn+F4).
10. You copy =B2*(1+F1) down and only the first row is right. Why? F1 moved to F2, F3 and so on, which are empty. Lock it: =B2*(1+$F$1). See how to copy a formula without changing its references.

Lookup questions (11–16)#

Lookups are among the most common Excel interview questions, and interviewers often ask about their limits.

11. How does VLOOKUP work? =VLOOKUP(lookup_value, table_array, col_index_num, FALSE) finds a value in the table's first column and returns the value from the column number you give, in the same row. FALSE means exact match. The VLOOKUP function guide has examples.
12. Why does VLOOKUP return #N/A? It found no exact match. Common causes: the value isn't there, extra spaces, a number stored as text, or a table range that slid down because it wasn't locked with $.
13. What are VLOOKUP's limits? It only looks to the right of the first column, the column number breaks if someone inserts a column, and without FALSE it does an approximate match.
14. How does INDEX MATCH work? =INDEX(A2:A13,MATCH(H1,B2:B13,0)). MATCH finds the position of H1 in the names, and INDEX returns the ID at that position. It can return a column to the left of the one you search.
15. What does XLOOKUP do better? =XLOOKUP(H1,A2:A13,E2:E13,"Not found") looks in any direction, defaults to an exact match and has a built-in "not found" value. It needs Excel 2021 or Microsoft 365.
16. How do you look up by row and column at the same time? Use INDEX with two MATCHes: =INDEX(B2:E9,MATCH(H1,A2:A9,0),MATCH(H2,B1:E1,0)). The first MATCH finds the row and the second finds the column.

IF and conditional formula questions (17–21)#

These questions check that you can turn a business rule into a formula.

17. Write a formula that labels a score of 70 or more as Pass. =IF(B2>=70,"Pass","Fail"). IF takes a test, a value if true and a value if false. See the IF function guide.
18. How do you test two conditions? Put AND or OR inside IF. "Rating 4 or more and hired before 2024": =IF(AND(F2>=4,D2<DATE(2024,1,1)),"Yes","No"). AND needs every test to be TRUE; OR needs at least one.
19. What's the difference between SUMIF and SUMIFS? SUMIF has one condition and puts the sum range last: =SUMIF(C2:C13,"Sales",E2:E13). SUMIFS allows many conditions and puts the sum range first: =SUMIFS(E2:E13,C2:C13,"Sales",F2:F13,">=4"). The SUMIFS guide shows more.
20. How do you count rows that meet two conditions? COUNTIFS with range and criteria pairs: =COUNTIFS(C2:C13,"Sales",F2:F13,">=4") counts Sales staff rated 4 or higher.
21. How do you total amounts between two dates? Use SUMIFS with the date column twice: =SUMIFS(E2:E13,D2:D13,">="&DATE(2025,1,1),D2:D13,"<"&DATE(2025,2,1)) adds January 2025. The & joins the operator to the date.

Data tool questions (22–24)#

These check that you know when a formula isn't the best tool.

22. What is a pivot table, and when would you use one? A pivot table summarizes a table without formulas, such as total sales by region and product. Use it to explore data or build a report fast. Remember it doesn't update on its own: refresh it after the data changes.
23. How do you remove duplicates? Data > Remove Duplicates deletes repeated rows from the data. =UNIQUE(A2:A13) lists each value once in new cells and leaves the data alone. UNIQUE needs Excel 2021 or Microsoft 365.
24. What's the difference between sorting and filtering? Sorting changes the order of rows. Filtering hides rows that don't match. SUM still adds hidden rows; =SUBTOTAL(109,E2:E13) adds only the visible ones.

A troubleshooting question (25)#

Most interviews include one "what would you check?" question.

25. A colleague's total looks too low. How do you find the problem? Check that the range covers every row. Look for numbers stored as text, often left-aligned with a small green triangle: compare =COUNT(E2:E13) with =COUNTA(E2:E13). Look for hidden or filtered rows, then press Ctrl+` to read the formulas.

How to answer Excel interview questions well#

A strong answer has three parts: what the tool does, its arguments in order, and one limit or edge case. For example: "SUMIFS adds numbers that meet several conditions. The sum range comes first, then range and criteria pairs. Every condition must be met, so for an OR you add two SUMIFS together."

If you don't know an answer, say how you'd find out, such as testing the function on a few rows. Role-specific lists help too: see the Excel skills for finance and accounting jobs, the skills for admin and office jobs and the skills every data analyst needs.

Practise the answers#

Reading answers is not the same as doing them. Lock references in relative vs absolute references, build lookups to the left in INDEX and MATCH, then answer real questions under test conditions in the HR roster interview drill. Start with the exercise below: it is question 14.