Interview questions

Excel Interview Questions

18 Excel interview questions from basic to advanced: lookups, PivotTables, dynamic arrays, data cleaning and a basic Excel test, with model answers.

Excel Interview Questions

What's here

18 Excel interview questions, grouped into 4 areas: basic excel questions, lookups and formulas, analysis and reporting and practical and advanced questions. Each one comes with why it is worth asking and what a strong answer contains, so the same question can be scored the same way by different interviewers.

  • Give a short practical test on a small messy dataset; it reveals more than any verbal question
  • Ask candidates to talk through their steps while they work, so you can see method as well as result
  • Include one lookup, one PivotTable and one data-cleaning task in any basic Excel test
For hiring teams

Cohesyve finds, contacts and tests Excel candidates on the real work, then ranks them for these interviews. 14-day free trial for 10 candidates, not charged until it ends.

Preparing for this interview yourself? Practise it free →

Excel interview questions appear in interviews for analysts, accountants, administrators, operations staff and almost any office role, and many employers add a basic Excel test. Interviewers want to know whether a candidate can clean data, look things up reliably, summarise with PivotTables and build a sheet that someone else can trust. These questions run from basic to advanced. Candidates can use the 'what to look for' notes as model answers; interviewers can use them to score answers and design a short practical test.

Basic Excel Questions

The essentials most office roles need. Expect these in a basic Excel test for interview.

  1. 1

    What is the difference between relative, absolute and mixed cell references?

    Why ask it

    Copying formulas correctly is the most basic Excel skill, and errors here break whole sheets.

    What to look for

    A relative reference (A1) shifts when copied; an absolute reference ($A$1) stays fixed; a mixed reference ($A1 or A$1) fixes the column or the row. A good example: a tax rate in one cell referenced absolutely while quantities are relative. Pressing F4 to cycle reference types shows practical use.
  2. 2

    How would you remove duplicates and clean up inconsistent text in a list?

    Why ask it

    Data cleaning is most of the real work in Excel.

    What to look for

    Remove Duplicates on the Data tab (after copying the original), TRIM to strip extra spaces, UPPER, LOWER or PROPER for consistent case, Text to Columns for splitting fields, Find and Replace, and Flash Fill. Strong candidates check results with a count before and after.
  3. 3

    Explain SUMIF, COUNTIF and their multi-condition versions.

    Why ask it

    Conditional aggregation is used daily in reporting.

    What to look for

    SUMIF and COUNTIF apply one condition; SUMIFS and COUNTIFS apply several. Example: a SUMIFS that totals the Sales column where Region is North and Month is March. Mentions of AVERAGEIFS, wildcards and criteria from a cell reference show fluency.
  4. 4

    How do you use conditional formatting, and when does it help?

    Why ask it

    Tests whether the candidate makes data readable for others.

    What to look for

    Highlighting rules (values above a threshold, duplicates, dates due soon), colour scales and data bars, and formula-based rules for custom logic. Good answers mention keeping it restrained so the important cells stand out.
  5. 5

    How would you protect a workbook so others can enter data but not break formulas?

    Why ask it

    Shared sheets are common and fragile.

    What to look for

    Unlock input cells, then protect the sheet so locked formula cells cannot be edited; use data validation to restrict inputs (drop-down lists, number ranges); keep inputs, calculations and outputs in clearly separate areas.
For hiring teams

Before the Excel interviews, see who can do the work

Cohesyve finds and contacts Excel candidates from your job description and tests each one on an AI job simulation of the role, so these questions go to a ranked shortlist. 14-day free trial for 10 candidates, not charged until it ends.

Lookups and Formulas

