Excel interview questions and practice tasks

The questions below are typical topics, not questions from any specific company. Answers are kept short on purpose: in an interview, a clear two-sentence answer plus an example beats a lecture.

Basic Excel interview questions

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

A relative reference like A2 shifts when you copy the formula. An absolute reference like $A$2 stays fixed. You need it when every row should point to the same cell, such as a tax rate. Mixed references like $A2 lock only the column. More in absolute references.

What is the difference between COUNT and COUNTA?

COUNT counts cells with numbers. COUNTA counts every cell that is not empty, text included. COUNTBLANK counts the empty ones. See COUNT and COUNTA.

How does the IF function work?

IF tests a condition and returns one value if it is true and another if it is false, for example =IF(C2>=1000,"Target met","Below target"). For several conditions in a row, IFS is easier to read than nested IFs. Details in the IF lesson.

Why should you use an Excel table instead of a plain range?

A table grows automatically when you add rows, so formulas, charts and PivotTables pick up new data. It also gives you filter buttons, banded rows and readable structured references. See creating a table.

Try it: an IF task

Task In D2, write a formula that shows "Target met" if the revenue in B2 is at least the target in C2, and "Target missed" otherwise.

	A	B	C	D
1	Branch	Revenue $	Target $	Status
2	Berlin	48200	45000
3	Hamburg	39800	42000
4	Munich	51500	50000
5	Cologne	36900	40000
6	Leipzig	30100	30000

Intermediate questions

How does VLOOKUP work, and what are its limits?

VLOOKUP searches the first column of a range and returns a value from a column to the right: =VLOOKUP(E2,A2:C9,3,FALSE). The last argument should be FALSE for an exact match, otherwise unsorted data returns wrong results. It cannot look to the left and breaks if someone inserts a column. Practice in the VLOOKUP lesson.

What does XLOOKUP do better?

XLOOKUP takes a lookup column and a return column separately, so it can look left, matches exactly by default and has a built-in "not found" value. It is available in Excel 2021 and Microsoft 365. In older versions, INDEX and MATCH does the same job.

How do you sum with conditions?

SUMIF adds values that meet one condition, SUMIFS handles several, for example revenue for one product in one region. COUNTIF and COUNTIFS count the same way. See COUNTIF and SUMIFS.

How do you handle #N/A and other errors?

First find the cause: #N/A usually means a lookup value was not found, often because of extra spaces or numbers stored as text. Once the cause is clear, IFERROR can show a friendly value instead. Explaining the cause before hiding the error is the answer interviewers want. See error values and IFERROR.

Try it: a lookup task

Task In F2, use VLOOKUP to find the price of the item whose number is in E2.

	A	B	C	D	E	F
1	Item no.	Item	Price $		Lookup	Price
2	A-101	Ballpoint pen	4.9		A-104
3	A-104	Stapler	19.9
4	A-103	Notepad	7.2
5	A-102	Binder	12.5
6	A-105	Eraser	3.4

Advanced questions

What is a PivotTable and when do you use it?

A PivotTable summarizes a long list in a few clicks, for example revenue by region and month, without writing formulas. You drag fields into rows, columns and values and can change the view instantly. It is the standard answer for "summarize this data". See PivotTables.

How do you remove or find duplicates?

Remove Duplicates on the Data tab deletes them in place, so work on a copy. To only highlight them, use conditional formatting or COUNTIF. In Microsoft 365 and Excel 2021, UNIQUE returns a clean list with a formula. See removing duplicates.

How would you highlight rows that meet a condition?

Use conditional formatting with a formula rule such as =$C2>1000. The dollar sign before the column keeps every cell in the row looking at column C. See conditional formatting.

What are dynamic array functions?

FILTER, SORT and UNIQUE return a whole range of results that spills into neighboring cells and updates when the data changes. They exist in Microsoft 365 and Excel 2021. See FILTER, SORT and UNIQUE.

How do you stop people from entering wrong data?

With data validation, for example a drop-down list of allowed values or a rule that only accepts whole numbers in a range. See drop-down lists.

Try it: summing with two conditions

Task In E2, use SUMIFS to calculate the coffee revenue in the North region.

	A	B	C	D	E
1	Region	Product	Revenue $		Revenue North, Coffee
2	North	Coffee	1250
3	South	Coffee	980
4	North	Tea	640
5	West	Coffee	1100
6	North	Coffee	870
7	South	Tea	520
8	North	Coffee	410

Practical tasks you may get

Many interviews include a short hands-on part. Typical tasks look like this:

• Calculate a total and each row's share of it, filled down with one formula.
• Pull a price or department into a list from a second table.
• Count or sum orders by region, product or month.
• Sort and filter a list, then summarize it with a PivotTable or a chart.
• Combine first and last names, or split a code into parts with text functions.
• Clean a messy export: spaces, duplicates, numbers stored as text.

Interviewers often watch how you work, not just the result. Using formulas instead of typed numbers, locking references correctly and moving with keyboard shortcuts all show that you use Excel every day.

Practice under real conditions

Our free Excel assessment test has twelve hands-on tasks in 20 minutes with no hints, much like a pre-employment test. It shows which topics are solid and links each weak spot to its lesson. If you are not sure of your level at all, start with the shorter Excel skills test.

Frequently asked questions