Skip to content
TryHired.
Interview prep · Technical · Python 3.12.10

Excel interview questions: formulas and skills test practice

The Excel functions and features interviews and pre-employment skills tests check, with example formulas and results checked in Python.

Updated Nov 12, 2026 · Examples run on Python 3.12.10 · questions: TryHired
Quick answer

Excel interviews and skills tests check lookups (XLOOKUP, VLOOKUP, INDEX and MATCH), conditional sums and counts (SUMIFS, COUNTIFS), cell references (relative and absolute), pivot tables, logical and text functions, dynamic arrays (FILTER, UNIQUE, SORT), data cleaning and Power Query. Below are 18 questions with example formulas; where a result is quoted, the same calculation was run in Python on the same sample values.

Sample data

The sample table the formulas use

Advice
Sample table used below (A1:D7):
Region | Rep  | Product | Sales
West   | Ava  | Laptop  | 1200
East   | Ben  | Monitor | 300
West   | Cara | Monitor | 450
East   | Ava  | Laptop  | 1100
West   | Ben  | Laptop  | 900
East   | Cara | Mouse   | 40
Basic

Basic excel interview questions

Advice

Basic1. What is the difference between XLOOKUP and VLOOKUP?

VLOOKUP searches the first column of a range and returns a value from a column number to the right, defaults to an approximate match unless you pass FALSE, and breaks if columns are inserted. XLOOKUP takes separate lookup and return ranges, can look left, defaults to an exact match, and has a built-in if-not-found value.

Excel formula · Example (not executed)
=VLOOKUP("Cara", B2:D7, 3, FALSE)
=XLOOKUP("Cara", B2:B7, D2:D7, "not found")

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

A relative reference (A1) shifts when you copy the formula. An absolute reference ($A$1) stays fixed. A mixed reference fixes only the column ($A1) or only the row (A$1), which is what you need for multiplication tables and rate grids. F4 cycles through the options.

Basic3. How do SUMIFS and COUNTIFS work?

SUMIFS adds values that meet one or more conditions; COUNTIFS counts rows that meet them. The sum range comes first in SUMIFS, followed by pairs of criteria range and criteria.

Excel formula · Example (not executed)
=SUMIFS(D2:D7, A2:A7, "West", C2:C7, "Laptop")
=COUNTIFS(A2:A7, "East", D2:D7, ">=300")

Basic4. What results do those SUMIFS and COUNTIFS formulas give on the sample table?

West laptops are Ava's 1,200 and Ben's 900, so SUMIFS returns 2,100. East rows with sales of at least 300 are Ben's monitor and Ava's laptop, so COUNTIFS returns 2. The same calculation in Python:

Python
rows = [('West', 'Ava', 'Laptop', 1200), ('East', 'Ben', 'Monitor', 300), ('West', 'Cara', 'Monitor', 450),
        ('East', 'Ava', 'Laptop', 1100), ('West', 'Ben', 'Laptop', 900), ('East', 'Cara', 'Mouse', 40)]
print('SUMIFS  =', sum(s for r, _, p, s in rows if r == 'West' and p == 'Laptop'))
print('COUNTIFS=', sum(1 for r, _, _, s in rows if r == 'East' and s >= 300))
Output (Python 3.12.10)
SUMIFS  = 2100
COUNTIFS= 2

Basic5. What is a pivot table?

A pivot table summarizes a table by dragging fields into rows, columns, values and filters: total sales by region and product, for example, without writing formulas. Values can be summed, counted or shown as a percentage of a total, and the pivot refreshes when the source changes.

Python
from collections import defaultdict
rows = [('West', 'Laptop', 1200), ('East', 'Monitor', 300), ('West', 'Monitor', 450), ('East', 'Laptop', 1100), ('West', 'Laptop', 900), ('East', 'Mouse', 40)]
pivot = defaultdict(int)
for region, product, sales in rows: pivot[(region, product)] += sales
for key in sorted(pivot): print(key, pivot[key])
Output (Python 3.12.10)
('East', 'Laptop') 1100
('East', 'Monitor') 300
('East', 'Mouse') 40
('West', 'Laptop') 2100
('West', 'Monitor') 450

