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.
What these tests check
| Level | What you're asked to do | On this page |
|---|---|---|
| Basic | Get 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 |
| Intermediate | Summarize with conditions: a conversion rate from two counts (COUNTIFS, COUNTIF), an average and a maximum for one group (AVERAGEIFS, MAXIFS) | Exercises 3 to 5 |
| Advanced | A 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.
Each signup ID has three parts: platform, state and a number. In F2, pull the two-letter state out of the ID in A2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Signup ID | Date | Channel | Paid | Seats | State |
| 2 | WEB-TX-1041 | 1/6/2026 | Yes | 12 | ||
| 3 | APP-NY-1042 | 1/14/2026 | Search | No | 3 | |
| 4 | WEB-CA-1043 | 1/27/2026 | No | 5 | ||
| 5 | WEB-NY-1044 | 2/3/2026 | Social | Yes | 8 | |
| 6 | APP-TX-1045 | 2/11/2026 | Search | Yes | 45 | |
| 7 | WEB-FL-1046 | 2/19/2026 | No | 40 | ||
| 8 | APP-CA-1047 | 3/2/2026 | Search | Yes | 15 | |
| 9 | WEB-TX-1048 | 3/10/2026 | No | 6 | ||
| 10 | WEB-FL-1049 | 3/18/2026 | Social | No | 2 |
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.
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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Signup ID | Date | Channel | Paid | Seats | Month |
| 2 | WEB-TX-1041 | 1/6/2026 | Yes | 12 | ||
| 3 | APP-NY-1042 | 1/14/2026 | Search | No | 3 | |
| 4 | WEB-CA-1043 | 1/27/2026 | No | 5 | ||
| 5 | WEB-NY-1044 | 2/3/2026 | Social | Yes | 8 | |
| 6 | APP-TX-1045 | 2/11/2026 | Search | Yes | 45 | |
| 7 | WEB-FL-1046 | 2/19/2026 | No | 40 | ||
| 8 | APP-CA-1047 | 3/2/2026 | Search | Yes | 15 | |
| 9 | WEB-TX-1048 | 3/10/2026 | No | 6 | ||
| 10 | WEB-FL-1049 | 3/18/2026 | Social | No | 2 |
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.
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%).
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Signup ID | Date | Channel | Paid | Seats | Channel | Paid rate | |
| 2 | WEB-TX-1041 | 1/6/2026 | Yes | 12 | ||||
| 3 | APP-NY-1042 | 1/14/2026 | Search | No | 3 | |||
| 4 | WEB-CA-1043 | 1/27/2026 | No | 5 | ||||
| 5 | WEB-NY-1044 | 2/3/2026 | Social | Yes | 8 | |||
| 6 | APP-TX-1045 | 2/11/2026 | Search | Yes | 45 | |||
| 7 | WEB-FL-1046 | 2/19/2026 | No | 40 | ||||
| 8 | APP-CA-1047 | 3/2/2026 | Search | Yes | 15 | |||
| 9 | WEB-TX-1048 | 3/10/2026 | No | 6 | ||||
| 10 | WEB-FL-1049 | 3/18/2026 | Social | No | 2 |
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.
In H2, calculate the average number of seats for the signups that came from the channel in G2 and paid (Yes in column D).
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Signup ID | Date | Channel | Paid | Seats | Channel | Avg seats | |
| 2 | WEB-TX-1041 | 1/6/2026 | Yes | 12 | Search | |||
| 3 | APP-NY-1042 | 1/14/2026 | Search | No | 3 | |||
| 4 | WEB-CA-1043 | 1/27/2026 | No | 5 | ||||
| 5 | WEB-NY-1044 | 2/3/2026 | Social | Yes | 8 | |||
| 6 | APP-TX-1045 | 2/11/2026 | Search | Yes | 45 | |||
| 7 | WEB-FL-1046 | 2/19/2026 | No | 40 | ||||
| 8 | APP-CA-1047 | 3/2/2026 | Search | Yes | 15 | |||
| 9 | WEB-TX-1048 | 3/10/2026 | No | 6 | ||||
| 10 | WEB-FL-1049 | 3/18/2026 | Social | No | 2 |
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.
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.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Signup ID | Date | Channel | Paid | Seats | Channel | Top unpaid | |
| 2 | WEB-TX-1041 | 1/6/2026 | Yes | 12 | ||||
| 3 | APP-NY-1042 | 1/14/2026 | Search | No | 3 | |||
| 4 | WEB-CA-1043 | 1/27/2026 | No | 5 | ||||
| 5 | WEB-NY-1044 | 2/3/2026 | Social | Yes | 8 | |||
| 6 | APP-TX-1045 | 2/11/2026 | Search | Yes | 45 | |||
| 7 | WEB-FL-1046 | 2/19/2026 | No | 40 | ||||
| 8 | APP-CA-1047 | 3/2/2026 | Search | Yes | 15 | |||
| 9 | WEB-TX-1048 | 3/10/2026 | No | 6 | ||||
| 10 | WEB-FL-1049 | 3/18/2026 | Social | No | 2 |
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.
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.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Signup ID | Date | Channel | Paid | Seats | Seats | Signup ID | |
| 2 | WEB-TX-1041 | 1/6/2026 | Yes | 12 | 40 | |||
| 3 | APP-NY-1042 | 1/14/2026 | Search | No | 3 | |||
| 4 | WEB-CA-1043 | 1/27/2026 | No | 5 | ||||
| 5 | WEB-NY-1044 | 2/3/2026 | Social | Yes | 8 | |||
| 6 | APP-TX-1045 | 2/11/2026 | Search | Yes | 45 | |||
| 7 | WEB-FL-1046 | 2/19/2026 | No | 40 | ||||
| 8 | APP-CA-1047 | 3/2/2026 | Search | Yes | 15 | |||
| 9 | WEB-TX-1048 | 3/10/2026 | No | 6 | ||||
| 10 | WEB-FL-1049 | 3/18/2026 | Social | No | 2 |
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.
In G2, list every channel that appears in column C, each one once. One formula: the result fills the cells below by itself.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Signup ID | Date | Channel | Paid | Seats | Channels | |
| 2 | WEB-TX-1041 | 1/6/2026 | Yes | 12 | |||
| 3 | APP-NY-1042 | 1/14/2026 | Search | No | 3 | ||
| 4 | WEB-CA-1043 | 1/27/2026 | No | 5 | |||
| 5 | WEB-NY-1044 | 2/3/2026 | Social | Yes | 8 | ||
| 6 | APP-TX-1045 | 2/11/2026 | Search | Yes | 45 | ||
| 7 | WEB-FL-1046 | 2/19/2026 | No | 40 | |||
| 8 | APP-CA-1047 | 3/2/2026 | Search | Yes | 15 | ||
| 9 | WEB-TX-1048 | 3/10/2026 | No | 6 | |||
| 10 | WEB-FL-1049 | 3/18/2026 | Social | No | 2 |
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.
A channel is typed in G2. In H2, list the signup IDs of every signup from that channel. One formula.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Signup ID | Date | Channel | Paid | Seats | Channel | Signup IDs | |
| 2 | WEB-TX-1041 | 1/6/2026 | Yes | 12 | ||||
| 3 | APP-NY-1042 | 1/14/2026 | Search | No | 3 | |||
| 4 | WEB-CA-1043 | 1/27/2026 | No | 5 | ||||
| 5 | WEB-NY-1044 | 2/3/2026 | Social | Yes | 8 | |||
| 6 | APP-TX-1045 | 2/11/2026 | Search | Yes | 45 | |||
| 7 | WEB-FL-1046 | 2/19/2026 | No | 40 | ||||
| 8 | APP-CA-1047 | 3/2/2026 | Search | Yes | 15 | |||
| 9 | WEB-TX-1048 | 3/10/2026 | No | 6 | ||||
| 10 | WEB-FL-1049 | 3/18/2026 | Social | No | 2 |
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.