Excel Test for Job Interviews: 10 Practice Exercises with Answers | Free Excel Course: From Beginner to Advanced

What you'll learn

Know what pre-employment Excel tests measure and how they're scored.
Solve the 10 exercises that come up most often, using a single data table.
Work faster with the shortcuts that make a difference on a timed test.
Describe your Excel skills on a resume and in an interview with concrete examples.
Check your work with control totals before you hand it in.

"Intermediate Excel" or "advanced Excel" shows up in countless job postings for administrative, finance, operations, sales and analyst roles, and more and more U.S. employers verify it with a skills assessment: they hand you a file, a list of tasks and 20 to 45 minutes. This final lesson is your practice run. If you can solve these exercises without peeking at the answers, you're ready.

10exercises across three levels, all on one data table

30 minis your target for solving them all without looking at the solutions

$17,160is the control total: if exercise 1 doesn't match it, check before moving on

What they measure
Basic practice test
Intermediate practice test
Advanced practice test
Your resume and the interview

What they measure and how it's scored

Correct results

The first thing graders check, often by comparing your totals against an answer key.

Formulas, not typed numbers

If the grader changes an input and your total doesn't move, you lose the point.

Properly anchored references

A formula that works in the first row and breaks when copied is the most common mistake (lesson 3).

Organization and presentation

Clear headers, number formatting, no unlabeled stray cells.

Time

Almost nobody finishes everything. Do what you know first and save the hard ones for last.

The practice test data

Imagine you're applying for a sales analyst job at a technology distributor in Chicago. Copy this data into a sheet, with headers in row 1 (A1:F1) and the data in A2:F11. You can turn it into a table with Ctrl + T if you like, but the solutions use plain ranges so they work in any version.

Date	Salesperson	Region	Product	Units	Price
1/5/2026	Nicole	North	Laptop	2	1,800
1/7/2026	Derek	South	Monitor	3	450
1/12/2026	Nicole	North	Monitor	5	450
1/15/2026	Monica	Central	Printer	2	620
1/20/2026	Derek	South	Laptop	1	1,800
2/3/2026	Monica	Central	Monitor	4	450
2/8/2026	Nicole	North	Printer	1	620
2/14/2026	Derek	Central	Monitor	2	450
2/21/2026	Monica	Central	Laptop	1	1,800
2/27/2026	Nicole	North	Laptop	1	1,800

Try each exercise before opening its solution. Time yourself: the goal is all ten in under 30 minutes.

Basic level

Exercise 1: amount and total

Add column G, "Amount" (units × price), and calculate total sales.

See solution

G2   =E2*F2        (copy down to G11)
G12  =SUM(G2:G11)  → 17,160

Exercise 2: share of the total

In column H, calculate what percentage of the total each sale represents.

See solution

H2   =G2/$G$12     (Percentage format, copy down to H11)
H2   → 20.98%

The grader is looking for the $: without it, the divisor shifts to G13, G14… as you copy, and #DIV/0! appears.

Exercise 3: classify each sale

In column I, write "High" if the amount is $1,500 or more, "Medium" if it's $800 or more and "Low" otherwise.

See solution

I2  =IF(G2>=1500, "High", IF(G2>=800, "Medium", "Low"))
or  =IFS(G2>=1500, "High", G2>=800, "Medium", TRUE, "Low")

Result: 6 High, 3 Medium, 1 Low

Order the conditions from strictest to least strict: if you test "≥ 800" first, the $3,600 sale would also come out as "Medium."

Intermediate level

Exercise 4: sales by region

Calculate total sales for each region (North, South, Central).

See solution

K2 North   L2 =SUMIF($C$2:$C$11, K2, $G$2:$G$11)  → 8,270
K3 South      (copy down)                           → 3,150
K4 Central                                          → 5,740

Always confirm that the regions add up to the total (8,270 + 3,150 + 5,740 = 17,160).

Exercise 5: count with several conditions

How many of Nicole's sales were over $1,000?

See solution

=COUNTIFS(B2:B11, "Nicole", G2:G11, ">1000")   → 3

Note the quotes: "greater than 1000" is written as text, ">1000". If the number were in a cell, it would be ">"&M1.

Exercise 6: sales by month

Calculate the totals for January and February 2026.

See solution

