Excel practice test: 12 sample tasks with answers
Employers' Excel tests vary, but most office tests ask for the same kinds of tasks. Here's a free practice test you can do in your own spreadsheet. Set a timer for 20 minutes, try each task before looking at the answer, and note the ones that slowed you down.
Set up the practice sheet
Type a small order list into a blank sheet. Put these headings in row 1: Order ID in column A, Region in column B, Units in column C and Price in column D. Then fill rows 2 to 11 with ten made-up orders, using the regions East, West and North, and units between 10 and 100.
Put a tax rate such as 8% in cell H1. You'll need it for one of the tasks.
The 12 tasks
| Task | One answer |
|---|---|
| 1. Total all the units | =SUM(C2:C11) |
| 2. Find the average price | =AVERAGE(D2:D11) |
| 3. In E2, work out the order value, then copy it down to row 11 | =C2*D2 |
| 4. In E12, total the order values | =SUM(E2:E11) |
| 5. Find the largest and smallest order value | =MAX(E2:E11) and =MIN(E2:E11) |
| 6. Count the orders from East | =COUNTIF(B2:B11,"East") |
| 7. Total the units sold in East | =SUMIF(B2:B11,"East",C2:C11) |
| 8. In F2, show "Large" for orders over 500 and "Standard" otherwise, then copy down | =IF(E2>500,"Large","Standard") |
| 9. In G2, work out the tax using the rate in H1, then copy down | =E2*$H$1 |
| 10. Find the price for the order ID typed in J1 | =VLOOKUP(J1,A2:D11,4,FALSE) or =XLOOKUP(J1,A2:A11,D2:D11) |
| 11. Count East orders of more than 50 units | =COUNTIFS(B2:B11,"East",C2:C11,">50") |
| 12. Show each order's share of the total as a percentage | =E2/$E$12, formatted as a percentage |
There's often more than one correct answer. A test that checks the result, rather than the exact formula, will accept any formula that gets it right.
Formatting and data tasks to add
Tests rarely stop at formulas. Try these too, without looking anything up:
- Make the headings bold and freeze the top row so it stays visible
- Format the Price and order value columns as currency
- Sort the list by Region from A to Z, then by Units from largest to smallest
- Filter the list to show only West orders, then clear the filter
- Make a column chart of the order values
Where people lose points
- Forgetting the $ signs in tasks 9 and 12, so the copied formulas point at the wrong cell
- Leaving out FALSE at the end of VLOOKUP, which can return a wrong match
- Typing a number where a formula was asked for. Many tests check that a formula is used
- Selecting only part of the list before sorting, which mixes up the rows
If tasks 9 to 12 felt hard, spend your next practice session on absolute references and lookups. They're the most common stumbling blocks.
Ready for a timed test?
Once you can do all 12 tasks without help, try a timed practice test with randomly chosen tasks, checked when the time runs out.
More guides
DeskReady is an independent practice tool, not affiliated with any software vendor or employer. Tests vary, so use this as a general guide.