Practice tests ยท By job role
Excel test for data analysts
Conditional counts and averages, cleaning messy text, medians and ranks, de-duplicating and joining tables are the skills data analysts are commonly asked to show.
Skills commonly tested for data analysts
- Counting and averaging with conditions. COUNTIFS, SUMIFS and AVERAGEIFS with two or more criteria, built from a sentence like "orders over $100 from the West".
- Cleaning data. Stray spaces, wrong capitals and numbers stored as text, using TRIM, PROPER, VALUE and SUBSTITUTE before any analysis.
- Summary statistics. Median, rank, percent of total and top-N lists, and knowing when the median says more than the mean.
- Distinct lists and joins. Listing values once with UNIQUE or Remove Duplicates, and bringing a field across from another table with a lookup.
- Pivot tables. Summarizing a table by category, which many analyst tasks expect. Our simulator does not build them yet, so rehearse in Excel.
- Reading and handling errors. Knowing why a lookup returns #N/A or a division returns #DIV/0!, and handling each on purpose.
8 sample tasks with answers
1. Count the orders over $100 from the West region.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Amount | ||
| 2 | West | 120 | 2 | |
| 3 | East | 300 | ||
| 4 | West | 80 | ||
| 5 | West | 250 |
COUNTIFS takes pairs of range and condition. A2:A5 must be West and B2:B5 must be above 100. West has 120, 80 and 250, and two of those pass.
2. Find the average order value for the West region.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Amount | ||
| 2 | West | 120 | 150 | |
| 3 | East | 300 | ||
| 4 | West | 80 | ||
| 5 | West | 250 |
AVERAGEIF averages B2:B5 only where column A says West: 120, 80 and 250 add to 450, and 450 divided by 3 is 150.
3. Report the median order value.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Amount | ||
| 2 | 1 | 20 | 30 | |
| 3 | 2 | 25 | ||
| 4 | 3 | 30 | ||
| 5 | 4 | 35 | ||
| 6 | 5 | 400 |
The five amounts sort to 20, 25, 30, 35 and 400, and the middle one is 30. The average is 102, pulled up by one large order, which is why the median is the better description of a typical order.
4. Clean the name in A2: no extra spaces, proper capitals.
| A | B | |
|---|---|---|
| 1 | Name | |
| 2 | ana RUIZ | Ana Ruiz |
TRIM removes the leading and trailing spaces and reduces the double space between the words to one. PROPER then capitalizes the first letter of each word and lowercases the rest.
5. Return the second-highest revenue.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Revenue | ||
| 2 | East | 300 | 500 | |
| 3 | West | 500 | ||
| 4 | North | 700 | ||
| 5 | South | 450 |
LARGE takes the range and the position. Position 2 is the second biggest of the four values, which is 500.
6. List each customer once.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | ||
| 2 | Ana | Ana | |
| 3 | Kim | Kim | |
| 4 | Ana | Raj | |
| 5 | Raj | ||
| 6 | Kim |
UNIQUE returns the distinct names in the order they first appear. The formula sits in C2 and spills down, so C3 and C4 fill themselves. Microsoft 365 and Excel 2021 have it, and older versions need Remove Duplicates.
7. Bring the region across from the Customers sheet by ID, and show Not found when the ID is missing.
| A | B | |
|---|---|---|
| 1 | ID | Region |
| 2 | 102 | West |
| A | B | |
|---|---|---|
| 1 | ID | Region |
| 2 | 101 | East |
| 3 | 102 | West |
| 4 | 103 | North |
XLOOKUP searches the ID column of the other sheet and returns the matching cell from the region column, so the answer column can sit anywhere. The fourth argument is the text for a miss.
8. Show each region's share of total sales, as a whole-number percent.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | |
| 2 | East | 300 | 30% |
| 3 | West | 450 | |
| 4 | North | 150 | |
| 5 | South | 100 |
East is 300 of 1,000 in total. The total range is locked with dollar signs so it stays the same when the formula is filled down. The 0% format displays 0.3 as 30%.
How to prepare in a week
- Day 1. Conditional counts, sums and averages with one criterion, then two. Write each formula from a sentence and check it by filtering the data by hand.
- Day 2. Cleaning: TRIM, PROPER, VALUE and SUBSTITUTE on a deliberately messy column. Learn to spot numbers stored as text by their left alignment.
- Day 3. Statistics: median against mean, LARGE and SMALL for top-N lists, RANK, and percent of total with a locked total cell.
- Day 4. Lookups: VLOOKUP, INDEX with MATCH and XLOOKUP across two sheets, including the not-found message.
- Day 5. Pivot tables in Excel itself: totals and counts by one and two fields, then check one figure per table with SUMIFS.
- Day 6. Errors and edge cases: #N/A from stray spaces, #DIV/0! from empty groups, and IFNA or IF to handle each on purpose.
- Day 7. Timed mixed set. Review each miss by writing down which skill it was, and drill the weakest one with fresh data.
Which of our practice fits
Analyst-style tests lean on the Analyst track for the lookup, statistical and dynamic-array material, with Everyday Excel underneath it for conditional totals and cleaning. The free 5-item sampler shows you the style of item before you commit.
Tasks that require a pivot table cannot be practiced in our grid yet, so give those their own session in Excel and use the SUMIFS formulas here to verify your figures.
Function guides for this role
Questions
- What Excel skills do data analyst tests cover?
- They vary by employer. Conditional functions, cleaning text, lookups, pivot tables and simple statistics are all worth practicing. Check the job description for hints, such as SQL or Power Query, which signal a heavier test.
- Are pivot tables required?
- Know both. SUMIFS and COUNTIFS answer a summary question when you can name the condition; a task that asks for a pivot table expects you to build one.
- Do I need Microsoft 365 functions such as UNIQUE and XLOOKUP?
- Learn them, but also know the older routes: Remove Duplicates for UNIQUE, and INDEX with MATCH or VLOOKUP for XLOOKUP. A test may run in an older Excel.
- Should I use the mean or the median?
- When a few extreme values pull the average, report the median. Say so in a note if the task allows one.