Excel Practice Exercises with Answers (Beginner to Intermediate)

Reading about formulas only gets you so far. These exercises make you write them. Set up the small sheet below in Excel or Google Sheets (it takes two minutes), then work through the questions. Answers are at the end of each section, so cover them first.

The practice sheet

Type this into A1:D7:

Level 1: references and basic functions

1. In F2, show the price of a Latte using a cell reference.
2. In F3, add the price of a Latte and a Tea.
3. In F4, find the total quantity sold.
4. In F5, find the average price.
5. In F6, find the most expensive item's price.
6. In F7, count how many items have a price.

Answers: 1. =B3 2. =B3+B4 3. =SUM(C2:C7) (157) 4. =AVERAGE(B2:B7) (2.97 rounded) 5. =MAX(B2:B7) (4.00) 6. =COUNT(B2:B7) (6)

If any of these felt shaky, read basic Excel formulas.

Level 2: operators, fill down and $

1. In E2, calculate revenue for Espresso (price × quantity). Fill it down to E7.
2. In E8, total all revenue.
3. In G2, show each item's share of total revenue. It must still work when filled down to G7.
4. Prices go up by the rate in J1 (type 10% there). In H2, show the new price, and fill down.

Answers: 7. =B2*C2, filled down. Espresso is 85. 8. =SUM(E2:E7) 9. =E2/$E$8 then format as %. Without the $, the copies divide by E9, E10 and break. 10. =B2*(1+$J$1)

The $ questions are the ones most people get wrong. See absolute references for why.

Level 3: conditions

1. In I2, show "Best seller" if quantity is at least 30, otherwise "Normal". Fill down.
2. Count how many items are in the Coffee category.
3. Total quantity sold for Coffee items.
4. Count items priced above 3.
5. Average price of Food items.

Answers: 11. =IF(C2>=30,"Best seller","Normal") 12. =COUNTIF(D2:D7,"Coffee") (3) 13. =SUMIF(D2:D7,"Coffee",C2:C7) (90) 14. =COUNTIF(B2:B7,">3") (2) 15. =AVERAGEIF(D2:D7,"Food",B2:B7) (2.90)

More in IF, COUNTIF and SUMIF.

Level 4: lookups and text

1. Type "Tea" in K1. In K2, look up its price with VLOOKUP.
2. Do the same with XLOOKUP.
3. Show "Not found" if K1 holds an item that isn't on the list.
4. In L2, write "Espresso (Coffee)" by joining A2 and D2.
5. Count how many items contain the letter "a" anywhere in the name.

Answers: 16. =VLOOKUP(K1,A2:B7,2,FALSE) 17. =XLOOKUP(K1,A2:A7,B2:B7) 18. =XLOOKUP(K1,A2:A7,B2:B7,"Not found") or =IFERROR(VLOOKUP(K1,A2:B7,2,FALSE),"Not found") 19. =A2&" ("&D2&")" 20. =COUNTIF(A2:A7,"*a*") (4: Latte, Tea, Croissant and Mocha. COUNTIF ignores case)

How to check your own work

• Change a number. Your answers should update. If not, you typed a value.
• Look at the last filled row. Click it and read the formula. Did the references move the way you wanted?
• Write it a second way. =B3+B4 and =SUM(B3:B4) must agree.

Want exercises that check themselves?

Cellio is built around exercises like these. You tap cells to build each formula, and the app calculates your answer and re-checks it on hidden versions of the data, then tells you exactly what went wrong. Levels 1 and 2 above match the units that are live today.

Frequently asked questions

Where can I find Excel practice exercises?

You can make your own with a small table like the one above, or use a practice app. Practising on your own real data (budget, work reports) also works well.

What Excel skills should I practise first?

References, SUM, AVERAGE, MIN, MAX and COUNT, then fill down and $ references.

How many exercises should I do per day?

Five to ten short exercises a day is plenty if you write each formula yourself.

Are these exercises the same in Google Sheets?

Yes. Every formula above works in Google Sheets as written.