Cellwise Hire · Data and reporting preset
Excel test for data analysts
Two workbooks that look like an analyst's week: join orders to a price list, deal with keys that do not match, apply a tier table, and debug a colleague's workbook where the formulas return the wrong answer without an error. Every answer is checked on hidden versions of the data.
Create a free data analyst testSee a sample task
Free for your first 3 candidates. The Data and reporting preset is preselected for you.
The test at a glance
- Workbooks
- Orders and price list + Rep bonus sheet
- Tasks
- 8 hands-on tasks at the level you pick
- Levels
- Basic, Intermediate or Advanced
- Time
- You choose; 20 to 40 minutes suits most
- Grading
- By result, on the visible sheet and 2 hidden versions
Good for: Reporting analysts, Junior data analysts, Business and operations analysts, Finance and commercial analysts who work mostly in Excel.
What data analysts actually do in Excel
Analysts spend much of their Excel time joining tables, summarizing them and checking that the result is right. The hard part is rarely writing a formula. It is noticing that one is wrong when nothing on screen says so.
An approximate-match lookup on unsorted data returns a name, just not the right one. COUNTIF with a range as its criterion returns 0 without complaint. A good analyst catches those before the numbers go into a deck.
- Joining two tables by key with XLOOKUP, VLOOKUP or INDEX and MATCH
- Finding keys that are missing from the lookup table, and counting them
- Approximate-match lookups into tier and rate tables
- Aggregating by key, including a SUMIF over a list of keys
- Debugging other people's formulas: match types, argument order, row-by-row comparisons
What the test checks
The Data and reporting preset uses two workbooks: Orders and price list (customer orders on the left, the price list on the right. The order lines only have a SKU code and a quantity) and Rep bonus sheet (a quarterly sales and bonus sheet a colleague built. Several of its formulas are broken: find what is wrong and fix them). Here is what that covers for data analysts, with the real task names and the level each one appears at.
Joining two tables
Item names, prices and managers pulled in by key. Any exact-match method passes; a lookup without FALSE or 0, or a range that slides when filled down, does not.
- Item namesBasic
- PricesBasic
- Unit pricesAdvanced
- Fix the manager lookupIntermediate
Missing keys
Order lines whose SKU is not in the price list, labeled and counted, rather than #N/A spreading into every total.
- Prices, with missing SKUsIntermediate
- Unknown SKUsIntermediate
Tier and rate tables
A quantity-discount table and a commission-rate table, read with an approximate-match lookup instead of nested IFs with the rates typed in.
- Quantity discountAdvanced
- Use the rate tableAdvanced
Aggregating by key
Units for one SKU, their value at list price, and the SKU with the most units across all orders, which takes a SUMIF over the whole list of keys.
- Units of one SKUIntermediate
- Value at list priceIntermediate
- Most-ordered SKUAdvanced
Debugging silent errors
A MATCH without its match type, a COUNTIF that tries to compare two columns row by row, an average that should skip zeros, SUMIF arguments in the wrong order, a threshold typed into a formula, and an average divided by a fixed 10.
- Fix the top repAdvanced
- Fix the target countAdvanced
- Fix the averageAdvanced
- Fix the East totalIntermediate
- Make the count follow K2Intermediate
- Fix the averageBasic
Every task in this test, by level
| Level | Orders and price list | Rep bonus sheet |
|---|---|---|
| Basic | Item names Prices Line totals Order total | Fix the bonus column Fix the total Fix "Hit target?" Fix the average |
| Intermediate | Prices, with missing SKUs Unknown SKUs Units of one SKU Value at list price | Fix the manager lookup Fix the East total Fix the #DIV/0! errors Make the count follow K2 |
| Advanced | Unit prices Quantity discount Net line totals Most-ordered SKU | Use the rate table Fix the top rep Fix the target count Fix the average |
Each test uses one level: four tasks from each workbook.
Which level to choose
- Basic · 20 minutes suggestedInterns and junior reporting roles: exact-match lookups, line totals, and four common formula fixes.
- Intermediate · 30 minutes suggestedReporting analysts: missing keys, values by key, and fixing lookups, #DIV/0! and hard-coded thresholds.
- Advanced · 40 minutes suggestedAnalysts: approximate-match tiers, top key by total, and debugging formulas that are wrong without showing an error.
A real task from this test
This is one of the tasks in the Data and reporting preset at the Advanced level, on the sheet candidates see. The answers below were run through the same grader that marks real tests: on this sheet, and on two hidden versions with different data and an extra row inserted.
Sample task · Rep bonus sheet · Advanced
Fix the top rep (in K3). A colleague's formula in K3 should return the rep with the highest Q3 sales. It returns a name, but the wrong one. Find the bug and fix it.
| Row | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Q3 sales | Target | Commission | ||||||
| 2 | Alicia Grant | North | $61,200.00 | $55,000.00 | $1,836.00 | ||||||
| 3 | Ben Torres | East | $48,900.00 | $50,000.00 | $0.00 | Top rep | Jade Moore | ||||
| 4 | Chloe Park | South | $72,400.00 | $65,000.00 | $2,172.00 | Reps who hit target | 0 | ||||
| 5 | Dev Malik | West | $0.00 | $45,000.00 | $0.00 | Average sales, reps with sales | $47,930.00 | ||||
| 6 | Elena Rossi | East | $39,800.00 | $45,000.00 | $0.00 | ||||||
| 7 | Frank Liu | North | $104,300.00 | $90,000.00 | $5,215.00 | ||||||
| 8 | Gabe Silva | West | $45,500.00 | $52,000.00 | $0.00 | ||||||
| 9 | Hana Sato | South | $0.00 | $50,000.00 | $0.00 | ||||||
| 10 | Ian Doyle | East | $66,000.00 | $60,000.00 | $1,980.00 | ||||||
| 11 | Jade Moore | West | $41,200.00 | $48,000.00 | $0.00 |
| Formula in K3 | Sheet they see | Hidden checks | Result |
|---|---|---|---|
=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11))The colleague's formula, as found | Jade Moore wrong | Not needed: already wrong | Not solved |
=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))The fix: match type 0 | Frank Liu right | Right on both | Solved |
=XLOOKUP(MAX(C2:C11),C2:C11,A2:A11)Or rewritten with XLOOKUP | Frank Liu right | Right on both | Solved |
=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11))MATCH without a match type defaults to approximate match, which assumes the column is sorted. Sales are not sorted, so it lands on the wrong row and returns a real name, with no error to warn anyone.
Both fixes pass, because grading is by result. The point of the task is spotting a bug that produces a believable answer, which is the kind that reaches a report.
Results calculated by the Cellwise formula engine. The hidden versions stay on our server, so this page shows only whether each answer passed them.
See the screen your data analyst candidates work in: try a 3-minute sample test and read the report you would get. No signup.
What the report shows
When a candidate finishes, you get an overall score and every task: solved or not, the exact formula they wrote, the time spent and the number of tries, plain-English notes, and a replay of every edit with the sheet as it looked at each step. For data analysts, two things are worth a look:
- On the fix-it tasks, the replay shows how the candidate debugged: whether they read the formula and changed one argument, or deleted it and started again. Both can be right; one tells you more about how they will handle a workbook they inherit.
- Formulas far longer than needed are noted, so you can see who reaches for the simplest correct answer.
You can also add 8 multiple-choice knowledge questions at the same level, scored separately. Candidates never see their score.
What this test does not cover
- It tests Excel only. SQL, Python, Power Query, PivotTables, charts and statistics are not part of it, so test those separately if the role uses them.
- There are no P&L, budget or forecasting tasks in this preset. For finance roles, the Excel test for financial analysts covers variance to budget, growth, forecasts and loan payments.
Pricing
Free for your first 3 candidates, no card needed. Then Starter is $79 a month or $790 a year for 25 completed candidates a month, and Agency is $199 a month or $1,990 a year for unlimited candidates. Only candidates who complete a test count.
Founding price: locked for as long as you stay subscribed (first 10 customers)
FAQ
What Excel skills should a data analyst have?
Exact and approximate-match lookups (XLOOKUP, VLOOKUP, INDEX and MATCH), SUMIFS and COUNTIFS, SUMPRODUCT for row-by-row conditions, dynamic arrays such as FILTER, UNIQUE and SORTBY, handling missing keys, and the ability to find bugs in formulas that return wrong answers without an error.
Which level should I choose for an analyst?
Advanced for most analyst roles, Intermediate for junior or reporting roles, and Basic for interns. The Advanced level includes the approximate-match and silent-bug tasks.
Does it test SQL, Python or Power BI?
No. It tests Excel formulas in a live spreadsheet. If the role needs other tools, test them separately.
Can candidates use dynamic array functions?
Yes. FILTER, UNIQUE, SORTBY, XLOOKUP, LET and similar functions work in the test spreadsheet, and any formula that returns the right result on every check passes.
Is it fair to give analyst candidates an Excel test?
A short test that reflects the real work is a common part of hiring. Give every candidate the same test and time, follow local hiring law, and keep tests job-related.
Related
Looking to learn Excel yourself? Start the free Cellwise course.