Basic6. How do IF, IFS and IFERROR differ?

IF returns one value when a condition is true and another when false. IFS tests several conditions in order and returns the value for the first true one, avoiding nested IFs. IFERROR returns a fallback when a formula produces an error, such as a failed lookup.

Excel formula · Example (not executed)
=IF(D2>=1000, "High", "Normal")
=IFS(D2>=1000, "High", D2>=400, "Mid", TRUE, "Low")
=IFERROR(XLOOKUP("Dana", B2:B7, D2:D7), 0)

Basic7. Which text functions do you use to clean data?

TRIM removes extra spaces, PROPER, UPPER and LOWER fix capitalization, LEFT, RIGHT and MID extract characters, TEXTBEFORE and TEXTAFTER split around a delimiter, and TEXTJOIN combines values with a separator.

Excel formula · Example (not executed)
=TRIM("  Ava   Lee ")
=TEXTBEFORE("ava@example.com", "@")
=TEXTJOIN(", ", TRUE, B2:B4)
Intermediate

Intermediate excel interview questions

Advice

Intermediate8. How does INDEX with MATCH work?

MATCH returns the position of a value in a range, and INDEX returns the value at a position in another range. Together they do a lookup in any direction and survive inserted columns, which made them the standard before XLOOKUP.

Excel formula · Example (not executed)
=INDEX(D2:D7, MATCH("Cara", B2:B7, 0))

Intermediate9. What do FILTER, UNIQUE and SORT do?

They are dynamic array functions: one formula returns many results that spill into neighboring cells. FILTER returns rows that meet a condition, UNIQUE returns distinct values, and SORT orders a range; they combine, for example a sorted list of reps with West sales.

Excel formula · Example (not executed)
=SORT(UNIQUE(FILTER(B2:B7, A2:A7="West")))

Intermediate10. What does that combined formula return on the sample table?

West rows belong to Ava, Cara and Ben, so the formula spills the three names in alphabetical order. The same logic in Python:

Python
rows = [('West', 'Ava'), ('East', 'Ben'), ('West', 'Cara'), ('East', 'Ava'), ('West', 'Ben'), ('East', 'Cara')]
print(sorted({rep for region, rep in rows if region == 'West'}))
Output (Python 3.12.10)
['Ava', 'Ben', 'Cara']

Intermediate11. How do you remove duplicates?

Use Data > Remove Duplicates on a copy of the data, choosing which columns define a duplicate, or use UNIQUE to produce a deduplicated list while keeping the source intact. Conditional formatting can highlight duplicates first so you can check them.

Intermediate12. What is conditional formatting used for?

It formats cells automatically based on their values: highlight values above a target, color scales, data bars, duplicates, or a custom formula such as highlighting a whole row when the status column says Overdue.

Intermediate13. What is data validation?

Data validation restricts what can be typed into a cell, such as a drop-down list of allowed values, whole numbers in a range, or dates after today, with an input message and an error alert. It keeps shared spreadsheets consistent.

Intermediate14. What is Power Query in Excel?

Power Query (Get and Transform Data) imports data from files, folders, databases and the web, and records cleaning steps (remove columns, split, merge, unpivot, change types) that rerun with one refresh. It replaces repetitive manual cleanup.

Advanced

Advanced excel interview questions

Advice

Advanced15. How do Goal Seek and scenarios help with what-if analysis?

Goal Seek changes one input until a formula reaches a target value, such as the price needed for a target margin. Scenario Manager stores sets of inputs to compare, and data tables show a formula's result across a range of one or two inputs.

Advanced16. What are named ranges and structured references?

