Excel Interview Questions and Answers (Beginner to Advanced)
Common Excel interview questions with short model answers: references, IF, VLOOKUP vs XLOOKUP, pivot tables, errors and data cleaning.
By Ayoub Farid · · 3 min read
Besides a practical test, interviewers often ask spoken Excel questions to check you understand the concepts. Here are the common ones with short answers you can adapt.
Beginner questions
What's the difference between a formula and a function?
A formula is any calculation starting with =. A function is a built-in named calculation used inside a formula, like SUM.
What is a cell reference?
The address of a cell, like B2, used in a formula so the result updates when that cell changes.
What's the difference between relative and absolute references?
Relative references (B2) shift when you copy a formula; absolute references ($B$2) stay fixed. I use absolute references for totals, rates and lookup tables. See absolute references.
How do you calculate a percentage of a total?
Divide each value by the total and lock the total: =B2/$B$14, formatted as %.
What's the difference between COUNT and COUNTA?
COUNT counts numbers; COUNTA counts non-empty cells.
Intermediate questions
How does IF work?
=IF(test, value_if_true, value_if_false). For several conditions I combine it with AND/OR or use IFS.
Explain VLOOKUP.
It finds a value in the first column of a table and returns a value from a column to the right. I always use FALSE for an exact match and lock the table range.
VLOOKUP vs XLOOKUP?
XLOOKUP looks in any direction, uses exact match by default, has a built-in not-found argument and doesn't break when columns are inserted. VLOOKUP is still useful for older versions. See XLOOKUP vs VLOOKUP.
SUMIF vs SUMIFS?
SUMIF has one condition; SUMIFS has several. SUMIFS puts the sum range first.
What does #N/A mean and how do you fix it?
A lookup didn't find a match. I check for extra spaces, numbers stored as text and the match type, then use IFERROR or XLOOKUP's not-found value if missing values are expected.
How do you remove duplicates?
Data > Remove Duplicates, or UNIQUE in Excel 365, after cleaning spaces with TRIM.
Advanced questions
What is a pivot table and when would you use it?
A tool to summarise large data by categories (e.g. sales by region and month) without formulas. I use it for quick analysis and reports.
INDEX/MATCH vs VLOOKUP?
INDEX/MATCH can look left, survives inserted columns, and works in all versions.
How would you clean messy data?
TRIM for spaces, PROPER/UPPER for case, Text to Columns or TEXTSPLIT for splitting, Remove Duplicates, and checking numbers stored as text.
How do you make a report update automatically?
Use formulas that reference the data (SUMIFS, XLOOKUP, dynamic arrays) or tables and pivot tables with refresh, instead of typed values.
Tips for answering
- Give a real example from your work or a project.
- Mention how you check your work (changing inputs, checking the last filled row).
- If you don't know something, say how you'd find out, then move on.
Practise the written side too with basic Excel test questions and our job test guide.
Frequently asked questions
What Excel questions are asked in interviews?
References, IF, VLOOKUP/XLOOKUP, SUMIF(S), pivot tables, errors and data cleaning are the most common topics.
How do I explain my Excel skills in an interview?
Name the specific functions and tools you use and give a concrete example of a task you completed with them.
Will I have to do a practical Excel test?
Often, yes, especially for admin, finance and analyst roles.
What is the most important Excel skill for interviews?
Understanding references (including $) and being comfortable with IF, SUMIFS and a lookup function.