The first thing most hiring managers and team leads do when they ask 'Do you know Excel?' is assume the answer is yes—or no—based on a checkbox. That’s useless. It tells you nothing about whether someone can spot a broken INDEX-MATCH in row 127, fix a #REF! error after column deletion, or explain why their SUMIFS returns zero when the data looks right. Worse, it trains people to say 'yes' even when they’ve never used F9 to evaluate part of a formula.
Quick Answer
'Do you know Excel?' is the wrong question. Ask instead: 'Can you rebuild this dashboard from raw data in under 12 minutes?' Then watch them work in real time—no prep, no slides, just Excel open to Sheet1. Their keyboard rhythm, cell selection habits, and where they look first (Formula Bar? Status Bar? Name Box?) tell you more than any self-rating.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Live Task Assessment | Give raw data (e.g., sales log), define output (e.g., monthly top 3 products by region), set timer | Hiring, promotion reviews, team upskilling | Requires 15+ mins prep per test; not scalable for >20 people |
| Formula Breakdown Drill | Pick one complex formula from their file; ask them to explain each argument, then change one input and predict output | One-on-ones, coaching sessions | Fails if person copied formula without understanding |
| Error Simulation Test | Introduce deliberate errors (e.g., #N/A in lookup range, merged cells in pivot source) | Troubleshooting validation, support roles | Can feel adversarial if not framed as learning exercise |
| Shortcut Audit | Watch them navigate, select, edit, and format using only keyboard (no mouse) | Speed-critical roles (FP&A, ops analysts) | Ignores conceptual depth; favors muscle memory over logic |
| Sheet Reconstruction | Provide final output (e.g., formatted report) and blank workbook; ask them to recreate logic & formatting | Cross-training, documentation gaps | Time-intensive; hard to score objectively |
Method 1 Deep Dive
Use the Live Task Assessment. Start with this dataset in A1:E10:
| Date | Sales Rep | Region | Product | Amount |
|---|---|---|---|---|
| 2024-02-14 | Sarah Chen | APAC | Cloud Suite | $12,450 |
| 2024-02-15 | Marcus Lee | EMEA | DataGuard Pro | $8,900 |
| 2024-02-16 | Aisha Patel | Americas | Cloud Suite | $15,200 |
| 2024-02-17 | Sarah Chen | APAC | DataGuard Pro | $6,300 |
| 2024-02-18 | Marcus Lee | EMEA | Cloud Suite | $11,750 |
| 2024-02-19 | Aisha Patel | Americas | BackupVault | $4,100 |
| 2024-02-20 | Sarah Chen | APAC | BackupVault | $3,850 |
| 2024-02-21 | Marcus Lee | EMEA | BackupVault | $5,200 |
| 2024-02-22 | Aisha Patel | Americas | DataGuard Pro | $9,600 |
Task: “Show me the top 2 products by total revenue in the Americas region.” Time starts now.
Watch where they go first. If they highlight A1:E10 and hit Alt + N + V (Insert → PivotTable), good sign. If they start typing SUMIF formulas manually across 12 rows, that’s a red flag. The right move is a pivot with Region in Filters, Products in Rows, Amount in Values (Sum), then right-click → Sort → Descending. Bonus points if they use Slicer (Alt + N + S) to toggle regions live.
Method 2 Deep Dive
The Formula Breakdown Drill exposes copy-paste dependency. Give them this formula in cell G2:=INDEX($D$2:$D$10,MATCH(1,($B$2:$B$10="Sarah Chen")*($C$2:$C$10="APAC"),0))
Ask: “What does the * do here? What happens if you replace it with ,?”
If they say “it multiplies TRUE/FALSE arrays”, push further: “What’s the numeric value of TRUE × FALSE?” Answer: 0. That’s the core logic — boolean multiplication enables multi-criteria lookup. Most people think it’s syntax magic.
Then ask: “Change $C$2:$C$10="APAC" to $C$2:$C$10="EMEA". What breaks? Why?”
Correct answer: The MATCH returns #N/A because Sarah Chen has no EMEA records — but they must notice the array formula behavior (Ctrl+Shift+Enter legacy vs. modern dynamic arrays). If they don’t mention Ctrl+Shift+Enter or spill behavior, they’re memorizing, not mastering.
Here’s the counterintuitive tip: Never let them edit the formula directly. Make them select the entire formula in the Formula Bar, press F9 — then hit Esc. They’ll see the intermediate array result: {0;0;0;0;0;0;0;0;0}. That’s how you confirm they understand evaluation order.
Cheat Sheet
| Action | Step | Shortcut |
|---|---|---|
| Evaluate part of formula | Select segment in Formula Bar → F9 | F9 |
| Insert PivotTable | Select data → Alt + N + V | Alt+N+V |
| Toggle formula view | Show all formulas instead of results | Ctrl + ` (tilde) |
| Trace precedents | See which cells feed into active cell | Alt + M + P |
| Open Name Manager | Review, edit, or delete named ranges | Ctrl + F3 |
| Quick analysis tool | Select data → Alt + A + Q → choose chart/formula | Alt+A+Q |
| Paste values only | After copying → Alt + E + S + V → Enter | Alt+E+S+V |
