How to Pass an Excel Assessment Test (Complete Guide)

What employers actually test

Excel assessments for graduate and professional roles typically test the same core skills across industries. The difficulty varies — entry-level roles test basic formulas, analyst and finance roles go deeper — but the topics are consistent.

Almost always tested:

SUM, AVERAGE, COUNT, MAX, MIN
IF statements (including nested)
VLOOKUP or XLOOKUP
Basic formatting (number formats, conditional formatting)
Sorting and filtering data

Commonly tested for analyst/finance roles:

SUMIF and SUMIFS
INDEX MATCH
Pivot tables
Percentage calculations and growth rates
Basic financial formulas (PMT, NPV)

Advanced tests (investment banking, data roles):

XLOOKUP with multiple conditions
Dynamic arrays (FILTER, UNIQUE)
Complex nested IF formulas
Building models from scratch
Data cleaning formulas (TRIM, SUBSTITUTE)

Common question formats

Complete the formula — a partially built spreadsheet, fill in missing formulas
Fix the error — a broken formula, identify and fix the bug
Build from scratch — raw data, create a summary table or analysis
Multiple choice — "what does this formula return?" questions

The biggest mistakes candidates make

Spending too long on one question — move on and come back
Not reading the question carefully — they often specify exact cell references
Forgetting $ for absolute references when a formula needs to be copied
Using hardcoded numbers instead of cell references
Not checking the result makes sense

How to prepare effectively

The only preparation that works is actually writing formulas yourself — not watching tutorials. Time yourself. Work under pressure. Make mistakes and fix them.

Practice checklist:
✓ Write VLOOKUP and XLOOKUP without looking up syntax
✓ Build a SUMIFS with 3 conditions from scratch
✓ Create a pivot table and add a calculated field
✓ Nest IF with AND and OR
✓ Fix a broken INDEX MATCH formula
✓ Calculate percentage change across a time series

💡 Know your keyboard shortcuts

Speed matters in timed assessments. Know: F2 (edit cell), F4 (toggle absolute reference), Ctrl+D (fill down), Alt+= (auto-SUM), Ctrl+1 (format cells). These save 10-15 seconds per formula — significant across a 30-minute test.

Now practise it for real

Practice these formulas in the Students track — built for university assignments and graduate job assessments. Free to start.

Start the Students track free →

How to pass an Excel assessment testcomplete preparation guide