Why a Weak Excel Answer Doesn't Always Mean Rejection

Interview Insights · Excel

Why a Weak Excel Answer Doesn't Always Mean Rejection

SECTION 01What I score when an Excel answer goes wrong

Candidates often assume an interview is pass or fail on every question. It is not. I usually leave a round with notes across several areas, and a single weak answer touches only one of them. If you forget the order of arguments in MATCH, your syntax score dips. Your logic, honesty and learning scores can still be strong, and those carry more weight for a fresher.

The table below shows common weak answers and how much each one really costs, depending on what happens next.

Q: Return the price of product P-104 from a table
   where Product is in column B and Price in column A.

Candidate: =MATCH(B:B,"P-104")... no, wait.

'Let me say what it must do first. Price is to the left,
so VLOOKUP will not work. INDEX returns from the Price
column, and MATCH finds the row of P-104 in column B,
with 0 for exact match.'

=INDEX(A:A,MATCH("P-104",B:B,0))

Score: syntax slipped, logic and recovery strong.

Q: Return the price of product P-104 from a table
   where Product is in column B and Price in column A.

Candidate: =VLOOKUP("P-104",B:A,1,0)

Interviewer: Will that work with the price on the left?

Candidate: Yes, VLOOKUP can search backwards if you
reverse the range.

(It cannot. Excel treats B:A as A:B.)

Score: the original slip was small. The bluff is what
lowers trust in every other answer.

Notice that both candidates made a mistake in the first line. The difference is entirely in the next move. One returned to the problem; the other defended the mistake.

SECTION 02Why picking up a hint can beat a perfect answer

When an answer stalls, many interviewers give a hint. It might be a question such as 'what if the list were not sorted?' or a nudge like 'is there a function that counts with a condition?'. This moment is a small test of its own. It shows me how you would respond to a senior colleague's feedback on your first week.

A candidate who hears the hint, says what it changes, and fixes only the affected part often leaves a better impression than one who recited a perfect answer, because I have now seen them learn in real time.

Q: Give a grade: 80+ is A, 60-79 is B, below 60 is C.

First attempt (weak but fixable):
=IF(B2>60,"B",IF(B2>80,"A","C"))

Interviewer hint: 'What grade does 85 get?'

Candidate: 'B, which is wrong. The first test catches
everything above 60, so I must test the highest band
first.'

=IF(B2>=80,"A",IF(B2>=60,"B","C"))

Or, in Excel 2019 and later:
=IFS(B2>=80,"A",B2>=60,"B",TRUE,"C")

Q: How many orders from Delhi are above 5000?

First attempt:
=COUNTIF(C:C,"Delhi")

Interviewer hint: 'That counts all Delhi orders.'

Candidate: 'Right, I need a second condition on the
amount, so COUNTIFS with two criteria pairs.'

=COUNTIFS(C:C,"Delhi",D:D,">5000")

What the interviewer heard: the candidate named the
missing condition before changing the formula.

The key phrase in both examples is the candidate explaining why the first version was wrong. That one sentence turns a correction into evidence of understanding.

If you do not understand a hint, say so: 'Could you tell me a little more about what you mean by sorted?' Asking is better than nodding and changing random parts of the formula.

SECTION 03When a weak Excel answer really does cost you the round

It would be dishonest to say every weak answer is forgivable. Some gaps are hard to overlook for an analyst role, because daily work depends on them. If you cannot explain absolute references, every formula you copy down is at risk. If you think an average of percentages is always the overall percentage, your reports will quietly mislead people.

The good news is that this list is short, and every item on it can be learned properly in a few focused practice sessions.

Sheet: A = Product, B = Price, E1 = GST rate (18%)

Wrong (copied down from C2):
=B2*E1   ->  C3 becomes =B3*E2  (E2 is empty, result 0)

Right:
=B2*$E$1 ->  C3 becomes =B3*$E$1

Mixed references for a rate table:
=$A2*B$1  locks column A and row 1, so a
multiplication grid fills correctly in both directions.

Shortcut: select the reference and press F4 to cycle
$E$1 -> E$1 -> $E1 -> E1.

Store   Visitors   Buyers   Conversion
A         1000       100      10%
B          100        50      50%

Wrong overall rate:
=AVERAGE(D2:D3)            -> 30%

Right overall rate:
=SUM(C2:C3)/SUM(B2:B3)     -> 150/1100 = about 13.6%

What to say: 'The simple average treats both stores as
equal in size. The overall rate must weight by visitors,
so I divide total buyers by total visitors.'

If one of these gaps shows up in your interview, the best response is still honesty. Saying 'I am not confident with mixed references, but I know $ locks the row or column' is far better than a guess that produces zeros.

SECTION 04Recovery scripts for a shaky Excel answer

Recovery is a skill you can rehearse. Practise these lines out loud until they feel natural, so that when a formula breaks in the interview you reach for a script instead of freezing.

WHEN YOU FORGET ARGUMENT ORDER

"I don't remember the exact order, so let me say what
the formula has to do."

1. Name the value you are looking for
2. Name where to search for it
3. Name what to return
4. Say exact match or not

Then write it. If Excel shows #N/A, say:
"#N/A usually means the value was not found. I would
check for spaces or a text/number mismatch first."

WHEN YOUR ANSWER IS CLEARLY WRONG

"That total looks too high. Let me check one row
by hand."

Then:
- Pick one row and calculate it mentally
- Compare with the formula result
- Name the cause: wrong range, wrong reference,
  missing condition, text values
- Fix only that part

Never say: "Excel must be wrong."

WHEN YOU HAVE NOT USED THE FEATURE

"I haven't used Power Query yet. What I have done is
clean data with TRIM, Text to Columns and Remove
Duplicates. From what I understand, Power Query lets
you record those steps and refresh them, which is
something I plan to learn next."

Structure:
1. Honest admission
2. Closest thing you have done
3. What you understand it does
4. Willingness to learn

SECTION 05Habits that soften the impact of a mistake

These small habits make a weak moment look like a normal part of working with data.

• Talk before you type — Saying what the formula must do means a later syntax slip looks like a typo, not a lack of understanding.
• Test one row by hand — Checking one row mentally is the fastest way to catch your own error before the interviewer points it out.
• Thank the hint, then use it — A short 'good point' followed by a clear fix shows you are easy to work with and quick to adjust.
• Never defend a wrong result — Arguing for an answer you are unsure of turns a small slip into a trust problem across the whole interview.
• Close the loop — After fixing an answer, say in one sentence what you would do differently next time. It ends the question on a strong note.

SECTION 06Interview Q&A — Weak Excel answers

SECTION 07Test yourself — Weak Excel answers quiz

SECTION 08Frequently asked questions

SECTION 09Related reads