Excel Practice Exercises With Answers (Beginner to Advanced)
By ExcelReady TeamUpdated 7 min read
The short answer
The best Excel practice exercises use one small table and ask real questions about it. Start with SUM, AVERAGE and a simple multiply formula, then move to SUMIF, COUNTIF and IF, and finish with VLOOKUP, SUMIFS and SUMPRODUCT. Type every formula yourself, check your result against the answer, and repeat until you don't need the answer key.
Key takeaways
- Practise on one small table so you can check every answer by eye.
- Beginner exercises cover SUM, AVERAGE and cell maths like =D2*E2.
- Intermediate exercises add conditions: SUMIF, COUNTIF and IF.
- Advanced exercises combine lookups and several conditions: VLOOKUP, SUMIFS and SUMPRODUCT.
- Type each formula yourself before you look at the answer; reading is not practice.
On this page+
These Excel practice exercises take you from your first SUM to formulas with lookups and several conditions, all on one small sales table. Each exercise has a question, the formula and the answer, so you can check yourself. They suit job seekers getting ready for an Excel test and anyone who wants to stop reading about Excel and start typing.
How to use these exercises#
Type the table below into a blank sheet, starting in cell A1. Then answer each question in an empty cell, such as H2, before you read the answer. If your result is different, compare your formula with the one shown, letter by letter.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Rep | Region | Units | Price |
| 2 | 1001 | Ana | North | 12 | 25 |
| 3 | 1002 | Ben | South | 5 | 40 |
| 4 | 1003 | Ana | South | 8 | 25 |
| 5 | 1004 | Chloe | North | 20 | 15 |
| 6 | 1005 | Ben | North | 3 | 40 |
| 7 | 1006 | Chloe | South | 10 | 15 |
| 8 | 1007 | Ana | North | 6 | 40 |
The table is small on purpose. You can check every answer by eye, which is the fastest way to learn what each formula really does.
Beginner exercises: SUM, AVERAGE and cell maths#
Beginner exercises check that you can start a formula with =, point at cells and use the basic functions. If any of these feel new, the lesson on your first formulas walks through them step by step.
Exercise 1. How many units were sold in total?
=SUM(D2:D8)Answer: 64. SUM adds every number in the range. The SUM function guide shows the other ways to write it.
Exercise 2. What is the revenue for order 1001? Put it in F2.
=D2*E2Answer: 300 (12 × 25). Now fill it down to row 8 (Windows: Ctrl+D; Mac: ⌘D after selecting F2:F8). Because the references are relative, row 3 becomes =D3*E3, and so on.
Exercise 3. What is the average price, rounded to 2 decimals?
=ROUND(AVERAGE(E2:E8),2)Answer: 28.57. AVERAGE adds the prices (200) and divides by the count (7). ROUND keeps two decimals.
Intermediate exercises: SUMIF, COUNTIF and IF#
Intermediate exercises add a condition: you only add or count the rows that match. These appear in almost every Excel job test.
Exercise 4. How many units were sold in the North region?
=SUMIF(C2:C8,"North",D2:D8)Answer: 41 (12 + 20 + 3 + 6). The first range is checked, the second range is added. The SUMIF function guide explains each argument.
Exercise 5. How many orders did Ana take?
=COUNTIF(B2:B8,"Ana")Answer: 3. COUNTIF counts matching cells. It doesn't add anything, so it needs no third range.
Exercise 6. Label each order "Big" if it has 10 or more units, otherwise "Small". Put it in G2 and fill down.
=IF(D2>=10,"Big","Small")Answer for row 2: Big. Rows 5 and 7 are also Big, so three orders in total. IF always has the same shape: a test, the value if true, the value if false.
Advanced exercises: VLOOKUP, SUMIFS and SUMPRODUCT#
Advanced exercises combine skills. You look up a value from another column, use two conditions at once, or do a whole calculation in one cell.
Exercise 7. Which rep handled order 1004?
=VLOOKUP(1004,A2:E8,2,FALSE)Answer: Chloe. VLOOKUP finds 1004 in the first column of the range and returns the value from column 2. FALSE means an exact match. The VLOOKUP function guide covers the column number and the common #N/A error.
Exercise 8. How many units did Ana sell in the North?
=SUMIFS(D2:D8,B2:B8,"Ana",C2:C8,"North")Answer: 18 (12 + 6). SUMIFS puts the range to add first, then pairs of range and criteria. Both conditions must be true.
Exercise 9. What is the total revenue, in one formula, without a helper column?
=SUMPRODUCT(D2:D8,E2:E8)Answer: 1,510. SUMPRODUCT multiplies each row's units by its price and adds the results. It gives the same total as summing column F from Exercise 2.
Exercise 10. Which rep had the single biggest order by units?
=INDEX(B2:B8,MATCH(MAX(D2:D8),D2:D8,0))Answer: Chloe (order 1004, 20 units). MAX finds 20, MATCH finds its position (row 4 of the range), and INDEX returns the name in that position.
Which exercises do job tests ask most?#
Job tests ask about totals, conditional totals and lookups more than anything else. Exercises 1, 4, 5 and 7 match the questions you are most likely to see. If you can answer them quickly and without notes, you are ready for most basic tests.
A common test question is "VLOOKUP or SUMIF?" The rule is simple: VLOOKUP returns one value from one matching row, and SUMIF adds up every matching row. The article on when to use VLOOKUP vs SUMIF shows both on the same data. For ten more questions in a test format, try these Excel test questions and answers.
How to make practice stick#
Practice works when you recall the formula, not when you recognise it. A few habits help:
- Type, don't copy. Copying a formula teaches you nothing about the brackets and commas.
- Predict the answer first. Guess the result before you press Enter, then check. A wrong guess shows you what you misunderstood.
- Change the question. After Exercise 4, try South instead of North. After Exercise 7, look up order 1006.
- Repeat the next day. Do the same ten exercises again without notes, and time yourself.
If you are short on time, this plan to learn Excel fast for a job puts these skills in a sensible order. Graduates can also check the list of Excel skills for fresh graduates to see what employers expect. Not sure where you stand? The Excel level test gives you a starting point in 12 questions.
Practise it#
Reading answers is easy; writing the formula yourself is what counts. Practise SUMIF in a live spreadsheet: you type the formula and it checks your answer instantly. Then practise VLOOKUP with exact matches, or start from the beginning with your first formulas. When you feel ready, try a timed basic Excel test.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
How many units were sold in the North region? Click H2 and use SUMIF: check the Region column (C2:C8) and add up the Units column (D2:D8).
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Price | ||||||||
| 2 | North units | ||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 |
Tip: SUMIF needs three parts: the range to check, the word North in quotes, then the range to add.
Practise this in a lesson
Frequently asked questions
How can I practise Excel at home?
Type a small table of 6 to 10 rows, then answer questions about it with formulas: totals, averages, counts by group and lookups. Check each result by hand, then try the same questions again the next day without notes.
What Excel skills are tested in a job interview?
Most Excel tests for office jobs check SUM, AVERAGE, cell references, SUMIF, COUNTIF, IF and VLOOKUP or XLOOKUP. Harder tests add SUMIFS, pivot tables and formulas with several conditions.
How long does it take to get good at Excel formulas?
Most people can learn the core formulas in a few days of focused practice. Being fast and accurate under time pressure takes longer, so repeat the same exercises until you can do them without help.
Where can I find Excel exercises with answers?
This page has ten exercises with answers on one sales table. You can type the data into any copy of Excel, write each formula, and compare your result with the answer shown.