Cellwise Hire · Sales and operations preset
Excel test for sales operations
Candidates work in an order log and a price list: revenue and commission per order, totals by region, product and rep, prices looked up by SKU, codes that are not on the list, and quantity discounts. Every answer is checked on hidden versions of the data, so you see who builds reports that hold up next month.
Create a free sales operations hire testSee a sample task
Free for your first 3 candidates. The Sales and operations preset is preselected for you.
The test at a glance
- Workbooks
- Sales log + Orders and price list
- 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: Sales operations coordinators and analysts, Sales administrators, Order desk and order processing staff, Inside sales support.
What sales ops teams actually do in Excel
Sales ops lives between the CRM and the people asking questions. Export the orders, add revenue and commission, total by region or rep, and answer "how did West do since the 21st?" before the meeting.
Order lines often carry only a SKU and a quantity. Prices, item names and discount tiers live in another table. Joining the two, and noticing the SKU that is not on the price list, is daily work. So is the commission sheet that has to be right, because reps check it.
- Revenue and commission per order, with the rate locked in one cell
- Totals by region, product and rep, with the criteria in cells
- Item names and prices looked up from a price list by SKU
- A clear "Not found" for SKUs missing from the list, instead of #N/A
- Quantity-discount tiers and net totals, and the top product or rep
What the test checks
The Sales and operations preset uses two workbooks: Sales log (october orders: one row per order, with the rep, region, product, units and unit price) and 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). Here is what that covers for sales ops teams, with the real task names and the level each one appears at.
Revenue and commission
Units times price down a column, and commission with the rate locked as $K$1 so the formula fills down correctly.
- Revenue per orderBasic
- Commission per orderIntermediate
- Line totalsBasic
Totals by region, product and rep
SUMIF and SUMIFS with the region, rep, SKU or start date typed in a cell. At the Advanced level, a date criterion built as ">="&K2.
- Revenue for one regionIntermediate
- Desk lamps in that regionIntermediate
- Rep revenue since a dateAdvanced
- Units of one SKUIntermediate
Price lookups by SKU
Item names and prices from a second table with VLOOKUP and FALSE, XLOOKUP, or INDEX and MATCH. Any exact-match method passes; a range without $ that slides as it fills down does not.
- Item namesBasic
- PricesBasic
- Unit pricesAdvanced
- Value at list priceIntermediate
Codes that are not on the list
Order lines with SKUs missing from the price list, shown as "Not found" with IFERROR, IFNA or XLOOKUP's if-not-found argument, and counted.
- Prices, with missing SKUsIntermediate
- Unknown SKUsIntermediate
Discount tiers and rankings
An approximate-match lookup into a quantity-discount table, net line totals, the rep with the biggest order, the most-ordered SKU and a running total. Advanced level.
- Quantity discountAdvanced
- Net line totalsAdvanced
- Rep with the biggest orderAdvanced
- Most-ordered SKUAdvanced
- Running totalAdvanced
Every task in this test, by level
| Level | Sales log | Orders and price list |
|---|---|---|
| Basic | Revenue per order Total units Average order size Orders from East | Item names Prices Line totals Order total |
| Intermediate | Commission per order Revenue for one region Desk lamps in that region Large orders | Prices, with missing SKUs Unknown SKUs Units of one SKU Value at list price |
| Advanced | Running total Rep with the biggest order Rep revenue since a date Different products sold | Unit prices Quantity discount Net line totals Most-ordered SKU |
Each test uses one level: four tasks from each workbook.
Which level to choose
- Basic · 20 minutes suggestedSales administrators and order desk staff: revenue per order, totals and counts, and exact-match price lookups.
- Intermediate · 30 minutes suggestedSales ops coordinators: commission with a locked rate, totals by region and product, missing SKUs handled.
- Advanced · 40 minutes suggestedSales ops analysts: discount tiers with approximate match, revenue by rep since a date, running totals and top SKU.
A real task from this test
This is one of the tasks in the Sales and operations preset at the Intermediate 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 · Orders and price list · Intermediate
Prices, with missing SKUs (in D2, filled down to D11). Look up each order line's price from the price list in H:J by its SKU. Some SKUs are not on the list: show "Not found" for those instead of an error. One formula, filled down.
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Price | SKU | Item | Price | |||
| 2 | SO-301 | CHR-02 | 4 | DSK-01 | Standing desk | $420.00 | ||||
| 3 | SO-302 | LMP-03 | 12 | CHR-02 | Office chair | $149.00 | ||||
| 4 | SO-303 | ARM-04 | 3 | LMP-03 | Desk lamp | $24.50 | ||||
| 5 | SO-304 | DSK-01 | 1 | ARM-04 | Monitor arm | $89.00 | ||||
| 6 | SO-305 | LMP-03 | 30 | CAM-05 | Webcam | $59.00 | ||||
| 7 | SO-306 | HUB-07 | 8 | HUB-06 | USB-C hub | $38.00 | ||||
| 8 | SO-307 | CAM-05 | 6 | |||||||
| 9 | SO-308 | CHR-02 | 10 | |||||||
| 10 | SO-309 | ARM-04 | 2 | |||||||
| 11 | SO-310 | HUB-06 | 15 |
| Formula in D2 | Sheet they see | Hidden checks | Result |
|---|---|---|---|
=IFERROR(VLOOKUP(B2,$H$2:$J$7,3,FALSE),"Not found")Exact match, locked range, IFERROR | 10 of 10 rows right right | Right on both | SolvedUsed locked references ($) correctly |
=IFERROR(VLOOKUP(B2,H2:J7,3,FALSE),"Not found")The same, without $ | 4 of 10 rows right wrong | Not needed: already wrong | Not solvedMissing $: the first formula breaks when it is filled down |
=VLOOKUP(B2,$H$2:$J$7,3,FALSE)No error handling | 9 of 10 rows right wrong | Not needed: already wrong | Not solved |
=IFERROR(VLOOKUP(B2,H2:J7,3,FALSE),"Not found")The first rows are right. Filled down, the price list range slides with each row, so it stops covering the top of the list and real SKUs come back "Not found". IFERROR hides the problem instead of showing it.=VLOOKUP(B2,$H$2:$J$7,3,FALSE)Correct prices, and #N/A on the SKU that is not on the list. One #N/A also breaks any SUM below it.
Row by row
| Row | SKU | =IFERROR(VLOOKUP(B2,$H$2:$J$7,3,FALSE),"Not found") | =IFERROR(VLOOKUP(B2,H2:J7,3,FALSE),"Not found") | =VLOOKUP(B2,$H$2:$J$7,3,FALSE) |
|---|---|---|---|---|
| 2 | CHR-02 | $149.00 | $149.00 | $149.00 |
| 3 | LMP-03 | $24.50 | $24.50 | $24.50 |
| 4 | ARM-04 | $89.00 | $89.00 | $89.00 |
| 5 | DSK-01 | $420.00 | Not found (wrong) | $420.00 |
| 6 | LMP-03 | $24.50 | Not found (wrong) | $24.50 |
| 7 | HUB-07 | Not found | Not found | #N/A (wrong) |
| 8 | CAM-05 | $59.00 | Not found (wrong) | $59.00 |
| 9 | CHR-02 | $149.00 | Not found (wrong) | $149.00 |
| 10 | ARM-04 | $89.00 | Not found (wrong) | $89.00 |
| 11 | HUB-06 | $38.00 | Not found (wrong) | $38.00 |
The second answer is the one to watch for in sales ops: it looks finished, the error is hidden, and any total built on that column is quietly wrong. The report flags it as a missing $ that breaks when the formula is filled down.
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 sales operations hire 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 sales ops teams, two things are worth a look:
- On lookup tasks the notes say whether references were locked correctly ("Used locked references ($) correctly") or a $ was missed, which is a common way price lookups go wrong.
- Any correct method passes, so you can see who reaches for XLOOKUP, who uses VLOOKUP with FALSE, and who uses INDEX and MATCH, without marking anyone down for their choice.
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 does not test PivotTables, charts, CRM tools or forecasting. If those are central to the role, cover them in the interview.
- Stock levels, reorder points and days of supply are not in this preset. For stockroom and purchasing roles, use the Excel test for inventory coordinators, which pairs the price list with an inventory workbook.
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 does sales operations need?
SUMIFS and COUNTIFS with criteria in cells, exact-match lookups (XLOOKUP, VLOOKUP with FALSE, or INDEX and MATCH) between orders and a price list, handling missing codes with IFERROR or IFNA, absolute references for rates, approximate-match lookups for tiers, and date criteria for "since" and "between" questions.
Which level should I choose for sales ops?
Basic for sales administrators and order desk roles, Intermediate for sales ops coordinators, and Advanced for analysts who build commission, discount and rep reports.
Does it test commission calculations?
Yes, at the Intermediate level: commission per order from a rate in a cell, which must be locked with $ to fill down. Tiered rates appear in the discount-table task at the Advanced level.
Will a candidate who uses VLOOKUP instead of XLOOKUP lose marks?
No. Grading is by result. Any formula that returns the right values on the visible sheet and on the hidden versions passes, whichever function it uses.
Is it fair to give sales ops 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.