Excel Interview Questions for Data Analysts

Most analyst job descriptions in the region list SQL and Excel in the same line, and most interview loops test both. The SQL round gets all the preparation attention. The Excel round is the one people walk into cold, and it is usually the one where the rejection happens — not because the formulas are hard, but because candidates practise Excel as a spreadsheet skill and get tested on it as a data skill.

Those are different things. Nobody asks you to build a budget template or format a chart. They hand you two tabs of messy export data and ask a question that has one correct number as its answer.

What actually gets asked

Across analyst interviews at ride-hailing, e-commerce, banking and logistics companies in Southeast Asia, the Excel round is nearly always one of four shapes. Each has a direct equivalent in SQL, which is the useful way to hold them in your head.

If you can write the SQL, you already understand the concept. What is missing is the Excel dialect and the specific traps, which are different from the SQL ones.

A real question shape: the ops rollup

Here is the format almost verbatim as it gets asked. You are given a trips tab — one row per trip, roughly 40,000 rows — with columns for trip id, city, driver id, status, fare and completed timestamp. You are given a second tab, cities, mapping each city to a region and a launch date.

Question: total fare by region for completed trips in the last full month.

It looks like a two-minute task. Here is where candidates lose it, in the order the mistakes usually happen.

They aggregate before filtering. SUMIF on city, then try to filter status afterwards. The moment there is more than one condition, you need SUMIFS — plural — and both conditions go in the same call:

=SUMIFS(trips[fare], trips[status], "completed", trips[city], A2)

This is exactly the WHERE before GROUP BY distinction from SQL. Filter at the right stage or the number is wrong in a way that still looks plausible.

They forget cancelled trips have fares. A cancelled trip often carries a non-zero fare in the raw table — cancellation charges are real revenue in some contexts and not in others. The candidate who asks "should cancelled trips count?" before writing anything scores higher than the one who produces a fast answer to the wrong question. This is the single most common differentiator in the round, and it is not a formula skill at all.

They join on a dirty key. The city name in trips is "Kuala Lumpur " with a trailing space; in cities it is "Kuala Lumpur". The lookup returns #N/A for an entire region, the candidate sees a few error cells, wraps the whole thing in IFERROR to make them disappear, and reports a total that is missing a third of the country. Wrapping an error to hide it, rather than to handle a known case, is the most expensive habit in spreadsheet work. Find out why it errored first.

They report a number with no sanity check. The strongest candidates finish by checking that the regional totals sum to the grand total of completed fares. Ten seconds, catches every one of the mistakes above.

The dates trap

"Last full month" is doing more work than it looks. If today is 3 September, that is 1–31 August, not the last 30 days, and not August-to-date. Excel dates are serial numbers, so the correct boundary comparison is arithmetic, not text:

=SUMIFS(trips[fare], trips[completed], ">="&EOMONTH(TODAY(),-2)+1, trips[completed], "<="&EOMONTH(TODAY(),-1), trips[status], "completed")

Two things there are worth memorising. The ">="& concatenation is how you build a criteria string from a computed value — writing ">=EOMONTH(TODAY(),-2)+1" as a literal string does not evaluate, it compares against text and silently returns zero. And EOMONTH(TODAY(),-1) is the last day of last month, which is why the start boundary is EOMONTH(TODAY(),-2)+1.

Also: if the timestamp column carries a time component, "<=" against a date matches only midnight of that day, so the last day of the month silently drops out. Compare against EOMONTH(...)+1 with a strict "<" if there are times in the column. This is the same inclusive-versus-exclusive boundary bug that shows up in every SQL date question, wearing different clothes.

Questions to expect, by company shape

• Ride-hailing and delivery — trip and order tables joined to drivers, merchants and promos. Conditional aggregation with a status filter, almost always. Watch for the cancelled-row question.
• E-commerce marketplaces — one row per order line, not per order. Every per-order metric needs a deduplication step first, and candidates who skip it double-count shipping.
• Banking and fintech — transaction ledgers plus customer state. Balances, and customers whose activity is missing rather than zero. The Excel equivalent of the LEFT JOIN trap: a lookup that returns #N/A for a customer with no transactions, where the right answer is zero.
• Logistics — timestamps, SLAs and date differences. Working-day arithmetic via NETWORKDAYS and WORKDAY, which most candidates have never used.

How to prepare in a week

Do not work through a general Excel course. The overlap with what gets tested is smaller than it looks, and most of the syllabus is formatting and charting you will never be asked about.

Instead: take the SQL concepts you are already comfortable with and learn their Excel dialect one at a time. Start with the lookup family, since it is the most-asked and has the most traps — VLOOKUP vs INDEX+MATCH vs XLOOKUP covers which to use and why the default is wrong. Then conditional aggregation, in SUMIFS and COUNTIFS: the GROUP BY of Excel.

If your SQL is not solid yet, do that first — it is the harder half and it transfers. The free SQL course starts from SELECT and the question bank grades your queries in the browser.