A named range gives a cell or range a readable name (TaxRate) to use in formulas. Formatting data as a table enables structured references such as Sales[Amount], which expand automatically as rows are added, so formulas and pivots do not miss new data.

Advanced17. What is the difference between XLOOKUP's exact, approximate and wildcard matches?

The fifth argument sets the match mode: 0 is exact (the default), -1 returns the next smaller item and 1 the next larger, which suits price bands and commission tiers, and 2 allows wildcards (* and ?). The sixth argument sets the search direction, such as last-to-first.

Excel formula · Example (not executed)
=XLOOKUP(7500, {0;1000;5000;20000}, {"0%";"5%";"10%";"15%"}, , -1)

Advanced18. Which discount band does that approximate-match formula return?

7,500 is between 5,000 and 20,000, so match mode -1 (the next smaller item) returns the row for 5,000 and the formula returns 10%. The same lookup in Python:

Python
import bisect
thresholds = [0, 1000, 5000, 20000]
rates = ['0%', '5%', '10%', '15%']
print(rates[bisect.bisect_right(thresholds, 7500) - 1])
Output (Python 3.12.10)
10%

Advanced19. How do you prepare for an Excel skills test?

Find out whether the test is in real Excel or a simulator and which version, practice lookups, SUMIFS, pivots, charts, sorting and filtering, and formatting under time pressure, and learn shortcuts (Ctrl+Arrow to jump, Ctrl+Shift+L for filters, Alt+= for AutoSum on Windows).

Practice

How to practice with this page

Advice
  • Recreate each formula on the sample table in a blank workbook.
  • Time yourself on a practice skills test: speed with shortcuts matters.
  • Build one pivot table and one chart from raw data without help.
  • Learn to clean a messy column with TRIM, TEXT functions or Power Query.
Next

Related interview prep

Disclosure: TryHired makes HIRED, a paid job-search program, so we have a conflict of interest when we mention it. This question bank is free, with no TryHired signup.

FAQ

Questions people ask

What Excel skills are tested in job interviews?
Lookups (XLOOKUP, VLOOKUP, INDEX and MATCH), SUMIFS and COUNTIFS, cell references, pivot tables, IF logic, text cleaning, charts, conditional formatting and, for analyst roles, Power Query and dynamic arrays.
What is on a pre-employment Excel test?
Usually tasks in a simulated or real workbook: formatting, sorting and filtering, basic formulas, lookups, pivot tables and charts, often timed. Ask the recruiter for the provider and level.
Is VLOOKUP still asked if XLOOKUP exists?
Yes. Many workplaces use older versions or older files, so know both and why XLOOKUP is safer.
How do I show Excel skills on a resume?
Name the functions and features you used and what they achieved, such as built a pivot-based weekly report that replaced manual counts. See the TryHired resume skills guides.
Is Excel enough for a data analyst interview?
It helps, but analyst interviews usually add SQL and a BI tool. See the SQL and Power BI interview questions.
Method

How this page is built

TryHired wrote these questions and answers. Every Python block on this page was executed with Python 3.12.10 when the page was built, and the output shown is the real output; the build stops if any example fails. Blocks marked Example (not executed) are configuration, commands or formulas that cannot run on the build machine: no output is claimed for them. The question list is general practice: it does not predict what a particular employer will ask.

What this page can't tell you
  • Interviewers ask different questions for different roles and levels.
  • Answers are short explanations, not complete references.
  • XLOOKUP and dynamic arrays need Excel for Microsoft 365 or Excel 2021 or later; older versions use VLOOKUP and INDEX/MATCH.
  • Skills tests differ by employer and test provider.
Sources
  1. Microsoft Support, XLOOKUP function: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
  2. Microsoft Support, SUMIFS function: https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b
  3. Microsoft Support, dynamic array formulas: https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531
  4. Microsoft Support, overview of PivotTables: https://support.microsoft.com/en-us/office/overview-of-pivottables-and-pivotcharts-527c8fa3-02c0-445a-a2db-7794676bce96