What level of Excel do most jobs actually require?

Most non-analyst roles need confident intermediate skills: lookups, SUMIFS, pivot tables, clean data handling and basic charts. Advanced features like Power Query, dynamic arrays and LAMBDA usually appear only for analyst, finance and reporting roles where you build tools other people rely on.

Should I say VLOOKUP or XLOOKUP in an interview?

Mention both. Say you prefer XLOOKUP where the environment supports it, explain why, then note that you use VLOOKUP or INDEX and MATCH when files must open in Excel 2016 or 2019. Showing awareness of compatibility scores higher than naming one function.

Are Excel tests done in real Excel or in a simulator?

Both are common. Many screening platforms use a browser-based simulation with a time limit, while take-home tasks arrive as a real workbook. Simulators often disable shortcuts and autocomplete, so practise typing full formulas without relying on Excel's suggestions.

How long do Excel assessment tests usually take?

Screening tests typically run 20 to 45 minutes with a fixed number of questions or tasks. Take-home dataset exercises are usually scoped to an hour or two. If the timer is short, prioritise finishing every question over perfecting the first one.

What is the fastest way to prepare if my interview is this week?

Spend your time on lookups, SUMIFS and COUNTIFS, pivot tables and data cleaning, in that order. These four cover the majority of test content. Practise on a real dataset rather than reading about functions, because tests measure whether you can produce a working answer.

Excel Interview Questions: What Companies Actually Ask

By the XLsheetAI Team · Updated August 8, 2026 · 11 min read

Read this in Machine translation, opens on Google’s site.

TL;DR

Four topics dominate almost every Excel test: lookups, conditional aggregation (SUMIFS and COUNTIFS), pivot tables, and data cleaning. Prepare those before anything exotic.
Interviewers score your reasoning, not just your answer. Saying why you chose a function beats naming it.
Most candidates fail on habits rather than knowledge: hardcoded numbers, no error handling, and unstated assumptions.
If you only have a week, practise on a real dataset. Tests measure whether you can produce a working answer under time pressure.

How Excel interviews are actually structured

Excel is usually assessed in three stages rather than one. A recruiter or hiring manager asks a few verbal questions to gauge your level, a timed assessment checks whether you can produce answers without help, and a dataset task shows how you structure work when nobody is watching. Each stage rewards something different.

The verbal round is about vocabulary and judgement. You are asked to explain a concept or compare two approaches, and the interviewer listens for whether you understand trade-offs. The timed assessment is about recall and speed, often in a browser-based simulator that disables autocomplete and some shortcuts. The dataset task is the one that carries the most weight for analyst roles, because it exposes how you lay out inputs, handle messy values, and present a result someone else can check.

Beginner questions and what the interviewer is listening for

Beginner questions filter out candidates who have only used Excel as a table for typing. Answer them precisely and briefly. The interviewer is checking that you know what the software is doing underneath, not just which menu to click, so add one sentence of reasoning to each answer rather than a single word.

1. What is the difference between a relative and an absolute reference?

A relative reference like B2 shifts when you copy the formula; an absolute reference like $B$2 stays fixed. Mixed references lock only the row or column, so B$2 keeps row 2 while the column moves. Mention the practical use: locking a tax rate or exchange rate in one input cell so every calculation points to the same place.

2. When would you use SUMIF instead of SUM?

Use SUM to total a range, and SUMIF to total only the rows matching a condition, as in =SUMIF(A2:A100,"North",B2:B100). The detail interviewers listen for is argument order: in SUMIF the range you add comes last, while in SUMIFS it comes first. Getting that backwards is a very common error.

3. What is the difference between COUNT and COUNTA?

COUNT counts cells containing numbers. COUNTA counts every non-empty cell including text, so it is the right choice for counting survey responses or filled-in names. Add that neither counts truly empty cells, but both count a cell containing "" returned by a formula, which surprises people auditing their totals.

4. How do you remove duplicate rows?

Data, then Remove Duplicates, choosing which columns define a duplicate. The point worth making is that this deletes permanently and is not case sensitive. A careful answer mentions working on a copy, or highlighting duplicates with conditional formatting first so you can inspect them before anything is removed.

5. What does Text to Columns do?

It splits one column into several using a delimiter or fixed width, which is how you separate "Smith, John" into two fields. Strong candidates add that it also repairs numbers and dates stored as text: running Text to Columns on a single column and pressing Finish forces Excel to re-evaluate the values.

6. What are freeze panes for?

Freezing keeps headers visible while you scroll a long table, using View then Freeze Panes. The useful nuance is that Excel freezes everything above and left of the selected cell, so selecting B2 freezes both the header row and the first column. It is a small answer that shows you have actually navigated large files.

7. What is the difference between sorting and filtering?

Sorting reorders every row permanently in the sheet; filtering hides rows temporarily without changing their order. Say that sorting a range without including all related columns is a classic way to scramble data, which is exactly why formatting the range as a Table first is safer.

8. What does IFERROR do, and when should you avoid it?

IFERROR(formula, fallback) replaces any error with your fallback value. The mature answer includes the warning: wrapping everything in IFERROR hides genuine problems, so a lookup that silently returns "Not found" can mask a broken data feed. Use it deliberately, not as blanket insurance.