Testing Excel skills properly: why multiple-choice doesn't cut it

Here is a question that has appeared, in some form, on nearly every corporate Excel test ever written: "Which function adds up a range of cells? (a) SUM (b) ADD (c) TOTAL (d) PLUS." Someone can answer that correctly and still be unable to build a working reconciliation, write a lookup that survives a new column, or spot that a total is £4,000 out because a range stops one row short. The quiz measured whether they recognise the word SUM. The job needs something else entirely.

If you are assessing spreadsheet competency to decide who is ready for the work — new fund accountants, analysts, finance graduates, ops teams — a multiple-choice test is close to useless, and everyone taking it knows it. The alternative is to make people build in a real workbook and grade what they produce. The reason most teams don't is not that they think MCQs are good; it's that grading workbooks by hand doesn't scale. This is a solvable problem.

Why multiple-choice fails for spreadsheets

Spreadsheet skill is procedural, not declarative. It lives in the doing — in the sequence of choices you make while a messy dataset is in front of you — and procedural skill is exactly the kind that recall questions cannot see. Three specific failures:

• Recognition isn't construction. Picking VLOOKUP from a list is a different act from writing one with the right lookup value, table range, column index, and an exact-match fourth argument that doesn't silently return approximate garbage. The first is trivia; the second is the skill.
• The real errors are invisible to a quiz. Off-by-one ranges, relative references that break when copied down, numbers stored as text, a hardcoded value buried in a formula — these are what actually go wrong in production, and none of them can be expressed as a question with four options.
• It signals that you don't take the skill seriously.Give a competent analyst a multiple-choice Excel test and you've told them the assessment is theatre. Give them a broken workbook to fix and you've told them it's real. The second gets honest effort.

Designing a task-based workbook assessment

A good spreadsheet assessment hands the candidate a workbook that looks like real work and asks them to finish it. The craft is in the setup.

Start from the job, not the function list

Don't assess SUMIFSbecause it's on a syllabus; assess it because the role calculates commission by region and period, which is a SUMIFS shaped problem. Derive the tasks from what the person will actually do — the same discipline as a proper training needs analysis. If the role reconciles two ledgers, the assessment reconciles two ledgers.

Use realistically messy data

Real datasets have trailing spaces, inconsistent date formats, a few numbers stored as text, duplicate keys, and a stray blank row. Clean data tests whether someone can type a formula; messy data tests whether they notice the formula is now wrong. The messiness isthe assessment — it's where competent and not-yet-competent separate.

Make tasks produce checkable outputs

Each task should land an answer in a specific cell: "put the reconciled difference in D42," "in column F, flag every row where the ledger and the statement disagree." A defined output cell is what makes automatic grading possible — and it also removes ambiguity for the candidate.

Building the rubric

The rubric is where a task-based assessment earns its keep. Grade against a set of discrete checks, each testing one thing, so a score becomes a diagnosis rather than a single opaque number. Three kinds of check, layered:

• Result checks:is the value in D42 correct? This is the outcome, and the easiest to automate — compare the candidate's cell against the reference answer, ideally with a tolerance for floating-point and rounding.
• Method checks:did they get there the right way? A number typed into D42 by hand is not the same as a number computed by a formula that will still be right when next month's data lands. Inspect the cell's formula, not just its value — check that it references the source range rather than hardcoding.
• Formatting and hygiene checks:is the column formatted as currency, are dates real dates, did they leave the workbook usable? For some roles this is cosmetic; for client-facing finance work it's part of the job.

Weight the checks by what matters. A correct result via a fragile hardcoded value should score below a slightly-off result reached with a sound, reusable formula — because the second person will be right every month and the first won't be. Encoding that judgement in the weights is how the rubric expresses what "good" actually means.

Per-check feedback beats a single score

"72/100" tells a learner nothing they can act on. "Correct total, but you hardcoded the tax rate instead of referencing the rate cell, and column F is formatted as text" tells them exactly what to fix. Per-check feedback turns the assessment into the last, most useful piece of training — which is also what makes it defensible when someone contests a fail.

The scaling problem, solved

Everything above is standard good practice that teams abandon the moment they picture grading eighty workbooks by hand — reconstructing each person's logic, cell by cell, is slow and inconsistent between markers. That single constraint is why so many organisations retreat to multiple-choice against their better judgement.

Automated grading removes the constraint. Because each task writes to a known output cell and each rubric check is a discrete rule — compare this value to the reference, inspect that cell's formula for a hardcoded number, confirm this column's format — a machine can evaluate the workbook the same way every time, in seconds, and return the per-check feedback with it. This is precisely what Mltitude's XLSim does: trainees download a workbook, complete it in real Excel, and upload it back; it's graded against the rubric automatically, with per-check feedback and no hand-marking. The reference answers stay server-side, so the answer key never ships inside the file the candidate holds.

The point isn't the tooling, though — it's what the tooling unlocks. Once grading is free, there's no reason left to test a practical skill with a trivia quiz. You can assess spreadsheet competency the way you'd actually judge it: by watching what someone builds.

Keep reading