January:  =SUMIFS(G2:G11, A2:A11, ">="&DATE(2026,1,1), A2:A11, "<"&DATE(2026,2,1))  → 10,240
February: =SUMIFS(G2:G11, A2:A11, ">="&DATE(2026,2,1), A2:A11, "<"&DATE(2026,3,1))  → 6,920

Also acceptable: a helper column with =TEXT(A2,"mmmm") or =MONTH(A2) followed by SUMIF. Either way is fine; what matters is not adding by hand.

Exercise 7: look up a value

Type a product name in one cell and make another cell show its price. If the product doesn't exist, it should say "Not found."

See solution

M1  Monitor
M2  =XLOOKUP(M1, D2:D11, F2:F11, "Not found")             → 450
or  =IFERROR(VLOOKUP(M1, D2:F11, 3, FALSE), "Not found")

If you use VLOOKUP, the fourth argument FALSE is mandatory: it's the first thing a grader checks (lesson 6).

Advanced level

Exercise 8: PivotTable with a chart

Build a PivotTable with salespeople in rows, months in columns and the sum of amount. Add a column chart and a product slicer.

See solution

Select A1:G11 → Insert → PivotTable → New Worksheet
Rows: Salesperson · Columns: Date (grouped by Months) · Values: Sum of Amount
PivotTable Analyze → PivotChart → Clustered Column
PivotTable Analyze → Insert Slicer → Product

            Jan       Feb      Total
Derek      3,150       900     4,050
Monica     1,240     3,600     4,840
Nicole     5,850     2,420     8,270
Total     10,240     6,920    17,160

A good habit on any test: check the PivotTable's grand total against your SUM from exercise 1 (17,160) and the months against exercise 6. If they don't match, there's an active filter or an incomplete range.

Exercise 9: a ranking with dynamic arrays

With a single formula, return the list of salespeople without duplicates, sorted from highest to lowest total sales.

See solution

=LET(s, UNIQUE(B2:B11),
     t, SUMIF(B2:B11, s, G2:G11),
     SORTBY(HSTACK(s, t), t, -1))

Nicole   8,270
Monica   4,840
Derek    4,050

Requires Microsoft 365 or Excel 2024 (lesson 10). If your version doesn't have HSTACK, use =SORTBY(UNIQUE(B2:B11), SUMIF(B2:B11, UNIQUE(B2:B11), G2:G11), -1) for the names and a SUMIF next to it for the totals.

Exercise 10: conditional formatting and validation

Highlight in green the entire rows whose amount is over $2,000, and make the Region column accept only North, South or Central.

See solution

Formatting: select A2:G11 → Conditional Formatting → New Rule →
            Use a formula: =$G2>2000  → green fill
            (highlights 2 rows: 3,600 and 2,250)

Validation: select C2:C11 → Data → Data Validation →
            Allow: List → Source: North,South,Central

The $ before the G locks the column so the whole row is colored based on its amount (lessons 4 and 8).

Your control totals

On a real test nobody gives you the answers, but you can check yourself: results from different exercises have to agree. The regions (exercise 4) add up to exercise 1's total, and so do the months (exercise 6) and the salespeople (exercise 9):

North (48.2%) $8,270All of it sold by Nicole.
Central (33.4%) $5,740Monica plus one of Derek's sales.
South (18.4%) $3,150Two of Derek's sales.

8,270 + 5,740 + 3,150 = 17,160, the same as January + February (10,240 + 6,920) and the sum of the three salespeople. If a control total doesn't match, that's where the error is.

1. Your PivotTable in exercise 8 shows a grand total of $15,360 instead of $17,160. What do you check first?

The type of PivotChart you chose
That the source range includes every row and that no filters are active
That the PivotTable is on a new sheet

You're missing $17,160 − $15,360 = $1,800, exactly the last row (2/27, Nicole, one laptop). Typically the source range stops at row 10, or a filter is excluding that sale.

2. During a test, you copy your share-of-total formula down and #DIV/0! appears from the second row on. What's missing?

Locking the total cell with $ (an absolute reference).
Wrapping it in IFERROR to hide the error.
Converting the data into a table.

The divisor has to stay fixed on the total. Hiding the error with IFERROR would be worse: the result would still be wrong and the grader would notice.

Shortcuts that save minutes