Intermediate questions that most analyst and finance interviews include.

  1. 6

    What is the difference between VLOOKUP and XLOOKUP?

    Why ask it

    Lookups are the most asked Excel interview topic.

    What to look for

    VLOOKUP searches the first column of a range and can only return values to its right; its default is approximate match, so FALSE or 0 is needed for an exact match, and inserting columns can break a hard-coded column number. XLOOKUP can return a column on either side, matches exactly by default and has an if_not_found argument. XLOOKUP is available in Excel for Microsoft 365, Excel 2024 and Excel 2021 but not in Excel 2016 or 2019, so INDEX/MATCH still matters for older versions.
  2. 7

    How does INDEX/MATCH work, and why do people still use it?

    Why ask it

    Shows deeper understanding of how lookups work.

    What to look for

    MATCH returns the position of a value in a range; INDEX returns the value at a position. Combined, they look up in any direction, survive column inserts, and work in every Excel version. Two-way lookups use MATCH for both row and column.
  3. 8

    How do you handle errors such as #N/A or #DIV/0! in formulas?

    Why ask it

    Clean outputs matter in reports, but hiding errors can hide problems.

    What to look for

    IFERROR or IFNA to return a clear value, XLOOKUP's if_not_found, and checking denominators before dividing. Strong candidates warn that wrapping everything in IFERROR can hide real data problems, so they investigate first.
  4. 9

    What are dynamic array formulas, and what does a #SPILL! error mean?

    Why ask it

    Separates candidates who use current Excel from those who learned an older version.

    What to look for

    A dynamic array formula returns several values that spill into neighbouring cells, sized automatically; functions such as SORT and FILTER work this way. #SPILL! means something is blocking the output range, and clearing the blocking cells fixes it.

Analysis and Reporting

Advanced questions for analyst, finance and operations roles.

  1. 10

    Walk me through building a PivotTable to summarise sales by region and month.

    Why ask it

    PivotTables are the fastest way to summarise data and a common practical test.

    What to look for

    Convert the data to a table first so the source grows with new rows, insert a PivotTable, drag Region to rows, Month (or a grouped date) to columns and Sales to values; set the value field to sum, format numbers, add slicers for filtering, and refresh when data changes. Mentions of grouping dates and showing values as a percentage of total show depth.
  2. 11

    What is Power Query, and when would you use it instead of formulas?

    Why ask it

    Tests whether the candidate can automate repeated data preparation.

    What to look for

    Power Query (known as Get & Transform in Excel) imports data from files, databases, the web and tables in the workbook, then shapes it through recorded steps that run again automatically on refresh. It suits repeated monthly imports, merging queries and reshaping data, where formulas would be manual and fragile.
  3. 12

    How would you build a simple model that someone else can audit?

    Why ask it

    Shows whether the candidate's work can be trusted and handed over.

    What to look for

    Separate inputs, calculations and outputs; colour-code inputs; avoid hard-coded numbers inside formulas; use named ranges or tables; keep formulas short and consistent across rows; add checks (totals that should match); document assumptions on a notes tab.
  4. 13

    You have two lists of customer IDs from different systems. How do you find the ones missing from each?

    Why ask it

    A realistic reconciliation task.

    What to look for

    COUNTIF or XLOOKUP/MATCH against the other list to flag matches, then filter for non-matches; or a Power Query merge of the two lists. Strong candidates first clean both lists (trim spaces, consistent formats, numbers stored as text).
  5. 14

    How would you present a monthly trend to a manager who has two minutes?

    Why ask it

    Excel work ends in a decision; communication matters.

    What to look for

    One clear chart (a line chart for trends), a title that states the finding, minimal clutter, the key number called out and the comparison that matters (versus last month or target). Avoiding 3D charts and crowded legends is a good sign.

Practical and Advanced Questions

