Count entries with stray spaces
How many region labels in A2:A6 contain leading or trailing spaces?
Formula: =SUMPRODUCT((LEN(A2:A6)<>LEN(TRIM(A2:A6)))*1) → 4
Hands-on Excel exercises
Excel Assessment Test Practice
Practice the cleanup and summary tasks common in pre-employment Excel assessments with a messy region list, worked answers, and checks.
Pre-employment assessments are rarely about exotic functions. They tend to hand you data that is a little dirty and ask for something plain: a clean total, a count of problems, a value looked up by name. The dirt is the point. Extra spaces, mixed capitalization, and a missing entry decide whether a formula returns the right number or a quiet zero.
This page trains that situation. The live sheet is a short sales list whose region labels were typed by different people: leading spaces, trailing spaces, upper and lower case. You will build a total that treats those variants as the same region instead of fixing each cell by hand, then use the worked answers to count how many entries have stray spaces, produce a clean label, and look up a value safely.
A good routine for any assessment is to look before you calculate. Scan the headers, check for blanks and spaces, decide what a sensible answer looks like, and only then type the formula. Employers differ, and no page here can predict yours, so treat this as practice for transferable habits. When you want a time limit, the free Sample mock exam has four tasks in eight minutes.
Your turn
Your turn: in F2, total the sales for East. Region labels have stray spaces and mixed case, so clean them inside the formula.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Sales | ||||
| 2 | east | 120 | ||||
| 3 | WEST | 95 | ||||
| 4 | North | 80 | ||||
| 5 | south | 60 | ||||
| 6 | East | 45 |
Not yet solved.
Answer: =SUMPRODUCT((TRIM(PROPER(A2:A6))="East")*B2:B6)
Practice under a time limit
Try the free Sample mock exam with 4 tasks in 8 minutes, or browse the level and interview mock exams included with Pro + Exams.
Related exercises
Open a task in its lesson.
- In C7, calculate how many items were bought in total.
- In C2:C8, write "Pass" if the score is at least the passing score (F1), otherwise "Fail".
- In F2, calculate the total of Emma's deals in Boston.
- In G2:G4, look up the item price.
- In G2:G6, turn the text in column A into numbers (some have spaces at the edges).
- Build a second PivotTable: the average deal amount by city, with the result in F8.
- In F10:F13, find the prepayment discount from the scale in F2:G5 (approximate match on the number of months).
- Fix D3: the average price of coffee.
Worked answers
Try the spreadsheet first, then compare your formula and result.
Clean one label
Return the label in A3 with spaces removed and the first letter capitalized only.
Formula: =PROPER(TRIM(A3)) → West
Look up a value without breaking
Return the sales for North, or 0 if the label is missing.
Formula: =IFERROR(INDEX(B2:B6,MATCH("North",A2:A6,0)),0) → 80
Frequently asked questions
Why not just fix the cells by hand?
On a short list you can, but assessments often reward a formula that survives new rows. Cleaning inside the formula, or in a helper column, keeps the answer correct when data changes.
What does TRIM not remove?
It removes extra spaces but not non-breaking spaces from web pages or other invisible characters. CLEAN handles some of those; check unusual lengths with LEN.
How do I check a cleaned total?
Count the matching rows by eye, add two of them by hand, and compare with the formula. If they disagree, look for a variant spelling.
Do assessments really use messy data?
Many do, because real work data is messy. Expect inconsistent text, blank cells, and numbers stored as text.