Scenario-Based Excel Interview Questions (With Answers)

Excel scenario based interview questions don't ask "what does SUMIFS do?". They describe a situation, such as "your manager needs North sales for March by noon", and ask how you would handle it. This guide gives ten common scenarios with model answers, all worked on one small table, plus a simple way to structure any answer so the interviewer hears both the formula and your thinking.

How to answer a scenario question#

The interviewer is checking whether you pick the right tool and whether you'd catch your own mistakes. Use the same four steps every time:

1. Restate the goal in one sentence: "So you need the total for one region in one month."
2. Name the tool and why: "I'd use SUMIFS, because there are two conditions."
3. Give the exact formula or the clicks.
4. Say how you'd check it: "I'd filter the table and compare with the status bar total."

Step 4 is the one most candidates skip, and it's often what separates a good answer from a great one. For quick-fire definition questions, see the 25 common Excel interview questions.

The sample data#

All the answers use this order list in A1:E8:

Lookup scenarios (1–3)#

1. "A customer calls with order 1004. How much was it?" Use XLOOKUP to find the ID and return the amount:

=XLOOKUP(1004,A2:A8,E2:E8)

The result is 1,500. In practice, put the ID in a cell so you can reuse the formula. XLOOKUP needs Excel 2021 or Microsoft 365; in older versions, =VLOOKUP(1004,A2:E8,5,FALSE) does the same. The XLOOKUP function guide covers its optional arguments.

2. "Your VLOOKUP returns #N/A for IDs that are clearly in the list. Why?" Say what you'd check, in order: a missing FALSE (approximate match), extra spaces, numbers stored as text on one side, or a lookup column that isn't the first column of the range. Then the fix: TRIM the data, convert text numbers with VALUE, or switch to XLOOKUP.

3. "Some reps aren't in the region list. Show 'Not found' instead of an error." Use XLOOKUP's fourth argument, if_not_found: =XLOOKUP(G2,RepList,RegionList,"Not found"). In older Excel, wrap VLOOKUP in IFNA. Mention that IFNA is safer than IFERROR here, because it hides only the "not found" error and still shows real mistakes.

Summary scenarios (4–6)#

4. "What were North sales in March 2026?" Two conditions means SUMIFS, with the date as a range:

=SUMIFS(E2:E8,D2:D8,"North",B2:B8,">="&DATE(2026,3,1),B2:B8,"<="&DATE(2026,3,31))

The result is 4,400 (1,200 + 800 + 300 + 2,100). Explain that DATE is safer than typing "3/1/2026", which depends on regional settings. The SUMIFS function guide shows the full syntax.

5. "How many North orders were over 1,000?" Counting with two conditions means COUNTIFS:

=COUNTIFS(D2:D8,"North",E2:E8,">1000")

The result is 2 (orders 1001 and 1007). Point out that the operator goes inside the quotes. To get their total instead, SUMIFS with the same conditions gives 3,300.

6. "You have 50,000 rows. Summarise revenue by rep and region before the meeting." Say you'd use a pivot table: Insert > PivotTable, Rep in Rows, Region in Columns, Amount in Values. It's quicker than writing formulas for every pair, and it updates with a refresh. The pivot table interview questions cover the follow-ups you're likely to get.

Data cleaning scenarios (7–8)#

7. "The export has duplicate orders. How do you handle them?" First find them: =COUNTIF($A$2:$A$8,A2)>1 flags any repeated order ID. Then decide, with the business, whether they're true duplicates. To remove them, use Data > Remove Duplicates on a copy of the data, choosing the Order column. Mentioning the copy shows you protect the original.

8. "Rep names arrive as ' ana', 'ANA ' and 'Ana'. Your totals split them." Clean them in a helper column with =PROPER(TRIM(C2)), which removes extra spaces and fixes the capitals, then paste the results back as values. Explain that SUMIFS isn't case-sensitive but does see the spaces, which is why the totals split.

Analysis scenarios (9–10)#

9. "Ana sold 3,000 in February and 4,100 in March. What's the growth?" Percent change is new divided by old, minus 1:

=4100/3000-1

The result is about 0.367, or 36.7% when formatted as a percentage. In a real sheet, use cell references, and wrap it in IFERROR in case last month was 0.

10. "Flag orders that shipped more than 5 days after the order date." With a ship date in column F, use =IF(F2-B2>5,"Late","On time"). Dates are numbers, so subtracting gives the days between them. If the rule is working days, use NETWORKDAYS(B2,F2)-1>5 instead, and say which you assumed.

A problem-solving scenario#

"Your total doesn't match the number finance sent. What do you do?" This tests your method, not a formula. A strong answer covers:

• Check the scope. Same date range, same regions, same status (for example, cancelled orders)?
• Check the data. Look for duplicates, numbers stored as text and hidden or filtered rows.
• Rebuild the total another way. Compare the SUMIFS result with a pivot table or a filtered total.
• Find the gap. Break both totals down by month or region to see where they differ.

Longer versions of this become full case studies. See how to handle an Excel case study interview, and for harder technical questions, the advanced Excel interview questions.

How to prepare for scenario questions#

Reading model answers helps, but typing them under time pressure is what builds confidence. Practise on real data until the formulas come without thinking, then rehearse saying the four steps out loud.

Many employers also send a timed test before or after the interview; what to expect from an Excel assessment explains the usual formats. To try one, take the Excel case study mock test. If you're not sure where to start, the Excel level test shows which skills to work on first.

Practise it#

Scenario questions reward hands-on practice. Start with the interview drill: sales report, which asks the kind of questions on this page in a live spreadsheet. Then work through the real estate case study and sharpen your totals in the COUNTIFS and SUMIFS lesson. Every answer is checked instantly.