Excel skills test
20 multiple-choice questions in three sections, from cell references to pivot tables and Power Query. Optional 10-minute timer. You get a score out of 20, a section breakdown, and a one-line explanation for every answer. Free, no sign-up, and nothing you answer leaves your browser.
Take the test
20 questions, one at a time. You can skip a question and come back to it. Answers are scored in your browser and never leave it.
Your result
0/20
Time ran out, so unanswered questions were counted as missed.
- Basics 0/6
- Intermediate 0/8
- Advanced 0/6
Answer key and explanations
-
Cell C2 contains the formula =A2+B2. You copy it and paste it into C5. What formula ends up in C5?
Correct answer: B. =A5+B5
Relative references shift with the formula. Pasting three rows down turns A2 and B2 into A5 and B5.
-
Column A holds order amounts and cell B1 holds a tax rate. You write =A2*B1 in C2 and copy it down the column. Every result except the first is wrong. What is the fix?
Correct answer: C. =A2*$B$1
$B$1 locks the tax rate cell so it stays put as the formula copies down. A2 stays relative so each row uses its own amount.
-
In A1:A100, 80 cells hold numeric order IDs, 12 hold the text 'pending', and 8 are blank. What does =COUNTA(A1:A100) return?
Correct answer: C. 92
COUNTA counts every non-empty cell, text included, so 80 + 12 = 92. COUNT would return 80 because it only counts numbers.
-
A cell displays $1,240 but the formula bar shows 1239.6437. What is going on?
Correct answer: A. Number formatting changed the display only. The stored value is still 1239.6437
Formatting changes what you see, never what is stored. Calculations keep using the full underlying value.
-
In a customer table, you select only column C, sort it Z to A, and choose 'Continue with the current selection' at the warning. What happens?
Correct answer: D. Only column C reorders, so its values no longer line up with the right rows
Sorting one column alone tears it away from its rows and quietly scrambles the table. Let Excel expand the selection so whole rows move together.
-
You filter a 500-row sales table to show only the West region. Below the table, a plain =SUM formula adds up the full Amount column. What does it show?
Correct answer: B. The total for all 500 rows, because filtering hides rows without removing them
Filtered-out rows are hidden, not gone, and SUM still includes them. SUBTOTAL is the function that respects filters.
-
You write =VLOOKUP(E2, A2:C400, 3) with no fourth argument, and it returns plausible-looking but wrong values. Why?
Correct answer: D. With the fourth argument omitted, VLOOKUP defaults to approximate match, which assumes the first column is sorted
The omitted range-lookup argument defaults to TRUE, an approximate match that silently returns wrong values on unsorted data. End with FALSE for exact matches.
-
Which of these can XLOOKUP do that VLOOKUP cannot?
Correct answer: B. Return a value from a column to the left of the lookup column
XLOOKUP takes separate lookup and return ranges, so it can look in any direction. VLOOKUP can already do the other three.
-
Region names sit in A2:A400 and order amounts in C2:C400. Which formula totals the amounts for the West region only?
Correct answer: A. =SUMIF(A2:A400, "West", C2:C400)
SUMIF takes the range to test, the criterion, then the range to add. Test the regions, sum the amounts.
-
Column D holds days outstanding for 200 invoices in D2:D201. Which formula counts the invoices more than 30 days out?
Correct answer: C. =COUNTIF(D2:D201, ">30")
Comparison criteria in COUNTIF and SUMIF are written as text, so the operator and number go inside quotes.
-
A2 contains ' Acme Corp ' with stray spaces that break your lookups. Which formula cleans it to 'Acme Corp'?
Correct answer: B. =TRIM(A2)
TRIM strips leading and trailing spaces and collapses repeated spaces between words. SUBSTITUTE with an empty string would glue the words into AcmeCorp.
-
Your pie chart of revenue share has 14 slices and nobody can read it. What is the standard fix?
Correct answer: D. Switch to a sorted bar chart
Past a handful of categories, length is easier to compare than angle. A sorted bar chart makes 14 values readable at a glance.
-
The Status column should only ever contain Open, In progress, or Closed, and typos keep sneaking in. Which feature fixes that at entry time?
Correct answer: A. Data validation with a dropdown list
A data validation list restricts what a cell will accept and gives entry a dropdown. Conditional formatting only changes how values look after they land.
-
You apply a conditional formatting formula rule to A2:F100 so a whole row highlights when that row's value in column D exceeds 60. Which formula works?
Correct answer: C. =$D2>60
Lock the column, not the row. $D2 makes every cell in a row test that row's column D value, while an unlocked D2 would drift across columns.
-
You add 200 new rows under your pivot table's source data, but the pivot's totals do not change. What is the explanation and the durable fix?
Correct answer: B. Pivots read a cached snapshot of a fixed source range. Refresh it, and base it on an Excel Table so the source grows automatically
A pivot works from a cached copy of a defined source range. Refresh re-reads it, and a Table as the source picks up new rows on its own.
-
What does =INDEX(B2:B500, MATCH("Acme", A2:A500, 0)) return?
Correct answer: C. The value in B2:B500 at the same position where Acme sits in A2:A500
MATCH finds Acme's position in column A, and INDEX returns the value at that position in column B. The 0 forces an exact match.
-
In Excel 365 you enter =UNIQUE(A2:A500) in C2, which should spill about 40 distinct values down column C. C10 already contains data. What happens?
Correct answer: D. The formula returns #SPILL! and writes nothing until the blocking cell is cleared
Spilled arrays never overwrite existing data. The formula shows #SPILL! until every cell in the spill range is empty.
-
Every Monday you get a raw CSV export that needs the same dozen cleanup steps before it is usable. What is the Excel-native way to stop doing that by hand?
Correct answer: A. Build the cleanup once in Power Query, then refresh the query against each new file
Power Query records the transformation steps as a repeatable query. Point it at the new export and refresh, and the whole cleanup re-runs.
-
What does =IFERROR(VLOOKUP(E2, A:C, 3, FALSE), "Not found") do?
Correct answer: B. Returns the lookup result, or 'Not found' when the lookup fails with an error like #N/A
IFERROR passes the result through and swaps in your fallback only when the formula errors. An empty match is not an error, so it returns 0, not the fallback.
-
Your sheet calculates a monthly loan payment from rate, term, and amount. You know the payment you can afford and want Excel to find the loan amount that produces it. Which built-in tool does this?
Correct answer: C. Goal Seek
Goal Seek works backwards: set a formula cell to a target value and it adjusts one input until the formula hits it. Scenario Manager only stores and swaps sets of inputs.
What the test covers, and why these 20 questions
Excel skill isn't one thing. Someone can write clean formulas and still scramble a table the first time they sort it. So the test splits 20 questions across three layers and scores each one separately.
Basics, six questions. Cell references, what happens to a formula when you copy it, what number formatting actually changes, and how to sort and filter without wrecking the data. This is the floor for any position that touches a spreadsheet. The sorting question describes the single most common way shared workbooks get quietly corrupted.
Intermediate, eight questions. The biggest section on purpose. Lookups, SUMIF and COUNTIF, text cleanup, chart judgment, data validation, and conditional formatting. This is the layer most day-to-day reporting runs on, so it carries the most weight.
Advanced, six questions. Pivot tables, INDEX and MATCH, dynamic arrays, Power Query, error handling, and what-if analysis. These separate people who have processed real data volumes from people who have edited spreadsheets someone else built.
Every question tests a decision you'd face in real work: why a lookup returns plausible but wrong values, which tool restricts entry versus which one just highlights it, what a #SPILL! error is telling you. No keyboard-shortcut trivia, no "which menu is it under". And every answer comes with an explanation, so a wrong guess turns into the exact thing to go learn.
Using it to screen candidates for Excel skills
"Advanced Excel" might be the least verifiable line on any resume. It costs nothing to write and most interviews never check it. A short practical test replaces self-description with evidence: here is what this candidate can actually answer.
An Excel test for employment screening only works if you run it the same way for everyone. Same questions, same timer setting, same point in the process. Decide the bar before anyone takes it, and read the section breakdown, not just the total. A bookkeeping candidate who misses what-if analysis is fine. One who misses absolute references is not.
Calibrate before you trust any number. Have two or three people already doing the position take the test. Their scores tell you what good looks like on your team, which beats any generic passing score.
And be honest about what a quick unproctored quiz gives you: a useful first signal, not a screening process. Candidates take it on their own machine, on their own time, with nothing keeping conditions consistent. If Excel matters for the position, make a skills assessment a structured step that every candidate completes under the same conditions.
Using it as a self-check before an interview
If the position description lists Excel and you're not sure where you stand, take it with the timer on. Ten minutes is enough pressure to stop you from second-guessing every answer, which is closer to how you'd perform in a real work sample.
Then read every explanation, including the ones you got right. Each one names the function or concept to search next. Miss the XLOOKUP question and you know exactly what to spend an evening on before the interview.
Frequently asked questions
-
What does this Excel test cover?
Three sections. Six basics questions on cell references, copying formulas, formatting, and sorting and filtering. Eight intermediate questions on VLOOKUP and XLOOKUP, SUMIF and COUNTIF, text functions, charts, data validation, and conditional formatting. Six advanced questions on pivot tables, INDEX and MATCH, dynamic arrays, Power Query, error handling, and what-if analysis.
-
How is it scored?
One point per question, 20 total, no partial credit. You get the overall score, a per-section breakdown, and a one-line explanation for every answer. Scoring happens instantly in your browser.
-
Can I use this to screen candidates?
Yes, as a quick check: send the link and ask candidates to reply with their results. For real screening, every candidate should take the same assessment under the same conditions, with results scored consistently and compared side by side. That's the job of a screening platform like Truffle.
-
What is a passing score?
It depends on the position's bar. A reporting analyst should clear the advanced section; an office coordinator mostly needs the basics and intermediate layers. The honest way to set a bar is to have two or three people already doing the work take the test and calibrate from their scores.
-
Do you store my results?
No. The test runs and scores entirely in your browser, your answers never leave it, and there's no email gate or sign-up.
-
Should I learn VLOOKUP or XLOOKUP?
XLOOKUP, wherever your Excel version has it (2021 and Microsoft 365): it matches exactly by default, looks in any direction, and doesn't break when columns move. Learn to read VLOOKUP anyway, because older workbooks are full of it.
See other free recruiting tools
- Assessments Work style profile A Big Five based self-assessment for work. See how you operate across five dimensions. No four-letter labels.
- Sourcing Boolean search builder Turn a role and a skills list into a clean boolean string for LinkedIn, Indeed, or Google. Copy it and go source.
- Interviewing Interview question generator Pick a role family, seniority, and the competencies you care about. Get a structured question set with a scorecard.
Run the same skills check on every candidate
This page is the manual version. Truffle is a candidate screening platform that combines talent assessments with resume screening and one-way video interviews, so the skills check becomes a step in your process instead of a link you paste. Pick role-based skills assessments from the test library, send the same one to every candidate, and AI scores the responses against your criteria and surfaces the evidence. You decide who moves forward.
7-day free trial. No credit card required.