Many employers check Excel skills before an offer, especially for finance, analyst, operations and consulting roles. It usually takes one of two forms. The first is a short online assessment, often timed and under 30 minutes, mixing knowledge questions with tasks in a simulated spreadsheet. The second, common in finance, is a practical task: clean a dataset, build a summary, or answer questions from a workbook, sometimes in the room and sometimes as a take-home.
Either way, the same core skills come up again and again. Below is what each level usually covers, ten practice questions modeled on what candidates report seeing, and a short plan for the week before your test.
Applying for a finance, banking or analyst role? Also work through Excel for finance interviews, which covers NPV, IRR, XIRR and the modeling conventions graders look for.
Your 7-day plan
About 25 minutes a day: read the guide, then do the graded exercises. Pick your level above; days you have already completed are marked from your progress in the app.
- Day 1Totals and averages
- Day 2One condition
- Day 3Counting
- Day 4Your first lookup
- Day 5Sorting and filtering
- Day 6Formatting and charts
- Day 7Timed mock test
- Day 1Lookups
- Day 2Conditional totals
- Day 3Logic
- Day 4Pivot tables
- Day 5Text and dates
- Day 6INDEX/MATCH and speed
- Day 7Timed mock test
- Day 1Lookups
- Day 2Weighted totals
- Day 3Loans and savings
- Day 4Discounting
- Day 5Real dates
- Day 6Rates and growth
- Day 7Mock test + finance set
What reviewers notice, not just the answer
On this page
What each level usually tests
Job postings and test providers use "basic", "intermediate" and "advanced" loosely, so read the job description: an analyst or finance role almost always means at least intermediate.
| Level | What you should be able to do |
|---|---|
| Basic | SUM and AVERAGE, simple IF, formatting, sorting and filtering, a basic chart. |
| Intermediate | VLOOKUP or XLOOKUP, SUMIFS and COUNTIFS, pivot tables, IFERROR, absolute references, conditional formatting, dropdown lists. |
| Advanced / finance | INDEX/MATCH, nested logic with AND/OR, SUMPRODUCT, date functions, text functions, PMT and NPV/IRR, formulas across sheets, dynamic arrays. |
Speed matters too. Timed tests reward people who move around a sheet with the keyboard, so it's worth knowing the core Excel keyboard shortcuts before test day.
10 practice questions with answers
Try each one before opening the answer. Questions 1 to 6 are intermediate level; 7 to 10 are the kind of thing an advanced or finance test adds.
1. Look up a price with VLOOKUP
Column A holds product codes and column B their prices. Cell D2 contains SKU-104. Write a formula in E2 that returns its price.
| A | B | |
|---|---|---|
| 1 | Code | Price |
| 2 | SKU-101 | 12.50 |
| 3 | SKU-102 | 8.00 |
| 4 | SKU-103 | 23.75 |
| 5 | SKU-104 | 5.40 |
| 6 | SKU-105 | 17.20 |
Show answer
Result: 5.40
The fourth argument, FALSE, forces an exact match. Leave it out and VLOOKUP defaults to an approximate match, which can quietly return the wrong row on unsorted data. This is the single most common way candidates lose points on lookup questions.
Practice this in a graded grid →VLOOKUP guide
2. Return a clear message when a code is missing
Same table. D2 now contains SKU-999, which isn't in the list. Return the price if it exists, otherwise the text Not found.
Show answer
Result: Not found
XLOOKUP's fourth argument is built for exactly this. In older Excel versions the equivalent is =IFERROR(VLOOKUP(D2,A2:B6,2,FALSE),"Not found"). Interviewers often accept either, but knowing both shows you can work in any version of Excel.
Practice this in a graded grid →XLOOKUP guide
3. Total sales for one region and one quarter
Total the West region's Q1 sales.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Quarter | Sales |
| 2 | West | Q1 | 4,200 |
| 3 | East | Q1 | 3,900 |
| 4 | West | Q2 | 5,100 |
| 5 | West | Q1 | 2,750 |
| 6 | North | Q1 | 3,300 |
| 7 | East | Q2 | 4,600 |
| 8 | West | Q1 | 1,980 |
| 9 | North | Q2 | 2,500 |
Show answer
Result: 8,930
SUMIFS takes the range to add first, then pairs of criteria range and criterion. Every range must be the same size; mixing C2:C9 with A2:A10 returns #VALUE!.
Practice this in a graded grid →SUMIFS guide
4. Count orders that meet two conditions
How many orders were over $500 and arrived late?
| A | B | C | |
|---|---|---|---|
| 1 | Order | Amount | Status |
| 2 | ORD-1 | 320 | On time |
| 3 | ORD-2 | 780 | Late |
| 4 | ORD-3 | 515 | Late |
| 5 | ORD-4 | 1,200 | On time |
| 6 | ORD-5 | 640 | Late |
| 7 | ORD-6 | 450 | Late |
| 8 | ORD-7 | 905 | On time |
| 9 | ORD-8 | 500 | Late |
Show answer
Result: 3
Comparison operators go inside the quotes: ">500". Note ORD-8 is exactly 500, so it is not counted; if the question said "500 or more", the criterion would be ">=500". Test writers like these edge cases.
Practice this in a graded grid →COUNTIFS guide
5. Combine two conditions in one IF
A rep earns a bonus if sales are at or above target and their rating is 4 or higher. Write the formula for E2 and fill it down.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Target | Rating |
| 2 | Ana | 52,000 | 50,000 | 4 |
| 3 | Ben | 61,000 | 60,000 | 3 |
| 4 | Chloé | 48,000 | 50,000 | 5 |
Show answer
Result: Ana: Bonus, Ben: No bonus, Chloé: No bonus
Ben beats his target but misses on rating, so he gets no bonus. Use AND when every condition must hold and OR when any one is enough. Nesting two IFs gives the same result, but it's longer and harder for a reviewer to check.
Practice this in a graded grid →AND / OR guide
6. Fill a formula down without breaking it
The commission rate (4%) sits in E1. Write a formula in C2 that you can fill down to C4 to calculate each rep's commission.
| A | B | |
|---|---|---|
| 1 | Rep | Deal value |
| 2 | Ana | 18,000 |
| 3 | Ben | 26,500 |
| 4 | Chloé | 9,400 |
Show answer
Result: Ana: 720.00, Ben: 1,060.00, Chloé: 376.00
Without the dollar signs, filling down turns E1 into E2, E3, which are empty, and every commission after the first comes out as 0. Press F4 (Fn+F4 on many Macs) to toggle $ on a reference. Giving E1 a name like CommissionRate is an even clearer fix.
Practice this in a graded grid →Named ranges guide
7. Look up a value to the left, or in any column
Return the department for employee ID E-130 (in E2) using INDEX and MATCH.
| A | B | C | |
|---|---|---|---|
| 1 | ID | Name | Department |
| 2 | E-104 | Lina Park | Finance |
| 3 | E-117 | Omar Haddad | Risk |
| 4 | E-121 | Sofia Rossi | Treasury |
| 5 | E-130 | Minh Tran | Audit |
| 6 | E-142 | Jonas Berg | Risk |
Show answer
Result: Audit
MATCH finds the row position (the 0 means exact match) and INDEX returns the value at that position from any column you choose. Unlike VLOOKUP, inserting a new column into the table can't break it. Many finance teams still expect you to know this pattern.
Practice this in a graded grid →INDEX/MATCH guide
8. Summarize a table with a pivot table
Using the sales table from question 3, show total sales by region, largest first.
Show answer
Result: West: 14,030, East: 8,500, North: 5,800
Pivot questions are usually graded on the output, so check two things before moving on: the value field says Sum of, not Count of (Excel switches to Count if the column has a blank or a text cell), and the sort is applied to the values, not the labels.
Practice this in a graded grid →Pivot tables guide
9. Pull part of a code out of text
Each booking code has the format Country-City-Number. Extract the 3-letter city code into column B.
| A | |
|---|---|
| 1 | Booking code |
| 2 | FR-PAR-0042 |
| 3 | VN-HAN-0107 |
| 4 | KR-SEL-0009 |
Show answer
Result: PAR, HAN, SEL
MID takes the text, the start position and the number of characters. Fixed-width codes like this suit MID; when the parts vary in length, combine it with SEARCH to find the dash, or use TEXTSPLIT in Excel 365.
Practice this in a graded grid →Text functions guide
10. Calculate a loan payment
A 5-year loan of $25,000 at 6% annual interest is repaid monthly. What is the monthly payment?
Show answer
Result: 483.32
The two classic mistakes: using the annual rate with a monthly number of periods (always divide the rate by 12 and multiply the years by 12), and the sign. PMT returns a negative number for money going out, so enter the loan as negative, or wrap the formula in a minus sign, to show a positive payment.
Practice this in a graded grid →PMT guide
Mistakes that cost points
- Typing a result instead of a formula. Graders check the cell's formula, and a hard-coded number scores zero even when it's right.
- Forgetting
FALSE(or0) in VLOOKUP and MATCH, so Excel does an approximate match. - Relative references where one cell must stay fixed, so every row after the first is wrong once the formula is filled down.
- Ranges of different sizes inside SUMIFS or COUNTIFS.
- Wrapping every formula in IFERROR. It hides real mistakes, and a reviewer reading your workbook will notice.
- Spending five minutes on one hard question. In a timed test, skip it and come back.
FAQ
How long is an Excel test for a job interview?
Online assessments are often 10 to 30 minutes. Practical tasks in finance interviews can run from 30 minutes to a few hours for a take-home. Ask the recruiter: it's a normal question and they will usually tell you the format.
Is the test in real Excel?
It depends on the provider. Some use multiple-choice questions, many use a simulated spreadsheet that behaves like Excel, and practical tasks are usually done in real Excel. Practicing in a grid where you type real formulas prepares you for all three.
Which version of Excel should I prepare for?
Learn XLOOKUP and the dynamic array functions, but also know VLOOKUP and INDEX/MATCH. Many companies still run older versions, and a test may not accept functions the grader's version doesn't have.
What score do I need to pass?
Employers set their own pass marks and rarely publish them. Aim to answer intermediate questions quickly and accurately rather than attempting everything.
Practice in a real grid, graded instantly
Every question above has a matching exercise where you type the formula and get immediate feedback. Free to start, no signup required.
Start practicing →