12 Excel tasks from the test round.
Most analyst hiring has one. Messy data, a lookup, a few totals, a ranking.
Try each task on your own sheet first. Then check my formula.
If you can do all 12 without Googling, you're ready for that round.
Save this and practise 3 a day.
Tasks 01-02 · Cleaning
First, fix the messy data.
- 01
Clean names with extra spaces
=PROPER(TRIM(A2))Removes extra spaces, then fixes the case.
- 02
Pull the domain from an email
=TEXTAFTER(A2, "@") =MID(A2, FIND("@", A2) + 1, 100)First one needs Excel 365. Second works in any version.
Tasks 03-04 · Lookup
The lookup every test has.
- 03
Price for a product ID
=XLOOKUP(A2, Products[ID], Products[Price], "Not found")Fourth argument handles missing IDs. No ugly #N/A.
- 04
Lookup with two conditions
=XLOOKUP(1, (Sales[Region]=G2) * (Sales[Month]=H2), Sales[Amount])Multiply the conditions. Only the matching row becomes 1.
Tasks 05-06 · Totals
Totals with conditions.
- 05
Sales for one region in one month
=SUMIFS(Sales[Amount], Sales[Region], "North", Sales[Month], "Jan")Add as many range, criteria pairs as you need.
- 06
Count orders above 5,000
=COUNTIFS(Sales[Amount], ">5000")Put the operator inside the quotes.
Tasks 07-08 · Dynamic arrays
One formula, whole table.
- 07
Unique list of customers
=UNIQUE(Sales[Customer])Spills down on its own. Updates when data changes.
- 08
All rows for one region
=FILTER(Sales, Sales[Region] = "North", "No rows")Third argument shows when nothing matches.
Tasks 09-10 · Ranking
Who is on top.
- 09
Top 5 rows by amount
=TAKE(SORT(Sales, 3, -1), 5)Sort by column 3 (Amount) descending, keep 5 rows.
- 10
Rank each salesperson
=RANK.EQ(C2, $C$2:$C$20, 0)0 means highest value gets rank 1.
Tasks 11-12 · Logic + dates
Buckets and dates.
- 11
Grade scores into A to D
=IFS(B2 >= 90, "A", B2 >= 75, "B", B2 >= 60, "C", TRUE, "D")Checks top to bottom. TRUE catches the rest.
- 12
Working days between two dates
=NETWORKDAYS(A2, B2)Skips Saturdays and Sundays. Add a holiday range as 3rd input.
How to clear the round
Don't
- Type numbers you can reference
- Merge cells in the output
- Leave #N/A on the sheet
Do
- Convert data to a table (Ctrl+T)
- Give every lookup a fallback
- Label each answer clearly
Free PDF
12 tasks they give you in the Excel test round
Download the PDFOpens in Google Drive. No sign-up.