Excel Assessment Test: Formulas, Lookups and Practice
If a job advert says "strong Excel skills", there is a fair chance you will be asked to prove it. Admin, finance, operations, analyst and HR roles often include a short Excel test, either as part of the online assessment or on a laptop at the interview, because almost everyone writes "advanced Excel" on their CV.
This guide covers what pre-employment Excel tests check, how the main formats work, how they are scored, and eight practice questions with the correct formulas and results. The spreadsheets in the questions are written out as text tables, so you can rebuild them in Excel and check each answer yourself.
What Is an Excel Assessment Test?
An Excel assessment is a skills test, not an aptitude test. It does not measure how quickly you reason; it measures whether you can do specific things in the program. That makes it one of the most coachable tests in a hiring process. You either know how SUMIF works or you do not, and a week of focused practice changes that.
Several assessment publishers sell Excel tests to employers. To give you a concrete picture, here is what Criteria Corp, the publisher behind the CCAT, states about its Microsoft Excel 365 test in its help centre:
| Feature | What Criteria publishes |
|---|---|
| Time | 5 to 7 minutes |
| Questions | 12 multiple-choice questions |
| Interactive version | Adds 3 simulations in a simplified Excel interface |
| Adaptive | Questions come from a large item bank, so candidates rarely get the same set |
| Skills covered | Formulas and functions, formatting cells, sorting and filtering, charts, macros and VBA, PivotTables, data validation |
| Result | A numerical score compared with other test takers, plus a proficiency rating from Beginner to Advanced |
Criteria also notes that most keyboard shortcuts (such as Ctrl+C or Ctrl+Shift+Arrow) are not supported in its simulation tasks, although you can move between cells with the arrow keys and press Enter to confirm a formula. Other publishers and in-house tests differ in length and format, so treat this as one well-documented example rather than a universal standard. Your invitation is the only reliable source for your own test.
Multiple choice versus simulation
Multiple-choice Excel questions show a screenshot or a small table and ask which formula gives a result, what a formula returns, or which menu option does a job. You need to read formulas accurately, including every comma and dollar sign.
Simulations give you a working (but stripped-down) spreadsheet and a task: "Calculate the total in E2 and copy it down", "Sort the table by date, newest first", "Insert a PivotTable showing revenue per region". The system checks the end result and sometimes the method. Here, knowing where things sit in the ribbon matters as much as knowing the formula.
The Skills Most Excel Tests Check
Expect questions in roughly these groups, from basic to advanced.
Cell references. The difference between relative (A1), absolute ($A$1) and mixed references ($A1, A$1). This is the single most tested basic concept, because copying a formula down a column is where errors start.
Core functions. SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND. Easy points if you read carefully.
Conditional functions. IF, nested IF, IFERROR, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF. These show up in nearly every intermediate test.
Lookups. VLOOKUP, HLOOKUP, INDEX with MATCH, and XLOOKUP. Microsoft's documentation lists XLOOKUP for Microsoft 365 and Excel 2021 or later, and notes it is not available in Excel 2016 or 2019, so many workplaces and tests still use VLOOKUP.
Text functions. LEFT, RIGHT, MID, LEN, TRIM, PROPER, UPPER, CONCAT or the & operator. Common in data-cleaning tasks.
Data handling. Sorting by one or more columns, filters, removing duplicates, data validation drop-down lists, conditional formatting.
PivotTables and charts. Building a PivotTable, choosing what goes in Rows, Columns and Values, changing Sum to Count or Average, and picking the right chart type.
8 Excel Practice Questions
These are our own questions, written in the style of multiple-choice Excel tests. Questions 1 to 3 and 6 to 7 use this sales table, which starts in cell A1.
| Row | A: Order ID | B: Region | C: Units | D: Unit price |
|---|---|---|---|---|
| 1 | Order ID | Region | Units | Unit price |
| 2 | 1001 | North | 12 | 15 |
| 3 | 1002 | South | 8 | 20 |
| 4 | 1003 | North | 5 | 15 |
| 5 | 1004 | East | 20 | 10 |
| 6 | 1005 | South | 10 | 20 |
A VAT rate of 0.2 is stored in cell G1.
Practice question 1
You want a formula in E2 that calculates Units × Unit price × VAT rate, and you will copy it down to E6. Which formula works correctly in every row?
- A.
=C2*D2*G1 - B.
=C2*D2*$G$1 - C.
=$C$2*D2*G1 - D.
=SUM(C2:D2)*G1
Answer: B. When you copy a formula down, relative references shift one row at a time. C2 and D2 should shift, so they stay relative. G1 must not shift, because the VAT rate exists only in G1. In option A, row 3 would point to G2, which is empty, giving 0. Option C locks the wrong cell, and option D adds units and price instead of multiplying them.
Practice question 2
What does =SUMIF(B2:B6,"South",C2:C6) return?
- A. 2
- B. 18
- C. 28
- D. 40
Answer: B. SUMIF checks the range B2:B6 for "South" and adds the matching values from C2:C6. South appears in rows 3 and 6, with 8 and 10 units, so the result is 18. Option A is what COUNTIF(B2:B6,"South") would return, and 40 is the total of the South unit prices.
Practice question 3
What does =COUNTIFS(B2:B6,"North",C2:C6,">10") return?
- A. 0
- B. 1
- C. 2
- D. 17
Answer: B. COUNTIFS counts rows where all conditions are true. North appears in rows 2 and 4, but only row 2 has more than 10 units (12). Row 4 has 5. So one row meets both conditions. Seventeen is the sum of North units, which is what SUMIF would give, not COUNTIFS.
Practice question 4
This product table sits in A2:C5 of a second sheet.
| A: Code | B: Product | C: Price |
|---|---|---|
| P-101 | Stapler | 4.50 |
| P-204 | Desk lamp | 18.00 |
| P-310 | Monitor arm | 42.00 |
| P-415 | Chair mat | 25.00 |
What does =VLOOKUP("P-204",A2:C5,3,FALSE) return?
- A. Desk lamp
- B. 18.00
- C. 42.00
- D. #N/A
Answer: B. VLOOKUP searches the first column of the range for "P-204", finds it in the second row, and returns the value from the third column of the range, which is the price, 18.00. Column index 2 would return "Desk lamp". FALSE forces an exact match, so the formula does not return #N/A here because the code exists.
Practice question 5
A colleague's formula =VLOOKUP(F2,A2:C5,3) sometimes returns the price of the wrong product. The codes in column A are not sorted. What is the most likely cause?
- A. The column index should be 2
- B. The last argument is missing, so VLOOKUP uses an approximate match
- C. VLOOKUP cannot search text values
- D. The range should start at A1
Answer: B. If you leave out the fourth argument, VLOOKUP defaults to approximate match, which assumes the first column is sorted in ascending order. On unsorted data it can return a value from the wrong row without any error message. Adding FALSE (or 0) as the fourth argument fixes it. The XLOOKUP equivalent, =XLOOKUP(F2,A2:A5,C2:C5), uses exact match by default.
Practice question 6
You insert a PivotTable from the sales table, with Region in Rows and Units in Values, summarised as Sum. What value appears for North?
- A. 2
- B. 12
- C. 17
- D. 32
Answer: C. A PivotTable groups rows by Region and adds the Units for each group: North is 12 + 5 = 17. If the Values field were set to Count, North would show 2. South shows 18 and East 20.
Practice question 7
Cell F4 contains =IF(C4>=10,"Bulk","Standard"). What does it display?
- A. Bulk
- B. Standard
- C. TRUE
- D. 5
Answer: B. C4 contains 5. The test C4>=10 is false, so IF returns the second value, "Standard". If the formula were copied to F5 (20 units) it would display "Bulk".
Practice question 8
Cell A2 contains the text " jan de vries " (with extra spaces at the start and end). What does =PROPER(TRIM(A2)) return?
- A. "jan de vries"
- B. "JAN DE VRIES"
- C. "Jan De Vries"
- D. "Jan de vries"
Answer: C. TRIM removes leading and trailing spaces and reduces repeated spaces between words to one. PROPER then capitalises the first letter of each word. Note that PROPER also capitalises "de", which you may not want for a Dutch surname.
How Excel Tests Are Scored
Because Excel tests measure knowledge, scoring is more straightforward than for reasoning tests, but there is still no universal pass mark.
- Multiple-choice tests count correct answers. Criteria describes its Excel test as adaptive and reports a score compared with other test takers, so your result is not necessarily a simple count of correct answers.
- Simulations check whether the final state of the sheet is correct. A formula that gives the right answer in E2 but breaks when copied down usually counts as wrong.
- Proficiency bands (for example Beginner, Intermediate, Advanced) translate the score into language a hiring manager understands. The employer decides which band the role needs. An admin role might be happy with solid intermediate skills, while a financial analyst role expects lookups and PivotTables without hesitation.
Common Mistakes on Excel Tests
- Forgetting absolute references. The formula works in the first row and fails in the rest. Always ask yourself what should move when you copy.
- Leaving out FALSE in VLOOKUP. Covered above, and tested constantly.
- Reading the wrong cell. Multiple-choice questions often refer to row 4 when you have been looking at row 2 for the last three questions.
- Relying on shortcuts. In a simplified simulation, Ctrl+Shift+L may do nothing. Know the ribbon route for filters, sorting and PivotTables.
- Overbuilding. If a task asks for the total per region, a SUMIF or a simple PivotTable is enough. Elaborate array formulas take time and add risk.
A One- to Two-Week Preparation Plan
1. Days 1 to 2: find your level
Rebuild the tables in this article in Excel and answer the eight questions without looking at the explanations. Then list every function in the skills section above and mark each as "can do from memory", "need to look it up" or "never used". That list is your plan.
2. Days 3 to 5: references and conditional functions
Spend one session on relative and absolute references until copying formulas never surprises you. Then build SUMIF, SUMIFS, COUNTIFS and nested IF formulas on a small dataset of your own, such as your monthly spending.
3. Days 6 to 8: lookups
Create a product table and an order table on separate sheets and connect them with VLOOKUP, then INDEX with MATCH, then XLOOKUP if your version has it. Break each formula on purpose (wrong column index, missing FALSE, unsorted data) and see what happens.
4. Days 9 to 11: PivotTables, sorting and filtering
Build PivotTables from the same data, change the summary from Sum to Count and Average, add a filter, and create one chart. Practise every step through the ribbon, without shortcuts.
5. Days 12 to 14: timed practice
Do short timed sets of multiple-choice questions and at least one take-home style task in under an hour. Review every error until you can say why the right answer is right.
If the Excel test is one part of a wider online assessment, combine this with numerical practice: many employers who test Excel also use a numerical reasoning test, and the table-reading skills overlap. For a free baseline on the reasoning side, try our free practice tests.
Practical Tips for Test Day
- Use a laptop or desktop with a mouse, especially if simulations are included. Dragging ranges and building a PivotTable on a phone screen is slow and error-prone.
- Read each task twice: "copy down to row 20" and "sort newest first" are details that decide whether an answer counts.
- If the test is in person, ask which Excel version and language the laptop uses. Function names differ in non-English versions of Excel.
Excel skills also come up in interviews for office roles, so prepare one short example of a spreadsheet you built and what it saved. Our guide to competency-based interview questions shows how to structure that answer.
Frequently Asked Questions
What is on a pre-employment Excel test?
Most Excel tests cover formulas and functions, cell references, sorting and filtering, formatting, charts and PivotTables. Advanced versions add data validation, macros and VBA. The job advert and the invitation usually tell you which level is being tested.
Is an Excel assessment multiple choice or hands-on?
It can be either. Some tests ask multiple-choice questions about Excel, others put you in a simulated spreadsheet and ask you to complete a task. Criteria, for example, offers an Excel 365 test with multiple-choice questions only and an interactive version that adds three simulations.
Can I use keyboard shortcuts in an Excel simulation?
Do not count on it. Simulations run in a simplified copy of the Excel interface, and Criteria tells candidates that most keyboard shortcuts (such as Ctrl+C or Ctrl+Shift+Arrow) are not supported in its simulation tasks, although you can move between cells with the arrow keys and press Enter to confirm a formula. Practise doing each task through the ribbon and menus as well.
Should I learn VLOOKUP or XLOOKUP for an Excel test?
Learn both. VLOOKUP still appears in many tests and workplace files, while XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Knowing why VLOOKUP needs FALSE for an exact match is one of the most tested points.
How is an Excel test scored?
Usually as a score compared with other test takers, sometimes with a proficiency band. Criteria reports a numerical score plus a rating on a scale from Beginner to Advanced. The employer decides what level the role needs.
About the author
Ingmar van Maurik is a specialist in test and interview preparation who helps candidates perform better in recruitment processes.
References
Criteria Corp, Microsoft Excel 365 (help centre): https://criteria.helpdocs.io/article/8lomtf9c2y-microsoft-excel-365 Criteria Corp, Microsoft 365 tests FAQ: https://criteria.helpdocs.io/article/4xpdqr9ngi-microsoft-365-tests-faq Criteria Corp, Assessment Portfolio: https://www.criteriacorp.com/files/Criteria-AssessmentPortfolio.pdf Microsoft Support, XLOOKUP function: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929