Questions that show whether the candidate builds sheets other people can rely on.

  1. 15

    What is the difference between a plain range and an Excel table, and why use tables?

    Why ask it

    Tables make formulas, PivotTables and charts more robust, and experienced users rely on them.

    What to look for

    A table expands automatically as rows are added, so formulas, PivotTables and charts based on it pick up new data; it gives structured references by column name, consistent formatting, filters and a total row. Candidates who build reports on fixed ranges often end up with figures that miss new rows.
  2. 16

    How would you create a drop-down list so people enter consistent values?

    Why ask it

    Inconsistent entries break lookups and summaries.

    What to look for

    Data validation with a list, ideally pointing to a range or table of allowed values so it can be updated in one place, plus an input message and an error alert. Good answers mention protecting the source list.
  3. 17

    What is a macro, and when would you record one rather than use formulas?

    Why ask it

    Shows whether the candidate knows the limits of formulas and of automation.

    What to look for

    A macro records or codes a sequence of actions (VBA in desktop Excel) so a repetitive task, such as formatting a monthly export, runs in one step. Formulas suit calculations that should update live; macros suit repeated actions. Strong candidates mention keeping a backup, testing on a copy and that macro-enabled files need care when shared.
  4. 18

    How do you check a large spreadsheet for errors before you send it?

    Why ask it

    One wrong figure in a shared report damages trust.

    What to look for

    Control totals that should reconcile to a source, spot checks of a few rows by hand, looking for hard-coded numbers inside formulas, checking that formulas are consistent down each column, searching for error values, and asking a colleague to review anything high-stakes.

Running the interview well

  • 1Give a short practical test on a small messy dataset; it reveals more than any verbal question
  • 2Ask candidates to talk through their steps while they work, so you can see method as well as result
  • 3Include one lookup, one PivotTable and one data-cleaning task in any basic Excel test
  • 4Check which Excel version your team uses before marking XLOOKUP or dynamic array answers
  • 5Ask how they check their own work; error-checking habits matter more than knowing every function
  • 6For finance and analyst roles, ask them to explain a model they built and how others used it
  • 7Do not penalise keyboard or mouse preferences; judge accuracy and clarity

Common questions

What Excel questions are asked in an interview?

Most interviews cover cell references, SUMIFS and COUNTIFS, VLOOKUP, XLOOKUP or INDEX/MATCH, PivotTables, removing duplicates and cleaning text, conditional formatting and charts. Analyst and finance roles add Power Query, dynamic arrays and model building.

What is in a basic Excel test for an interview?

Employers design their own tests, so the content varies. Common tasks include sorting and filtering a list, writing SUM, AVERAGE and IF formulas, a lookup between two tables, a simple PivotTable, formatting a table and making a chart. Ask the recruiter which version of Excel the test uses.

How can I prepare for an Excel interview quickly?

Practise on a real dataset: clean it, look up values from a second table, summarise it in a PivotTable and chart the result. Make sure you can explain relative versus absolute references and VLOOKUP versus XLOOKUP in a sentence each.

Should I learn XLOOKUP or VLOOKUP?

Learn both, plus INDEX/MATCH. XLOOKUP is simpler and more flexible in Microsoft 365, Excel 2024 and Excel 2021, but many workplaces still have older files and versions where VLOOKUP and INDEX/MATCH are used.

Do I need VBA or macros for an Excel interview?

Only for roles that mention automation or advanced Excel. For most roles, formulas, PivotTables and Power Query matter more. If the job description lists VBA, expect a question on recording or editing a simple macro.

How should I describe my Excel skills on a CV?

Name the specific skills (PivotTables, XLOOKUP, Power Query, dashboards) and an outcome, such as a report you automated or a reconciliation you built. 'Advanced Excel' on its own tells an employer very little.

For hiring teams

Save these questions for the Excel candidates who can do the work

Cohesyve takes your Excel job description, finds candidates, reaches out to them and runs an AI job simulation of the real work, then returns a ranked shortlist.

1,500+

assessments completed

50%

faster time-to-hire

5 min

from JD to assessment

14-day free trial · 10 candidates · Not charged until the trial ends

For job seekers

Preparing for an Excel interview yourself? Practise on an AI job simulation of the role. 3-day free trial, cancel anytime.

Practise an Excel interview free →

See Cohesyve in action

Free 30-min walkthrough

See it on your role