Fifteen common Excel interview questions
- What's the difference between relative, absolute and mixed references?
- VLOOKUP versus INDEX/MATCH versus XLOOKUP?
- How do SUMIFS and COUNTIFS work?
- How do you build and refresh a pivot table?
- How do you remove duplicates and clean messy text?
- How do you split a full name into first and last names?
- How do you handle errors such as #N/A in formulas?
- What is conditional formatting, and how would you highlight outliers?
- How do you calculate the number of days between two dates?
- What is data validation?
- How do you combine data from multiple sheets?
- When would you use Power Query?
- Which chart would you use to show a trend? A composition?
- How do you make a workbook easy for others to use?
- Walk me through how you'd analyse this sales dataset.
Lookups and conditional sums
| Function | Notes |
|---|---|
| VLOOKUP | Looks right only; breaks when columns are inserted; use exact match (FALSE) |
| INDEX/MATCH | Looks in any direction; robust to column changes |
| XLOOKUP | Modern replacement: any direction, built-in not-found value, exact match by default |
| SUMIFS / COUNTIFS | Sum or count with multiple criteria: =SUMIFS(sales, region, "West", month, "Jan") |
| IFERROR / IFNA | Replace errors with a value; IFNA is safer because it only catches #N/A |
References and logic
- Relative (A1) shifts when copied; absolute ($A$1) stays fixed; mixed ($A1 or A$1) fixes one dimension — essential for copying formulas across tables.
- IF, AND, OR, IFS handle conditional logic; nested IFs beyond two or three levels are easier as IFS or a lookup table.
- Dates: subtract dates for days between them; NETWORKDAYS excludes weekends and holidays; EOMONTH finds month ends.
- Text: TRIM, PROPER, LEFT, RIGHT, MID, TEXTSPLIT or Text to Columns, and TEXTJOIN for combining.
Pivot tables and cleaning
Convert data to a Table first so pivots and formulas expand automatically. Build a pivot by dragging fields into rows, columns, values and filters; change aggregation from sum to average or count; group dates by month or quarter; add slicers for interactive filtering; refresh after data changes. For cleaning, remove duplicates, trim spaces, standardise text case, convert text numbers to numbers and check for blanks. Power Query repeats these steps automatically when new data arrives, which is valuable for monthly reports.
Charts and presentation
- Line charts for trends over time; column or bar charts for comparisons; stacked bars or a simple table for composition — pie charts only with a few categories.
- Label axes, remove clutter and highlight the point you want the reader to see.
- Separate inputs, calculations and outputs on different sheets or clearly marked areas, and avoid hard-coded numbers inside formulas.
The skills test
Read every question before starting, keep the raw data untouched on its own sheet, and build helper columns rather than one monstrous formula. Check results with a quick sanity total. Leave short notes explaining your approach. If something is ambiguous, state your assumption in a cell comment. See data analyst interview questions and financial analyst interview questions.
Excel and AI features
Interviewers increasingly ask how you'd use built-in AI and automation features responsibly. A good answer: use them to draft formulas or summarise, then check the result against a manual calculation on a sample before relying on it, and never paste confidential data into tools your employer hasn't approved.
Practising
Reading answers is not the same as saying them. Pick ten of the questions above and answer them aloud against a timer; a free AI interview assistant like CoPilot Interview can ask Excel questions in a mock interview and flag answers that ran long or lacked an example. Rebuild a small sales report from raw data in 30 minutes — lookup, SUMIFS, a pivot and a chart — then explain it aloud as you would to an interviewer.
Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly. Everything else on this page applies whichever tool you use, or none.
Frequently asked questions
What Excel skills are tested in interviews?
Lookups (XLOOKUP, INDEX/MATCH, VLOOKUP), SUMIFS and COUNTIFS, cell references, IF logic, text and date functions, pivot tables, data cleaning, conditional formatting, charts and sometimes Power Query.
Should I use VLOOKUP or XLOOKUP?
XLOOKUP where available: it looks in any direction, defaults to exact match and handles not-found values. INDEX/MATCH is the robust alternative in older versions.
What is the difference between absolute and relative references?
Relative references shift when a formula is copied; absolute references (with $) stay fixed. Mixed references fix only the row or the column.
How do I prepare for an Excel test?
Practise cleaning a messy dataset, writing lookups and conditional sums, building pivot tables and charts, and explaining your steps under time pressure.
Practise on real interview questions
Run 145 real interview questions across 10 tracks, answer out loud and see a structured answer to compare with yours. Free, no signup.
Try a practice question