The most common Excel interview questions test five things: VLOOKUP, pivot tables, the IF function, absolute versus relative references, and cleaning data (like removing duplicates). Interviewers aren't looking for textbook definitions; they want to hear that you've actually used these on real work. Below are clear, short answers you can say out loud, with a note on what each question is really checking.
How to approach an Excel interview
Keep answers concrete. When asked about VLOOKUP, don't just define it, say what you used it for: "I used VLOOKUP to pull prices from a master list into a billing sheet." That one sentence tells the interviewer you're job-ready.
Beginner questions
1. What is a cell reference? What's the difference between relative and absolute? A cell reference is the address a formula points to, like A1. A relative reference (A1) changes when you copy the formula; an absolute reference ($A$1) stays locked. You press F4 to lock it. Checking: whether your copied formulas will break.
2. What does the IF function do? IF returns one value if a condition is true and another if it's false: =IF(A2>=33,"Pass","Fail"). Checking: basic logic.
3. How do you remove duplicates? Select the data, go to Data ▸ Remove Duplicates, choose the columns, and click OK. Checking: basic data cleaning.
4. What is the difference between COUNT and COUNTA? COUNT counts cells with numbers; COUNTA counts any non-empty cell, including text. Checking: attention to detail.
5. How do you freeze the header row? View ▸ Freeze Panes ▸ Freeze Top Row, so headings stay visible while scrolling. Checking: practical everyday use.
Intermediate questions
6. What is VLOOKUP and when would you use it? VLOOKUP looks up a value in the first column of a table and returns a value from another column in the same row: =VLOOKUP(E2,A2:C100,3,FALSE). I've used it to bring names or prices from a master sheet into a working sheet. Checking: the single most-used office function.
7. What's the limitation of VLOOKUP, and what fixes it? VLOOKUP can only look to the right of the lookup column. XLOOKUP (Excel 2021/365) or INDEX-MATCH can look in any direction. Checking: depth beyond the basics.
8. What is a pivot table used for? It summarises a long list into a short report, like total sales per region, with no formulas, by dragging fields into Rows, Columns and Values. Checking: whether you can analyse data, not just enter it.
9. How do you combine text from two cells? Use =A2&" "&B2 or the CONCAT/TEXTJOIN functions. Checking: text handling.
10. What does #N/A mean and how do you handle it? It means a lookup couldn't find a match. Wrap the formula in IFERROR to show friendly text: =IFERROR(VLOOKUP(...),"Not found"). Checking: error handling.
Scenario questions
11. You have 5,000 rows of sales. How would you find total sales per salesperson? A pivot table with Salesperson in Rows and Sales in Values, or SUMIF for a single person. Checking: choosing the right tool.
12. How would you highlight all values above 1,00,000 automatically? Conditional Formatting ▸ Highlight Cells Rules ▸ Greater Than ▸ 100000. Checking: presentation and rules.
Quick reference table
| Question topic | One-line answer |
|---|---|
| Absolute reference | $A$1, locked with F4 |
| IF | true/false result |
| VLOOKUP | look up across a table |
| Pivot table | drag-and-drop summary |
| Remove duplicates | Data ▸ Remove Duplicates |
| Handle #N/A | wrap in IFERROR |
Pro tips for the interview
- If you don't know an exact function name, describe what you'd do. Problem-solving beats memorising.
- Mention keyboard shortcuts naturally; it signals real experience.
- If there's a practical test, talk through your steps as you work. Interviewers score your thinking, not just the answer.
Common mistakes candidates make
- Giving textbook definitions with no real example.
- Reaching for the mouse for everything during a hands-on test.
- Saying "I know advanced Excel" but freezing on a basic VLOOKUP. Be honest about your level.
Key takeaways
- Expect VLOOKUP, pivot tables, IF, references and data cleaning.
- Answer with a real example, not just a definition.
- Know how to handle
#N/Aand how to summarise big data.
Practice task
Pick five questions above and answer them out loud without reading. Then open Excel and actually do questions 3, 6 and 8 on a sample sheet, so your hands know the steps, not just your memory.
Walk into your interview confident. The ADCA program at HCI covers Excel plus resume and interview prep with placement support.
Frequently Asked Questions
What are the most common Excel interview questions?
They usually cover VLOOKUP, pivot tables, the IF function, absolute vs relative references, removing duplicates, and handling errors like `#N/A`.
How do I answer a VLOOKUP interview question?
Define it briefly, give the syntax `=VLOOKUP(value,table,col,FALSE)`, and add a real example, such as pulling prices from a master list into a billing sheet.
What level of Excel is needed for a data entry job?
Basic to intermediate: data entry, formatting, SUM/AVERAGE, sorting and filtering, and simple VLOOKUP. Pivot tables are a strong plus.
How do I prepare for an Excel practical test?
Practise the common tasks by hand: VLOOKUP, a pivot table, conditional formatting and remove duplicates. Talk through your steps during the test.
Should I mention keyboard shortcuts in an interview?
Yes. Using shortcuts naturally signals real, day-to-day experience and speed, which interviewers value.