Cellwise Start free

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.

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
Every task in the Data and reporting preset, by level and workbook
LevelOrders and price listRep bonus sheet
BasicItem names
Prices
Line totals
Order total
Fix the bonus column
Fix the total
Fix "Hit target?"
Fix the average
IntermediatePrices, 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
AdvancedUnit 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

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.

RowABCDEFGHIJK
1RepRegionQ3 salesTargetCommission
2Alicia GrantNorth$61,200.00$55,000.00$1,836.00
3Ben TorresEast$48,900.00$50,000.00$0.00Top repJade Moore
4Chloe ParkSouth$72,400.00$65,000.00$2,172.00Reps who hit target0
5Dev MalikWest$0.00$45,000.00$0.00Average sales, reps with sales$47,930.00
6Elena RossiEast$39,800.00$45,000.00$0.00
7Frank LiuNorth$104,300.00$90,000.00$5,215.00
8Gabe SilvaWest$45,500.00$52,000.00$0.00
9Hana SatoSouth$0.00$50,000.00$0.00
10Ian DoyleEast$66,000.00$60,000.00$1,980.00
11Jade MooreWest$41,200.00$48,000.00$0.00
Answers to the sample task, what each returns on the visible sheet, and the hidden checks
Formula in K3Sheet they seeHidden checksResult
=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11))The colleague's formula, as found Jade Moore wrongNot needed: already wrongNot solved
=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))The fix: match type 0 Frank Liu right Right on bothSolved
=XLOOKUP(MAX(C2:C11),C2:C11,A2:A11)Or rewritten with XLOOKUP Frank Liu right Right on bothSolved
  • =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:

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

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)

Create a free data analyst testCompare plans

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.