Excel practice › Excel interview test › Data analyst

Excel interview questions for data analysts: 8 exercises with answers

These are free Excel practice questions for data analyst interviews, with answers. You solve 8 exercises in a working spreadsheet in your browser, all on one log of trial signups. No download and no sign-up.

They are not any employer's or test vendor's actual questions. They cover the Excel skills that data analyst postings and tests commonly list: cleaning codes and dates, rates and conditional statistics, lookups, and dynamic arrays.

Skip to the 8 exercises ↓

What these tests check

LevelWhat you're asked to doOn this page
BasicGet the data into shape: pull a part out of a code (LEFT, MID, RIGHT), turn a date into a month label (TEXT)Exercises 1 and 2
IntermediateSummarize with conditions: a conversion rate from two counts (COUNTIFS, COUNTIF), an average and a maximum for one group (AVERAGEIFS, MAXIFS)Exercises 3 to 5
AdvancedA lookup to the left (INDEX/MATCH); lists that build themselves (UNIQUE, FILTER)Exercises 6 to 8

Job postings and tests differ. Ask the recruiter which version of Excel the test uses and which level it targets. The version matters here: UNIQUE, FILTER and XLOOKUP exist in Excel 2021 and later and in Microsoft 365, not in older versions.

This page practices formulas. It doesn't cover pivot tables or charts.

Part 1: get the data into shape

The data is made up: nine trial signups for a scheduling app, with the channel each came from, whether the team went on to pay, and how many seats it asked for.

Type the formula in the box and press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in number won't pass. The answer is under each exercise.

Exercise 1Pull a code out of an ID

Each signup ID has three parts: platform, state and a number. In F2, pull the two-letter state out of the ID in A2.

F2fx
ABCDEF
1Signup IDDateChannelPaidSeatsState
2WEB-TX-10411/6/2026EmailYes12
3APP-NY-10421/14/2026SearchNo3
4WEB-CA-10431/27/2026EmailNo5
5WEB-NY-10442/3/2026SocialYes8
6APP-TX-10452/11/2026SearchYes45
7WEB-FL-10462/19/2026EmailNo40
8APP-CA-10473/2/2026SearchYes15
9WEB-TX-10483/10/2026EmailNo6
10WEB-FL-10493/18/2026SocialNo2
Show the answer

=MID(A2,5,2) gives TX.

MID takes the text, the position of the first character to keep, and how many to keep. LEFT(A2,3) returns the platform and RIGHT(A2,4) the number. Hyphens count as characters, and this only works because every ID has the same layout.

Exercise 2Month label from a date

To group signups by month you need a label. In F2, turn the date in B2 into text like Mar 2026: the three-letter month, a space, the four-digit year.

F2fx
ABCDEF
1Signup IDDateChannelPaidSeatsMonth
2WEB-TX-10411/6/2026EmailYes12
3APP-NY-10421/14/2026SearchNo3
4WEB-CA-10431/27/2026EmailNo5
5WEB-NY-10442/3/2026SocialYes8
6APP-TX-10452/11/2026SearchYes45
7WEB-FL-10462/19/2026EmailNo40
8APP-CA-10473/2/2026SearchYes15
9WEB-TX-10483/10/2026EmailNo6
10WEB-FL-10493/18/2026SocialNo2
Show the answer

=TEXT(B2,"mmm yyyy") gives Jan 2026.

TEXT turns a date into text in the format you give it: mmm is the short month name and yyyy the year. The result is text, which is what you want for a label. It no longer sorts as a date, so keep the real date column next to it.

Part 2: summarize with conditions

Three questions a manager would ask about this log. Each one is a count, an average or a maximum over the rows that meet two conditions.

Exercise 3Conversion rate for one channel

A channel is typed in G2. In H2, calculate its paid conversion rate: the signups from that channel with Yes in the Paid column, divided by all the signups from that channel. The result is a decimal (0.4 would mean 40%).

H2fx
ABCDEFGH
1Signup IDDateChannelPaidSeatsChannelPaid rate
2WEB-TX-10411/6/2026EmailYes12Email
3APP-NY-10421/14/2026SearchNo3
4WEB-CA-10431/27/2026EmailNo5
5WEB-NY-10442/3/2026SocialYes8
6APP-TX-10452/11/2026SearchYes45
7WEB-FL-10462/19/2026EmailNo40
8APP-CA-10473/2/2026SearchYes15
9WEB-TX-10483/10/2026EmailNo6
10WEB-FL-10493/18/2026SocialNo2
Show the answer

