Excel interview questions, answered by someone who uses it

Most lists of Excel interview questions give you the definition of a function. Definitions are not what is being tested.

An interviewer who asks about VLOOKUP already knows what it does. They are finding out whether you have had it go wrong on you. So every answer below has two parts. The short answer, and the sentence that shows you have used it.

Lookups

What does VLOOKUP do?

Short answer. It searches for a value in the first column of a table. It returns something from the same row. Four arguments. What to find. Where to look. Which column to return. And whether to allow an approximate match.

The part that shows experience. "I always write FALSE for the fourth argument. Left out it means TRUE. On unsorted data that returns the wrong row, with no error at all."

What is the difference between VLOOKUP and INDEX with MATCH?

Short answer. VLOOKUP takes a column number. INDEX and MATCH take addresses. So it can look to the left, and it does not break when a column is inserted.

The part that shows experience. "The column number is the problem. Somebody inserts a column six months later and every VLOOKUP returns the column next door. Nothing errors. The report is just wrong."

When would you use XLOOKUP?

Short answer. Whenever the file stays with people on Excel 365 or 2021. It is exact by default, looks in any direction, and takes a not-found message as an argument.

The part that shows experience. "I check who opens the file first. XLOOKUP does not exist before Excel 2021, so a shared file gets INDEX and MATCH instead."

Errors

What does #N/A mean, and how do you handle it?

Short answer. A lookup found nothing. Wrap it in IFNA if a miss is expected.

The part that shows experience. "IFNA, not IFERROR. IFERROR hides #REF! and #VALUE! too. A formula that is really broken shows a neat message, and the total is wrong."

What is the difference between #VALUE! and #REF!?

Short answer. #VALUE! means the wrong kind of thing went into the formula. Usually text where a number was expected. #REF! means the cell the formula pointed at has been deleted.

The part that shows experience. "#VALUE! you fix by cleaning the data. #REF! you fix with undo. The address is gone, and Excel cannot know what it was."

Conditional maths

SUMIF or SUMIFS?

Short answer. SUMIF for one condition. SUMIFS for several. SUMIFS also works fine with just one.

The part that shows experience. "I use SUMIFS even for one condition. The argument order differs between them, and I would rather learn one. SUMIFS puts the range to add first. SUMIF puts it last."

How would you sum everything above a number in a cell?

Short answer. =SUMIF(D2:D99,">"&F1).

The part that shows experience. "The operator has to be in quotation marks and the cell outside them, joined with an ampersand. Writing ">F1" returns 0 and no error. That is a bad half hour if you have not seen it before."

References

What is the difference between A1, $A$1 and $A1?

Short answer. A1 moves when the formula is copied. $A$1 never moves. $A1 keeps the column. The row still moves.

The part that shows experience. "Mixed references are what let one formula fill a whole grid. A times table is one formula. The row is locked one way and the column the other."

Cleaning and shaping

A column of numbers will not add up. What do you check?

Short answer. They are text, not numbers. Check with =ISNUMBER(A2), or look at the alignment: numbers sit right, text sits left.

The part that shows experience. "If they came from a web page there is often a non-breaking space in them. Character 160. TRIM does not remove it. It has to be SUBSTITUTE with CHAR(160)."

How do you remove duplicates?

Short answer. Data → Remove Duplicates for a permanent change. UNIQUE for a live list that updates itself.

The part that shows experience. "I copy the sheet first. Remove Duplicates deletes rows and only tells you how many afterwards, and undo is the only way back."

Pivot tables

When do you use a pivot table instead of formulas?

Short answer. When the question is going to change. A pivot table rearranges in a drag. A grid of SUMIFS has to be rewritten.

The part that shows experience. "Formulas when the report is fixed and somebody else will maintain it. A pivot table for exploring. And a pivot table does not update on its own. It needs a refresh before anybody trusts the numbers."

The question behind the questions

Tell me about a spreadsheet mistake you made

This is the one that decides it. They want to know whether you check your own work.

A good answer names a specific mistake. It says how it was found, and what habit came out of it. A wrong total from an unlocked range. A VLOOKUP that broke when a column moved. A pivot table nobody refreshed. Then: "Now I total the column two ways and compare."

"I am very detail-oriented" is not an answer. Everybody says it, and it proves nothing at all.

The practical test

Many Excel interviews now hand you a file and twenty minutes. What is being watched is not whether you know every function.

• Look at the data first. How many rows, any blanks, any numbers stored as text. Thirty seconds spent here saves the whole attempt.
• Say what you are doing. "I am locking this range so it does not drift when I fill down." Silence tells them nothing.
• Check your answer a second way. A SUMIFS total against a quick pivot table. Doing this in front of them is worth more than the formula was.
• Use the keyboard. Ctrl+arrow to the edge of the data. Ctrl+Shift+arrow to select to it. F4 to lock a reference. Speed here is experience, and it looks like it.
• Say when you do not know. "I would look up the exact arguments for that" beats a confident wrong answer. The interviewer looked some up this week too.

Questions people ask

Which functions come up most?

VLOOKUP or XLOOKUP, IF, SUMIF and SUMIFS, COUNTIF, and pivot tables. Those five cover almost every question. That is for a job that is not an Excel job.

Do I need macros or VBA?

Only if the job advert says so. For most roles, say you avoid them. A workbook with VBA cannot be opened safely by everyone. That is an adult answer.

How do I prepare in a week?

Do the work rather than read about it. Pick a real file. Build one report with SUMIFS, one with a lookup, and one pivot table. Then break each of them on purpose. You want to have met the errors before somebody is watching.