Windows	Mac	What it does
`Ctrl` + `T`	Same	Turns the range into a table
`Ctrl` + `Shift` + `↓`	`Cmd` + `Shift` + `↓`	Selects to the end of the data
`F4`	`Cmd` + `T`	Toggles relative and absolute references while typing
`Alt` + `=`	`Cmd` + `Shift` + `T`	Inserts an AutoSum
Double-click the fill handle		Copies the formula down to the last row of data
`Ctrl` + `Shift` + `L`	`Cmd` + `Shift` + `F`	Turns filters on or off
`Ctrl` + `	`Ctrl` + `	Shows formulas instead of results (perfect for reviewing)

3. Time is running out and you have three exercises left, one of them very hard. What do you do?

Keep at the hard one until it's solved.
Type the results by hand so everything is finished.
Solve the two you know first and, if there's time left, attempt the hard one.

Tests usually score each exercise separately. Lock in the points you know; typed-in results almost always get penalized.

Before you submit: a 2-minute review

I pressed Ctrl + ` and there are no typed-in results where a formula should be.
My control totals agree (regions, months and salespeople add up to the same total).
Amounts have a thousands separator and percentages use the Percentage format.
Every standalone result has a label next to it explaining what it is.
I saved the file with the name they asked for.

How to show it on your resume and in the interview

"Advanced Excel" on its own says nothing; a concrete example says a lot. Compare:

"Proficient in Microsoft Excel."

"Advanced Excel: PivotTables, XLOOKUP, Power Query and macros. Automated the weekly sales report, cutting it from 3 hours to 15 minutes."

"Knowledge of formulas."

"Built an accounts-receivable tracker with SUMIFS and conditional formatting to flag overdue invoices."

Calling yourself an "expert" and then stumbling over VLOOKUP on the test.

Claim the level you can prove in 30 minutes with a grader watching.

Prepare two stories for the interview using the STAR method (situation, task, action, result): a problem you solved with a formula or a PivotTable, and a process you automated. If asked, share a sample file with made-up data: the exercises from this course make a good portfolio. If you'd like a formal credential as well, Microsoft offers Microsoft Office Specialist (MOS) exams for Excel at the Associate and Expert levels. And if analysis is the career you're after, read how to get a data analyst job in our data analytics course.

4. Which description of your Excel skills is most convincing to a recruiter?

"Expert-level Excel."
"PivotTables, Power Query and macros: automated the month-end close and reduced reconciliation errors."
"Familiar with Microsoft Office."

Specific tools plus a measurable result prove your level. Adjectives alone don't.

Your journey through the course

Level 1 · BeginnerThe foundationsThe interface, formulas, references, formatting and tables.
Level 2 · IntermediateDecide and look upIF, VLOOKUP and XLOOKUP, SUMIFS, text, dates and validation.
Level 3 · AdvancedSummarize and automatePivotTables, dynamic arrays, Power Query and macros.
Level 4 · ExpertDecide with numbersFinancial functions, what-if analysis and Solver, Power Pivot and this interview test prep.

Summary

Excel tests reward correct results built with formulas, not numbers typed by hand.
Lock references with $ before copying: it's the mistake that costs the most points.
Use control totals: regions, months and salespeople must add up to the same number.
Solve what you know first and save two minutes to review with Ctrl + `.
On your resume and in interviews, name specific tools and a measurable result.

Congratulations, you've finished the Free Excel Course! You now have the skills employers mean by "advanced Excel," and then some. Your certificate is free and takes three steps: create a free account (or log in if you already have one), mark all 16 lessons of the course as completed and download your certificate of completion from the course page. Add it to the Licenses & Certifications section of your LinkedIn profile.

Ready for what's next? Put your Excel skills to work in our free data analytics course, or browse the full catalog of free courses.

What they measure and how it's scored

Correct results

Formulas, not typed numbers

Properly anchored references

Organization and presentation

Time

The practice test data

Basic level

Exercise 1: amount and total

Exercise 2: share of the total

Exercise 3: classify each sale

Intermediate level

Exercise 4: sales by region

Exercise 5: count with several conditions

Exercise 6: sales by month

Exercise 7: look up a value

Advanced level

Exercise 8: PivotTable with a chart

Exercise 9: a ranking with dynamic arrays

Exercise 10: conditional formatting and validation

Your control totals

Shortcuts that save minutes

Before you submit: a 2-minute review

How to show it on your resume and in the interview

Your journey through the course

Summary

You made it to the end of the course!