Excel Test Questions and Examples for Hiring

Why generic Excel tests fail to differentiate

Most online Excel assessments ask about keyboard shortcuts or obscure menu options. A candidate who Googles "toggle freeze panes" in 30 seconds scores the same as someone who uses it daily. That's not assessment — that's trivia.

Real Excel skill sits in three places: formula logic, data manipulation, and problem-solving under constraints. When you assess those correctly, you see who actually works with data and who has memorized a few functions.

The formula-logic tier

Start with problems that require combining functions to solve realistic scenarios. Don't ask "What does VLOOKUP do?" Ask candidates to build with it.

Example: Sales data aggregation

"You have two sheets: Orders (order ID, customer ID, amount, date) and Customers (customer ID, region, segment). Write a formula in the summary sheet that returns the total sales for a specific customer across all orders. The customer ID should be input in cell A1 and the formula should handle missing matches gracefully."

This tests:

• VLOOKUP or INDEX/MATCH understanding
• IFERROR or error-handling logic
• Ability to reference multiple sheets
• Understanding of exact vs. approximate match

A candidate who knows Excel will produce something like: =IFERROR(SUMIF(Orders!$B:$B,$A$1,Orders!$C:$C),"No data")

A candidate guessing will produce syntax errors or irrelevant functions. The gap is real.

Example: Conditional calculations

"A product pricing sheet has cost (column A), desired margin percentage (column B), and current price (column C). Write a formula that checks if the current price meets the margin target. If it does, return the price. If not, return the minimum price that achieves the target margin."

This requires:

• Understanding profit-margin math (price = cost / (1 - margin%))
• IF logic with nested calculations
• Order of operations with parentheses

It's a small problem with commercial relevance. Excel beginners fail. Excel users solve it in seconds.

The data-manipulation tier

Move past single formulas to multi-step problems that simulate real work.

Example: Data cleaning and pivot prep

"You have a raw export with dates in multiple formats (01/15/2026, 2026-01-15, Jan 15, 2026), customer names with leading/trailing spaces, and amounts that include currency symbols. Clean the data so that dates are in a consistent format (YYYY-MM-DD), names have no extra spaces, and amounts are numeric. Write the formulas you would use and describe the order of operations."

This assesses:

• String functions: TRIM, SUBSTITUTE, FIND, MID, LEN
• Date functions: DATEVALUE, TEXT
• Data type conversion concepts
• Awareness of formula order and dependencies

No Excel user will say "I don't know where to start." A non-user will either guess or go blank.

Example: Conditional row filtering

"Without using filters or sorting, write a formula that lists every order from the past 30 days where the amount exceeded $1,000. The formula should return one result per row in a clean output area and skip rows that don't meet both criteria."

This tests:

• Understanding of array operations and helper columns
• Combining multiple conditions (AND logic)
• Date arithmetic (TODAY() - 30)
• The practical limitations of basic Excel (knowing when to pivot to more advanced techniques)

The problem-solving tier

Give candidates a realistic scenario and let them choose their approach.

Example: Scenario-based sales analysis

"A regional sales manager has data for 50 reps across 3 quarters. They need to identify underperformers quickly — people in the bottom 25% of their peer group (same region) within each quarter. Build a spreadsheet solution that flags these reps. You can use any Excel features you want. Explain your approach and the pros/cons of your method."

This is not a formula quiz. It's an assessment of:

• Problem decomposition: How do you break down "bottom 25% within groups"?
• Tool selection: Formulas, pivot tables, conditional formatting, or built-in ranking?
• Trade-offs: Speed to build vs. flexibility vs. maintainability
• Communication: Can they explain reasoning to a non-technical stakeholder?

What to avoid

Shortcut questions. "What is Ctrl+Shift+L?" Irrelevant. Anyone with Excel open can find it.

Isolated function questions. "What does the OFFSET function return?" Unless OFFSET is genuinely critical to the role (it's rarely is), skip it. Assess functions in context.

Formatting trivia. "How do you change cell background color?" Not a skill differentiation.

Hypothetical scenarios without constraints. "How would you analyze sales data?" is too open. "Analyze this specific dataset to identify the top performer by region per quarter" is testable.

How to score

For formula-based questions, correctness is binary: the formula works or it doesn't. But partial credit matters for transparency:

• Formula structure is correct but contains a minor syntax error: 60–70%
• Formula approaches the problem correctly but misses an edge case: 70–80%
• Formula is correct and handles edge cases: 100%

For scenario-based questions, score on clarity of approach, reasonableness of tool choice, and communication of trade-offs. Use a rubric anchored to the role.

Integration with broader assessment

Excel tests work best as a second-stage filter for data-adjacent roles—financial analysts, business analysts, operations coordinators. Pair them with:

• A short behavioral round on past data projects
• A take-home scenario that mirrors actual work
• A 30-minute discussion of their spreadsheet solution

This combination separates "I've used Excel" from "I can solve problems with Excel," which is the only distinction that matters for hiring.

When you assess Excel this way — on logic, data handling, and judgment — you hire people who will actually move data, not people who can recite menu options.