=COUNTIFS(C2:C10,G2,D2:D10,"Yes")/COUNTIF(C2:C10,G2) gives 0.25.

A rate is one count divided by another. COUNTIFS counts the rows that meet both conditions, and COUNTIF counts all the channel's rows. The mistake to avoid is the bottom of the fraction: dividing by all nine signups answers a different question.

Exercise 4Average with two conditions

In H2, calculate the average number of seats for the signups that came from the channel in G2 and paid (Yes in column D).

H2fx
ABCDEFGH
1Signup IDDateChannelPaidSeatsChannelAvg seats
2WEB-TX-10411/6/2026EmailYes12Search
3APP-NY-10421/14/2026SearchNo3
4WEB-CA-10431/27/2026EmailNo5
5WEB-NY-10442/3/2026SocialYes8
6APP-TX-10452/11/2026SearchYes45
7WEB-FL-10462/19/2026EmailNo40
8APP-CA-10473/2/2026SearchYes15
9WEB-TX-10483/10/2026EmailNo6
10WEB-FL-10493/18/2026SocialNo2
Show the answer

=AVERAGEIFS(E2:E10,C2:C10,G2,D2:D10,"Yes") gives 30.

AVERAGEIFS starts with the range to average, then takes pairs of range and condition, like SUMIFS. Watch the order: in AVERAGEIF the range to average comes last, in AVERAGEIFS it comes first.

Exercise 5Largest value in a group

Sales wants to call the biggest team that hasn't paid yet. In H2, return the largest number of seats among the signups from the channel in G2 with No in the Paid column.

H2fx
ABCDEFGH
1Signup IDDateChannelPaidSeatsChannelTop unpaid
2WEB-TX-10411/6/2026EmailYes12Email
3APP-NY-10421/14/2026SearchNo3
4WEB-CA-10431/27/2026EmailNo5
5WEB-NY-10442/3/2026SocialYes8
6APP-TX-10452/11/2026SearchYes45
7WEB-FL-10462/19/2026EmailNo40
8APP-CA-10473/2/2026SearchYes15
9WEB-TX-10483/10/2026EmailNo6
10WEB-FL-10493/18/2026SocialNo2
Show the answer

=MAXIFS(E2:E10,C2:C10,G2,D2:D10,"No") gives 40.

MAXIFS works like SUMIFS: the range to take the maximum from, then pairs of range and condition. Plain MAX looks at every row, and here the largest team in the log (45 seats) has already paid.

Part 3: look up and list

The last three return text instead of a number: one ID, then whole lists. Exercises 7 and 8 use dynamic arrays, where one formula fills several cells.

Exercise 6Look up to the left with INDEX/MATCH

The team from the last exercise has 40 seats, typed in G2. In H2, return its signup ID. The ID column is to the left of the seats, so VLOOKUP can't do it.

H2fx
ABCDEFGH
1Signup IDDateChannelPaidSeatsSeatsSignup ID
2WEB-TX-10411/6/2026EmailYes1240
3APP-NY-10421/14/2026SearchNo3
4WEB-CA-10431/27/2026EmailNo5
5WEB-NY-10442/3/2026SocialYes8
6APP-TX-10452/11/2026SearchYes45
7WEB-FL-10462/19/2026EmailNo40
8APP-CA-10473/2/2026SearchYes15
9WEB-TX-10483/10/2026EmailNo6
10WEB-FL-10493/18/2026SocialNo2
Show the answer

=INDEX(A2:A10,MATCH(G2,E2:E10,0)) gives WEB-FL-1046.

MATCH finds the position of G2 in the seats column (the 0 means exact match) and INDEX returns the ID at that position in column A. The two ranges have to cover the same rows. If two signups had the same number of seats, MATCH would stop at the first. =XLOOKUP(G2,E2:E10,A2:A10) returns the same ID where XLOOKUP exists. More on INDEX and MATCH.

Exercise 7A list of the distinct channels

In G2, list every channel that appears in column C, each one once. One formula: the result fills the cells below by itself.

