Cellwise Start free

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.

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
Every task in the Sales and operations preset, by level and workbook
LevelSales logOrders and price list
BasicRevenue per order
Total units
Average order size
Orders from East
Item names
Prices
Line totals
Order total
IntermediateCommission 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
AdvancedRunning 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

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.

RowABCDEFGHIJ
1OrderSKUQtyPriceSKUItemPrice
2SO-301CHR-024DSK-01Standing desk$420.00
3SO-302LMP-0312CHR-02Office chair$149.00
4SO-303ARM-043LMP-03Desk lamp$24.50
5SO-304DSK-011ARM-04Monitor arm$89.00
6SO-305LMP-0330CAM-05Webcam$59.00
7SO-306HUB-078HUB-06USB-C hub$38.00
8SO-307CAM-056
9SO-308CHR-0210
10SO-309ARM-042
11SO-310HUB-0615
Answers to the sample task, what each returns on the visible sheet, and the hidden checks
Formula in D2Sheet they seeHidden checksResult
=IFERROR(VLOOKUP(B2,$H$2:$J$7,3,FALSE),"Not found")Exact match, locked range, IFERROR 10 of 10 rows right right Right on bothSolvedUsed locked references ($) correctly
=IFERROR(VLOOKUP(B2,H2:J7,3,FALSE),"Not found")The same, without $ 4 of 10 rows right wrongNot needed: already wrongNot 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 wrongNot needed: already wrongNot 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
Each answer's result in every row of the sample
RowSKU=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)
2CHR-02$149.00$149.00$149.00
3LMP-03$24.50$24.50$24.50
4ARM-04$89.00$89.00$89.00
5DSK-01$420.00Not found (wrong)$420.00
6LMP-03$24.50Not found (wrong)$24.50
7HUB-07Not foundNot found#N/A (wrong)
8CAM-05$59.00Not found (wrong)$59.00
9CHR-02$149.00Not found (wrong)$149.00
10ARM-04$89.00Not found (wrong)$89.00
11HUB-06$38.00Not 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:

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 sales operations hire testCompare plans

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.