Why Excel Questions Reveal More Than Any Resume Line

Interview Insights · Excel

Why Excel Questions Reveal More Than Any Resume Line

SECTION 01What 'Advanced Excel' on a resume hides from me

When I screen analyst resumes, the skills block usually looks the same: Advanced Excel, Pivot Tables, VLOOKUP, Dashboards. The words are not wrong. They are just not informative, because a candidate who used VLOOKUP once in a college assignment writes the same words as one who maintains a weekly sales report.

So in the interview I convert each claim into a tiny task. I do not ask 'Do you know pivot tables?' because everyone says yes. I ask you to do something with one, on a data shape that looks like real work. Your answer then shows me what the resume could not.

Q: Bring the Region from the Customers sheet into Orders.

=VLOOKUP(B2,Customers!A:C,3)

Problems an interviewer notices:
- No 4th argument, so it defaults to approximate match
- Returns wrong regions silently on unsorted IDs
- Column number 3 breaks if someone inserts a column
- No plan for customer IDs that are missing

Q: Bring the Region from the Customers sheet into Orders.

=XLOOKUP(B2,Customers!A:A,Customers!C:C,"Not found",0)

Or, in older Excel versions:
=IFERROR(INDEX(Customers!C:C,MATCH(B2,Customers!A:A,0)),"Not found")

What I would say out loud:
'I use exact match because customer IDs are not sorted.
I return Not found instead of #N/A so I can filter
and count missing customers before I report anything.'

The second answer is not long, and it is not clever. It simply shows that the candidate has been burned by approximate matches and missing keys before. That experience is what the resume line was supposed to communicate and could not.

SECTION 02The follow-up ladder: how one question goes three levels deep

The first question is rarely the one that decides the round. The follow-ups do. Once you give a working formula, I change one condition and watch how you adapt. A candidate who memorised answers stops at level one. A candidate who has used Excel on real data keeps climbing, because each follow-up is a problem they have already met at work or in a project.

Here is a typical ladder I use for SUMIFS, one of the most common functions in analyst work.

Sheet: Sales  (A: OrderID, B: Date, C: Region, D: Amount)

Level 1
=SUMIFS(D:D,C:C,"North")

Level 2  (March 2025 only)
=SUMIFS(D:D,C:C,"North",B:B,">="&DATE(2025,3,1),B:B,"<"&DATE(2025,4,1))

Level 3  (region picked in G1, month start in G2)
=SUMIFS(D:D,C:C,G1,B:B,">="&G2,B:B,"<"&EDATE(G2,1))

Level 4  (find amounts stored as text)
=SUMPRODUCT(--ISTEXT(D2:D5000))

Weak explanation:
'I put March in the criteria.'

Strong explanation:
'Criteria on dates need the operator as text joined
to a real date, so I use ">="&DATE(2025,3,1).
I use less-than the first of April instead of
less-than-or-equal the 31st, so the formula also
works if the dates include a time part.'

Level 4 is my favourite question because it has no single formula answer. It asks what you do when two correct-looking numbers disagree. Candidates who check data types and stray spaces first have clearly reconciled reports before.

If you get stuck on a level, say what you would check. 'I would test whether the amounts are numbers using ISNUMBER' is a good answer even if you do not finish the formula.

SECTION 03Rewriting your resume line so the interview confirms it

Once you understand that every skill line will be tested, the goal of your resume changes. It is no longer to list as many Excel features as possible. It is to make claims that the interview will confirm, with enough detail that I already know which question to ask you.

A specific line also helps you, because it steers the interview toward work you have actually done. If your resume says you built a monthly sales tracker with SUMIFS and a pivot chart, I will ask about that tracker, and you can answer from memory instead of from theory.

SKILLS
Advanced Excel, VLOOKUP, HLOOKUP, Pivot Tables,
Macros, Dashboards, Charts, Conditional Formatting,
Data Analysis, MS Office

Problem: ten claims, no evidence, and the interviewer
will pick the one you know least (often Macros).

SKILLS
Excel: XLOOKUP, SUMIFS/COUNTIFS, Pivot Tables, Power Query (basic)

PROJECT: Retail Sales Tracker (Excel)
- Combined orders and customer sheets with XLOOKUP,
  flagging missing customer IDs for review
- Built pivots for region-wise and month-wise revenue,
  with % of total and slicers for category
- Cleaned names and dates with TRIM and DATEVALUE
  before analysis

Every line here is something you can demonstrate live.

Remove anything you cannot demonstrate in five minutes. If you list Macros, expect to be asked to explain a loop in VBA. It is better to leave it off than to lose trust on the one claim you cannot support.

SECTION 04Worksheet: prove each Excel claim before the interview

Use this worksheet the week before an analyst interview. For every Excel skill on your resume, you should be able to do a small task, explain it in two sentences, and survive one follow-up. If you cannot, either practise it or remove it.

FOR EACH EXCEL SKILL ON MY RESUME

Skill: ______________________
A task that proves it: ______________________
Can I do it without searching online?  Y / N
My two-sentence explanation:
  1. ______________________
  2. ______________________
One edge case I know how to handle: __________

If any answer is N or blank -> practise or remove.

PRACTICE SET (use any sample sales data)

1. Lookup region into orders with XLOOKUP, return 'Not found'
2. Count orders per region with COUNTIFS
3. Sum one region for one month with SUMIFS and DATE
4. Pivot: revenue by month, grouped from daily dates
5. Pivot: each category as % of column total
6. Find duplicate emails with COUNTIF(A:A,A2)>1
7. Clean names with =PROPER(TRIM(A2))
8. Convert an Excel range to a Table (Ctrl+T) and
   explain why formulas now expand automatically

ASK A FRIEND TO CHANGE ONE CONDITION EACH TIME

Start: total sales for North
+ only for one month
+ region taken from a dropdown
+ exclude cancelled orders (Status column)
+ the total does not match the pivot - why?

Score yourself:
[ ] Repeated what changed before typing
[ ] Edited only the needed argument
[ ] Said what I would check when stuck

SECTION 05Habits that make your Excel answers believable

Interviewers trust answers that sound like they come from real files. These habits make that obvious.

• Name the data shape first — Before writing a formula, say what the columns are and what one row represents. It shows you think about the data, not just the function.
• Always mention exact match — Saying why you set exact match in a lookup is a quick signal that you know where lookups go wrong.
• Plan for missing values — Mention what happens when a key is not found and how you would count or flag those rows before reporting.
• Use Tables, not fixed ranges — Explaining that an Excel Table grows with new data shows you have maintained a report over time.
• Check totals both ways — Say that you compare a formula total with a pivot total. Reconciling numbers is a habit interviewers value highly.

SECTION 06Interview Q&A — Excel questions

SECTION 07Test yourself — Excel questions quiz

SECTION 08Frequently asked questions

SECTION 09Related reads