G2fx
ABCDEFG
1Signup IDDateChannelPaidSeatsChannels
2WEB-TX-10411/6/2026EmailYes12
3APP-NY-10421/14/2026SearchNo3
4WEB-CA-10431/27/2026EmailNo5
5WEB-NY-10442/3/2026SocialYes8
6APP-TX-10452/11/2026SearchYes45
7WEB-FL-10462/19/2026EmailNo40
8APP-CA-10473/2/2026SearchYes15
9WEB-TX-10483/10/2026EmailNo6
10WEB-FL-10493/18/2026SocialNo2
Show the answer

=UNIQUE(C2:C10) gives Email (3 rows, starting with Email).

UNIQUE returns each distinct value once, and the result spills into the cells below. Wrap it in SORT when the list has to be in A to Z order. Dynamic array functions exist in Excel 2021 and later and in Microsoft 365; on an older version, the usual routes are Remove Duplicates or a pivot table.

Exercise 8Filter the rows of one channel

A channel is typed in G2. In H2, list the signup IDs of every signup from that channel. One formula.

H2fx
ABCDEFGH
1Signup IDDateChannelPaidSeatsChannelSignup IDs
2WEB-TX-10411/6/2026EmailYes12Email
3APP-NY-10421/14/2026SearchNo3
4WEB-CA-10431/27/2026EmailNo5
5WEB-NY-10442/3/2026SocialYes8
6APP-TX-10452/11/2026SearchYes45
7WEB-FL-10462/19/2026EmailNo40
8APP-CA-10473/2/2026SearchYes15
9WEB-TX-10483/10/2026EmailNo6
10WEB-FL-10493/18/2026SocialNo2
Show the answer

=FILTER(A2:A10,C2:C10=G2) gives WEB-TX-1041 (4 rows, starting with WEB-TX-1041).

FILTER takes the range to return and a condition that gives TRUE or FALSE for each row, and keeps the rows where it is TRUE. For two conditions, multiply them: (C2:C10=G2)*(D2:D10="No"). When no row matches, FILTER gives #CALC! unless you add a third argument with what to show instead.

How to use the time you have

If the interview is tomorrow. Do the 8 exercises on this page. For each one you couldn't solve, open the answer, read why it works, then empty the box and type the formula yourself. Then take the free 10-question Excel test, which ends with a level and the first topic you missed. If there is time left, the general interview test has 10 more exercises with answers.

If you have a few days. Work on what you missed. Lesson 1 and lesson 2 (the basics and IF) are free, and so are the function pages linked from each answer. The lessons on SUMIFS, COUNTIFS and AVERAGEIFS, INDEX/MATCH, dates and TEXT, pivot tables and FILTER, SORT and UNIQUE are part of Pro: $29 once for all three tracks, or $19 for the Excel track alone. No subscription, and a 14-day refund.

If the role also asks for SQL. There is a free SQL test on this site, built the same way: you write queries against a small database in the browser.

Questions people ask

Are these Excel interview questions for data analysts free, with answers?

Yes. Every exercise has a Show the answer line under it, with the formula, its result and a short explanation. The page is free and there is nothing to sign up for.

Can I practice Excel for a data analyst interview without installing it?

Yes. The spreadsheet on this page runs in your browser. The formulas are the same ones Excel uses, dynamic arrays included, so what you type here is what you would type in Excel. One difference: the sheet shows plain numbers, with no percentage formatting.

Are these the questions a data analyst Excel test will ask?

No. These are practice questions written for this site, on made-up data. They are not a copy of any employer's or test company's test. Ask the recruiter which version of Excel the test uses, which level it targets, whether it is timed, and whether it includes pivot tables or SQL, which this page does not practice.

How long do these 8 exercises take?

There are 8 exercises and no timer, so they take as long as you need. If your real test is timed, do the page once to learn the formulas, then do it again without opening the answers.

Keep going

Ten questions in a real spreadsheet, from SUM to INDEX/MATCH, on different data. You get a level at the end and the first topic you missed. Free, no sign-up.

Related

Excel test for job interviews, with answersExcel test for accounting interviewsCOUNTIFS function, with live examplesINDEX MATCH, with live examplesFILTER function, with live examplesExcel FILTER, SORT, UNIQUE practice (Pro)Excel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Last checked November 2026.