10 Excel Practice Tasks to Do Before Your Test

These 10 Excel practice tasks mirror the question types that commonly appear in job tests, from filling a formula down to a small case study. Each one gives you the setup, the question, a correct formula and what it checks. They're for anyone with an Excel test coming up who wants realistic practice instead of a list of functions.

How to use these Excel practice tasks#

Type a small table for each task (six to twelve rows is enough), cover the answer, and try the question yourself first. Every answer should point at cells rather than typed values, because many tests change an input to see if your answer follows.

Time yourself. Once you know the functions, most tasks take a few minutes. Any task you can't finish without help is the one to repeat tomorrow.

Warm-up tasks (1–3)#

These check the basics every test assumes.

1. Fill a calculation down a column#

Setup: units in E2:E13, price in F2:F13. Question: "Add a revenue column."

In G2, type =E2*F2, then select G2:G13 and press Ctrl+D (Mac: ⌘D). Each row multiplies its own units and price. Checks: relative references and Fill Down.

2. Build a % of total column#

Setup: revenue in G2:G13 and its total in G14, =SUM(G2:G13). Question: "Show each order's share of total revenue."

In H2, type =G2/$G$14 and fill down. The $ keeps every row pointing at the total; without it, H3 would divide by G15, which is empty, and show #DIV/0!. Format as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%). Checks: absolute references.

3. Count by a condition typed in a cell#

Setup: regions in B2:B13, a region name in J2. Question: "How many orders came from that region?"

=COUNTIF($B$2:$B$13,J2)

Change J2 and the count follows. Use "<>North" as the criteria to count everything except North. See the COUNTIF function guide. Checks: criteria from a cell.

Conditional counting tasks (4–5)#

These are among the most common intermediate questions.

4. Count and sum with two conditions#

Setup: region in B, product in D, revenue in G, rows 2 to 13. Question: "How many North Laptop orders were there, and what did they bring in?"

=COUNTIFS(B2:B13,"North",D2:D13,"Laptop")
=SUMIFS(G2:G13,B2:B13,"North",D2:D13,"Laptop")

COUNTIFS takes range and criteria pairs. SUMIFS takes the same pairs, but the sum range comes first. The guide to COUNTIFS and SUMIFS explains both. Checks: multiple conditions and argument order.

5. Fill a summary grid with one formula#

Setup: orders in A2:A16, region in B2:B16, product in C2:C16. Regions down G8:G10, products across H7:J7. Question: "Count orders for each region and product."

In H8:

=COUNTIFS($B$2:$B$16,$G8,$C$2:$C$16,H$7)

Fill right to J8, then down to row 10. $G8 always reads the region column, and H$7 always reads the product row. Checks: mixed references.

Lookup tasks (6–8)#

These use INDEX and MATCH, which work in every Excel version and look in any direction.

6. Look up to the left#

Setup: product names in A2:A9, SKUs in C2:C9, a SKU in G2. Question: "Return the product name for this SKU."

=INDEX($A$2:$A$9,MATCH(G2,$C$2:$C$9,0))

MATCH finds the SKU's position, and INDEX returns the name at that position. VLOOKUP can't do this, because the names are left of the SKUs. See the INDEX function guide and the beginner's guide to INDEX MATCH. Checks: lookups in any direction.

7. Find the name of the top item#

Setup: names in A2:A9, stock in E2:E9. Question: "Which product has the most stock?"

=INDEX(A2:A9,MATCH(MAX(E2:E9),E2:E9,0))

MAX finds the top number, MATCH finds its row, and INDEX returns the name. If two items tie, it returns the first. Checks: nesting functions without typing values.

8. Do a two-way lookup#

Setup: a table of units sold with products in A13:A17 and months in B12:E12. Question: "How many of the product in H12 sold in the month in H13?"

=INDEX(B13:E17,MATCH(H12,A13:A17,0),MATCH(H13,B12:E12,0))

The first MATCH finds the row, the second finds the column. Checks: INDEX with row and column numbers.

Case-study tasks (9–10)#

Case studies link several questions on one dataset. These two come from a hospital admissions case: patient IDs in A, ward in B, admitted and discharged dates in C and D, readmitted (Yes/No) in E, and the ward to study in I1.

9. Handle missing data#

Question: "Calculate each length of stay. Some patients haven't been discharged yet."

=IF(D2="","",D2-C2)

A plain =D2-C2 treats an empty discharge date as 0 and returns a large negative number, which wrecks every average. This version leaves the cell blank instead, and =AVERAGEIFS(F2:F13,B2:B13,I1) then skips it. Checks: edge cases.

10. Calculate a rate and summarize by group#

Question: "What share of the ward's patients were readmitted? Then build a summary by ward."

=COUNTIFS(B2:B13,I1,E2:E13,"Yes")/COUNTIFS(B2:B13,I1)

That's readmitted patients divided by all patients in the ward. For the summary, =SORT(UNIQUE(B2:B13)) in K2 lists each ward once, A to Z (Excel 2021 or Microsoft 365), and =COUNTIFS($B$2:$B$13,K2) next to it, filled down, counts each one. Checks: combining functions into a report.

How to check your answers#

Checking is part of the test. After each task:

• Compare totals: =SUM(H8:J10)=COUNTA(A2:A16) is TRUE only if every order lands in the grid. If some products aren't in the grid, add an "Other" column first.
• Change an input and confirm the answer moves.
• **Press Ctrl+** (Mac: Control+) to show formulas and look for typed numbers.

For the wider picture, see the guide to passing an Excel test for a job interview, the Excel interview questions with answers and the Excel skills admin jobs use.

Practise it#

Do these tasks in live spreadsheets that check your work: the summary grid skill check covers tasks 4 and 5, the INDEX/MATCH skill check covers tasks 6 to 8, and the hospital admissions case study covers tasks 9 and 10. Start with task 7 in the exercise below.