Skip to content

Interview Questions — Excel for Analytics

Excel questions appear in nearly every data analyst interview. These cover formulas, pivot tables, lookups, and the judgment to know when Excel is the right tool.

See also

The dedicated Excel Interview Questions bank has more questions with full answers. This file focuses on the Day 01 fundamentals.

Formulas

[Beginner] What is the difference between SUM, SUMIF, and SUMIFS?

Show answer
  • SUM(range) — totals all values in a range
  • SUMIF(range, criteria, sum_range) — totals values that meet ONE condition
  • SUMIFS(sum_range, range1, criteria1, ...) — totals values that meet MULTIPLE conditions
=SUM(G2:G100)
=SUMIF(D2:D100, "completed", G2:G100)
=SUMIFS(G2:G100, D2:D100, "completed", C2:C100, "Electronics")

Note the argument order trap: SUMIF puts the sum_range LAST, while SUMIFS puts it FIRST.

[Beginner] What does the $ symbol do in a cell reference?

Show answer

It locks part of a reference so it doesn't shift when the formula is copied. - A1 — relative: both shift - $A$1 — absolute: neither shifts (use for constants like a tax rate) - $A1 / A$1 — mixed: one part locked

The classic use: a discount rate in cell B1 used across many rows — write =A2*$B$1 so $B$1 stays locked while A2 shifts down. Press F4 to cycle reference types.

Pivot Tables

[Beginner] What is a pivot table and when would you use one?

Show answer

A pivot table summarises and reorganises large amounts of data interactively, without formulas. You drag fields into Rows, Columns, Values, and Filters to aggregate data (sum, count, average) by category.

Use it for: revenue by region, order count by status, monthly trends, top products — any "summarise by category" question. It's the fastest way to explore a few thousand rows.

[Mid-level] How would you show each region's revenue as a percentage of the total in a pivot table?

Show answer

Right-click the value field → Value Field Settings → Show Values As → % of Grand Total (or % of Column Total for share within a column). No formula needed — the pivot recalculates the percentages automatically and updates with filters.

Lookups

[Beginner] What is the difference between VLOOKUP and XLOOKUP?

Show answer

XLOOKUP is the modern replacement (Excel 365/2021+): - Looks up in any direction (VLOOKUP only looks right) - Exact match by default (VLOOKUP defaults to approximate — a bug source) - Built-in not-found handling (VLOOKUP needs IFERROR) - Robust to column insertion (VLOOKUP uses a fragile column index number)

=XLOOKUP(A2, Products[ID], Products[Name], "Not found")
=VLOOKUP(A2, Products!A:D, 3, FALSE)

Use XLOOKUP when available; VLOOKUP for older-version compatibility.

[Mid-level] A VLOOKUP returns #N/A even though you can see the value in the data. What are the likely causes?

Show answer
  1. Data type mismatch — the lookup value is text ("1001") but the data is numbers (1001), or vice versa
  2. Trailing/leading spaces — "C001 " ≠ "C001"; wrap with TRIM()
  3. Approximate match without sorting — if the 4th argument is TRUE/omitted and data isn't sorted; use FALSE
  4. Lookup value is right of the return column — VLOOKUP can't look left; use XLOOKUP or INDEX/MATCH

Judgment

[Mid-level] When is Excel the right tool, and when should you move to SQL or Power BI?

Show answer

Excel is right for: - Datasets up to ~1M rows (Power Pivot extends this) - Ad-hoc, one-off analysis - Quick financial models and what-if scenarios - Self-contained analyses to share as a file

Move to SQL when: - Data lives in a database with millions of rows - You need repeatable, queryable analysis

Move to Power BI/Tableau when: - You need an interactive dashboard for many users - The data must auto-refresh on a schedule - You need centralised, governed, secured reporting

The mark of a good analyst is choosing the right tool, not forcing everything into the one they know best.

[Senior] How would you make a complex Excel workbook maintainable and resistant to errors?

Show answer
  • Use Tables (Ctrl+T) with structured references instead of raw ranges — they auto-expand and are readable (Sales[Revenue] vs G2:G10000)
  • Use named ranges for key constants and inputs
  • Separate inputs, calculations, and outputs into distinct areas/sheets
  • Avoid hardcoded magic numbers — put assumptions in labelled cells
  • Prefer XLOOKUP/INDEX-MATCH over VLOOKUP with hardcoded column indexes (which break on column insertion)
  • Use Power Query for repeatable data cleaning instead of manual steps
  • Document assumptions in a notes section
  • Add data validation to input cells to prevent bad entries

The goal: someone else (or you in six months) can understand and safely modify the workbook without it silently breaking.

Previous: 06-mini-exercises | Next: SQL Basics