Excel Assessment Test Practice: The Formulas and Pivot Table Questions Employers Ask
Excel assessment test practice with real formula, VLOOKUP, and pivot table questions employers ask, worked answers, and the vendor formats you will actually face.
Excel Assessment Test Practice: The Formulas and Pivot Table Questions Employers Ask
If a job asked you to prove your Excel skills, you will most likely face a timed test of 10 to 30 questions covering formulas, lookups, pivot tables, formatting, and data cleaning, scored on both accuracy and speed. Most employer Excel tests sit at an intermediate business level, not an advanced modeling level, and the fastest way to prepare is to practice the exact question types they use: IF, VLOOKUP or XLOOKUP, SUMIFS, and pivot tables. This page gives you real practice questions with worked answers, the modern functions most guides skip, and a clear map of which test vendor you are likely about to take.
Quick takeaways
- Employer Excel tests usually run 10 to 30 questions, often timed at 10 to 20 minutes, and mix multiple-choice with hands-on tasks in a live spreadsheet.
- The core skills tested almost everywhere: formulas (SUM, IF, COUNTIF), lookups (VLOOKUP, INDEX/MATCH, XLOOKUP), conditional logic (SUMIFS), and pivot tables.
- Kenexa Prove It (now IBM Talent Assessments), IKM, SHL, TestGorilla, and TestDome are the vendors behind most tests; formats range from multiple-choice to interactive simulations.
- Modern functions like XLOOKUP, FILTER, and IFERROR increasingly show up; many free practice sets still teach only the legacy versions.
- There is no universal passing score. The employer sets the bar, and speed under the clock is what separates candidates.
- Practice the format, not just the functions. Most people lose points to the timer, not to difficulty.
What an Excel assessment test actually looks like
An Excel assessment test is a pre-employment or interview screen that measures how well you can use Microsoft Excel for real work tasks. You will see it for roles in administration, bookkeeping, finance, data entry, operations, and analysis. The test almost always has a time limit, which is the part candidates underestimate.
Formats fall into two buckets. The first is multiple-choice: you read a question about a formula or feature and pick the right answer or the correct result. The second is interactive simulation: you perform tasks inside a mock or live Excel window, such as building a formula, sorting a range, or creating a pivot table, and the system grades what you did. Many employer tests combine both, and some, like the classic Kenexa Prove It, are known specifically for the interactive style.
Difficulty is usually labeled Basic, Intermediate, or Advanced, and most job tests land in the Intermediate band. That means you are expected to be comfortable with lookups, conditional functions, and pivot tables, not just typing a SUM.
The Excel skills employers actually test
Before the practice questions, here is the honest skill map, drawn from the tasks that show up across employer tests and the major vendors. Focus your prep here.
| Skill area | What you need to do |
|---|---|
| Core formulas | SUM, AVERAGE, COUNT, COUNTA, COUNTIF, and absolute vs relative references ($) |
| Logic | IF, nested IF with AND/OR, IFERROR |
| Conditional math | SUMIF, SUMIFS, COUNTIFS, AVERAGEIF |
| Lookups | VLOOKUP, HLOOKUP, INDEX/MATCH, and modern XLOOKUP |
| Data tools | Sort, Filter, Remove Duplicates, Data Validation, Text to Columns, Flash Fill |
| Pivot tables | Build a pivot, set Rows, Columns, Values, and Filters, change aggregation, add a pivot chart |
| Formatting | Number formats, Merge and Center, Wrap Text, Conditional Formatting |
| Output | Page Setup, headers, landscape orientation, print to PDF |
If you are short on time, prioritize IF, SUMIFS, VLOOKUP or XLOOKUP, and pivot tables. Those four appear on almost every intermediate test.
Basic Excel practice questions (with answers)
Start here to confirm your foundation. Each question mirrors the recognition-and-single-step style of a basic-level test.
1. Which formula adds the values in cells B2 through B10?
The answer is =SUM(B2:B10). The colon defines the range, and SUM totals every value inside it. If you typed =B2+B3+B4 and so on, it works but is slower and breaks if a row is inserted.
2. You wrote =A1*C1 in cell D1 and copied it down. In D2 it became =A2*C2. How do you keep it always multiplying by C1?
Lock the reference with a dollar sign: =A1*$C$1. The $ before both the column and row freezes cell C1 so it does not shift when you copy the formula down. This absolute-versus-relative distinction is one of the most common basic-test traps.
3. What does Merge and Center do, and why is it risky in a data table?
It combines selected cells into one and centers the content. It is fine for a title row, but inside a data range it breaks sorting, filtering, and formulas that expect one value per cell. A good candidate knows to avoid it in tables.
4. Which keyboard shortcut selects the entire column of the active cell?
Ctrl and Spacebar selects the column; Shift and Spacebar selects the row. Small shortcuts like these matter on timed interactive tests where every second counts.
Intermediate Excel practice questions (with answers)
This is the band most job tests target. Work through these with a real spreadsheet if you can.
5. Given a sales table, write a formula that shows "Bonus" if a rep's sales are 1,000 or more, otherwise "No Bonus."
| Rep | Sales |
|---|---|
| Ada | 1,240 |
| Ben | 860 |
| Cara | 1,000 |
The answer is =IF(B2>=1000,"Bonus","No Bonus"). IF checks the condition, returns the first value if true and the second if false. Note that >= includes exactly 1,000, so Cara earns a bonus.
6. Using the same expense data, total only the "Travel" expenses for 2024.
You need SUMIFS, which sums with multiple conditions: =SUMIFS(Amount, Category, "Travel", Year, 2024). SUMIF handles one condition; SUMIFS handles two or more. Getting the argument order right (sum range first, then each criteria pair) is the tested skill.
7. You have an Employee ID in one column and need the matching Department from a lookup table. Which function, and why might INDEX/MATCH or XLOOKUP beat VLOOKUP?
VLOOKUP works: =VLOOKUP(A2, EmployeeTable, 3, FALSE). But it can only look to the right and breaks if someone inserts a column. INDEX/MATCH (=INDEX(DeptRange, MATCH(A2, IDRange, 0))) and the modern =XLOOKUP(A2, IDRange, DeptRange) both handle left lookups and survive column changes. If your version of Excel supports XLOOKUP, it is the cleanest answer, and naming it signals current knowledge.
8. How do you create a drop-down list so a cell only accepts "Yes" or "No"?
Use Data Validation. Select the cell, go to Data, then Data Validation, choose List, and enter Yes,No as the source. This is a frequent interactive-test task because it shows you understand data integrity, not just formulas.
Advanced Excel practice questions (with answers)
If the role is analytical, expect a handful of these.
9. You have raw sales rows with Region, Month, and Amount. You need total sales by Region, broken out by Month. What tool, and how do you set it up?
A PivotTable. Insert a pivot from the data, then drag Region to Rows, Month to Columns, and Amount to Values (set to Sum). The pivot aggregates thousands of rows into a clean cross-tab in seconds. Being able to describe which field goes in Rows, Columns, and Values is the exact competency intermediate-to-advanced tests check.
10. Write one formula that flags a candidate as "Qualified" only if their score is at least 80 and their attendance is at least 90 percent.
Nest AND inside IF: =IF(AND(B2>=80, C2>=0.9), "Qualified", "Not Qualified"). AND returns TRUE only when both conditions hold. This tests whether you can combine logical functions, a step up from a single IF.
11. A lookup formula returns #N/A when an ID is missing. How do you make it show "Not found" instead?
Wrap it in IFERROR: =IFERROR(XLOOKUP(A2, IDRange, DeptRange), "Not found"). IFERROR catches the error and substitutes a friendly value. Clean error handling is a mark of an experienced user and is often quietly graded.
The modern functions most practice tests skip
Many free Excel practice sets were written years ago and still teach CONCATENATE and legacy lookups as the "right" answer. Current Excel, especially Microsoft 365, added functions that are both easier and increasingly expected on updated tests:
- XLOOKUP replaces VLOOKUP, HLOOKUP, and much of INDEX/MATCH. It looks in any direction and returns a default if nothing matches.
- FILTER returns all rows that meet a condition as a spilled range, no manual filtering needed.
- UNIQUE returns a de-duplicated list from a column.
- TEXTJOIN joins text with a chosen separator, replacing clunky CONCATENATE chains.
- IFERROR cleans up error messages, as shown above.
You do not need to master all of them, but if your interviewer uses a recent version of Excel, knowing XLOOKUP and IFERROR is a genuine edge. If the test environment is older, VLOOKUP and INDEX/MATCH still apply, so learn both the legacy and the modern answer.
Which test are you actually taking? The vendor map
"Excel assessment" is a category, not a single product. Knowing the vendor tells you the format to expect. Employers rarely name it upfront, but this map covers the common ones.
| Vendor | Format | What to expect |
|---|---|---|
| Kenexa Prove It (IBM Talent Assessments) | Interactive simulation, sometimes multiple-choice | Do tasks in a mock Excel window; often a Normal User vs Power User version; timed |
| IKM (Assess) | Adaptive multiple-choice | Difficulty adjusts to your answers; broad function coverage |
| SHL | Interactive simulations, part of a wider battery | Office and Excel modules, timed, often alongside aptitude tests |
| TestGorilla | Multiple-choice plus interactive | Intermediate and advanced Excel tests used by many employers |
| TestDome | Practical, hands-on tasks | You solve real spreadsheet problems |
One current note worth knowing: Indeed discontinued its Indeed Assessments skills tests in October 2024, so if a listing still references them, the employer has likely moved to one of the vendors above.
A separate thing people confuse with employer tests is the Microsoft Office Specialist (MOS) certification (exam codes such as MO-200 for Excel Associate and MO-201 for Excel Expert). MOS is an official Microsoft credential you choose to earn, not a screen an employer sends you. Both are worth preparing for, but they are different tests with different formats.
How to prepare when your test is days away
You do not need weeks. The single most effective thing you can do is take timed, full-length practice that mirrors the real format, then drill your two weakest function types.
A practical order for a short window: confirm your basics (SUM, absolute references, sorting), then spend most of your time on the four highest-value skills (IF, SUMIFS, VLOOKUP or XLOOKUP, pivot tables), then do one full timed run so the clock does not surprise you. Because many tests are hands-on, practice inside an actual spreadsheet, not just by reading questions.
This is where structured practice pays off. Rather than guessing which functions matter, work through a test bank organized by the exact skills employers screen for, under the real time limit.
How PrepClubs helps you pass your Excel assessment
PrepClubs is built for exactly this situation: a real test on your calendar and limited time to prepare. For the Excel assessment you get full-length practice tests timed to the real format, plus topical drills for each function type, so you can close your specific gap on lookups or pivot tables instead of practicing at random.
Because a job can hinge on this one screen, PrepClubs backs its prep with a 30-day Pass Guarantee: prepare with us, and if you do not pass your real test, we extend your access at no extra cost. More than 1,600 students have used PrepClubs to prepare for cognitive, aptitude, and skills tests. You can start with the free practice questions and move to the full bank when you are ready.
FAQ
Is there free Excel assessment test practice?
Yes. This page includes free basic, intermediate, and advanced practice questions with worked answers, and PrepClubs offers a free set of practice questions before any purchase. Practicing the exact question types, especially IF, SUMIFS, lookups, and pivot tables, is more useful than random quizzes.
What is a passing score on an Excel assessment test?
There is no universal passing score. The employer sets the threshold based on the role, so an analyst position expects a higher score than an administrative one. Aim to answer confidently and quickly rather than chasing a specific number, since most candidates lose points to the time limit.
How long is an Excel assessment test?
Most run 10 to 30 questions with a time limit of roughly 10 to 20 minutes, though hands-on task tests can take longer. The timing is deliberately tight, which is why timed practice matters as much as knowing the functions.
What Excel functions are most commonly tested?
IF (including nested IF with AND and OR), SUMIF and SUMIFS, VLOOKUP, INDEX/MATCH, XLOOKUP, COUNTIF, and pivot tables. Formatting, sorting, filtering, Remove Duplicates, and Data Validation also appear frequently.
Can I use XLOOKUP on an Excel test?
If the test environment runs a recent version of Excel, yes, and it is often the cleaner answer. If it runs an older version, XLOOKUP may not be available, so learn VLOOKUP and INDEX/MATCH as well. Knowing both the modern and legacy approach is the safest preparation.
Is the Kenexa Prove It Excel test multiple-choice or hands-on?
Kenexa Prove It, now part of IBM Talent Assessments, is best known for interactive simulations where you perform tasks in a mock Excel window, though some versions include multiple-choice. It often comes in Normal User and Power User levels, and it is timed.
Related on PrepClubs
Ready to prepare seriously? PrepClubs gives you full-length Excel assessment practice tests and topical drills for every function type, from IF and SUMIFS to pivot tables, backed by the 30-day Pass Guarantee. Start practicing for your Excel test.
FAQ
