Skip to content
Tutorials Center
My learning

Topics

Lesson 88 of 93 ยท Practice and Review

Formula Exercises with Solutions

Practice Excel formulas with 15 graded exercises on references, IF logic, SUMIFS, lookups, text and dates, each with a worked solution and a checker.

Reading about formulas teaches you what exists; writing them teaches you what works. This lesson gives you 15 graded exercises on the topics of the whole formula part of the course: references, logic, conditional totals, lookups, text and dates. Each one has a short task, a hint and a worked solution. Try each exercise before you open the solution, ideally in real Excel with the sample data typed in.

Excel cannot run on this site, so the sample sheet lives in a small JavaScript spreadsheet engine that is a stand-in for Excel's calculation. It evaluates the formulas you give it and compares the result with the expected answer, so you can check your solutions without leaving the page.

How to practice

Read the task Note which cells it names.
Write it first In Excel or on paper.
Check it Run the checker below.
Compare Study the worked solution.
1 / 2

The sample sheet

Type this into a blank sheet. Column F is a helper column of revenue (units times price).

A B C D E F
1 Rep Region Units Price Hired Revenue
2 Ana East 12 9.50 15 Mar 2020 =C2*D2
3 Ben West 8 12.00 1 Sep 2021 =C3*D3
4 Cara East 20 7.25 10 Jan 2019 =C4*D4
5 Dan North 5 15.00 20 Jun 2022 =C5*D5
6 Eva West 15 8.00 2 Feb 2023 =C6*D6
7 Finn East 9 11.00 30 Nov 2020 =C7*D7

More cells used below: K1 holds the tax rate 0.2; G2:H4 is a small table of Region and Manager (East Mia, West Noah, North Olga); J2 holds the text john SMITH (with spaces around it), J3 holds ORD-2025-0042 and J4 holds [email protected].

Easy: references and basic logic

Exercise 1. Calculate Cara's revenue directly from her units and price (row 4).

Hint: multiply two cells. Solution: =C4*D4 gives 145.

Exercise 2. Calculate the total revenue of all reps without using column F.

Hint: a function multiplies two ranges item by item and adds the products. Solution: =SUMPRODUCT(C2:C7,D2:D7) gives 649. See SUMPRODUCT.

Exercise 3. Calculate the tax on Cara's revenue (F4) using the tax rate in K1, written so that the formula is still correct when copied down a column.

Hint: the rate cell must not move. Solution: =F4*$K$1 gives 29. The dollar signs lock the rate; without them =F4*K1 would turn into =F5*K2 one row down. See cell references.

Exercise 4. Show "Yes" if Ben sold at least 10 units, otherwise "No".

Solution: =IF(C3>=10,"Yes","No") gives No (he sold 8). See IF.

Exercise 5. Give Cara a tier: "Gold" if units are at least 10 and the price is at least 7, "Silver" if only the units condition holds, otherwise "None".

Hint: combine two conditions inside an IF, then nest a second IF. Solution: =IF(AND(C4>=10,D4>=7),"Gold",IF(C4>=10,"Silver","None")) gives Gold. See AND, OR and NOT.

Medium: conditional totals and lookups

Exercise 6. Count how many reps are in the East region.

Solution: =COUNTIF(B2:B7,"East") gives 3.

Exercise 7. Total the revenue (column F) of East reps who sold at least 10 units.

Hint: two criteria, so use the plural function. Solution: =SUMIFS(F2:F7,B2:B7,"East",C2:C7,">=10") gives 259 (Ana 114 plus Cara 145). Note that the sum range comes first in SUMIFS. See conditional aggregates.

Exercise 8. Find the average units sold by West reps.

Solution: =AVERAGEIF(B2:B7,"West",C2:C7) gives 11.5.

Exercise 9. Show the manager of Ben's region using the table in G2:H4, with an exact match.

Solution: =VLOOKUP(B3,$G$2:$H$4,2,FALSE) gives Noah. The last argument FALSE asks for an exact match; leave it out and Excel assumes an approximate match, which needs sorted data. See VLOOKUP.

Exercise 10. Look up the manager of the region "South" and show "No manager" if it is not in the table.

Solution: =XLOOKUP("South",G2:G4,H2:H4,"No manager") gives No manager. See XLOOKUP.

Exercise 11. Return the units sold by the rep named Dan using INDEX and MATCH.

Hint: MATCH finds the position, INDEX returns the value at that position. Solution: =INDEX(C2:C7,MATCH("Dan",A2:A7,0)) gives 5. The 0 means exact match. See INDEX and MATCH.

Harder: text and dates

Exercise 12. Clean J2 so that it reads John Smith: remove the extra spaces and fix the capitalization.

Solution: =PROPER(TRIM(J2)). TRIM removes leading, trailing and repeated spaces; PROPER capitalizes each word and lowercases the rest. See text functions.

Exercise 13. From the order number in J3 (ORD-2025-0042), return the year as a number.

Hint: the year starts at character 5 and has 4 characters. Solution: =VALUE(MID(J3,5,4)) gives 2025. Without VALUE the result would be text. See TEXT, VALUE and numbers stored as text.

Exercise 14. Return the domain (the part after @) of the email in J4.

Hint: find the position of @ first. Solution: =MID(J4,FIND("@",J4)+1,LEN(J4)) gives example.com. Asking MID for more characters than remain is harmless, which is why LEN(J4) is a convenient length.

Exercise 15. Calculate the full years of service of Ana (hired on E2) as of 1 January 2026.

Solution: =DATEDIF(E2,DATE(2026,1,1),"Y") gives 5. DATEDIF is a legacy function that works but is not listed in Excel's formula autocomplete. See date calculations.

Check your answers in the engine

The TryIt below holds the sample sheet and an answers object with one formula per exercise. It starts with the worked solutions, so every line shows PASS. Edit any formula (for example, delete a $ in exercise 3, or change FALSE in exercise 9) and press Run to see what fails. The checker evaluates each formula against the sheet and compares it with the expected value.

JS Answer checker for all 15 exercises

Press Run (or Ctrl + Enter) to see the console output here.

No output.

If something fails, read the result next to it: "#VALUE!" usually means a typo or an argument of the wrong type; a wrong number usually means a range or a reference is off by one row or column.

Common wrong answers and why

Three exercises catch most learners. The next TryIt shows how a formula changes when it is copied down, which is the root of many errors. It does not need the engine: it only shifts the row numbers in the text of a formula, the way Excel does when you fill down.

JS What copying down does to a formula

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The wrong version of exercise 9 loses the first rows of the table after the first copy. That is why lookup tables almost always carry dollar signs, or better, a name.

Going further

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In SUMIFS(F2:F7,B2:B7,"East",C2:C7,">=10"), which argument comes first?

    Unlike SUMIF, the plural SUMIFS takes the range to sum first, followed by pairs of criteria range and criterion.
  2. 2. What does the last argument 0 in MATCH("Dan",A2:A7,0) mean?

    Match type 0 asks for an exact match. 1 or omitted needs sorted data and finds the largest value not greater than the key.
  3. 3. Why does =F4*K1 give wrong results when copied down for a tax rate stored in K1?

    Relative references move with the formula. Writing $K$1 locks the cell.
  4. 4. What does =PROPER(TRIM(" john SMITH ")) return?

    TRIM removes the extra spaces and PROPER capitalizes the first letter of each word and lowercases the rest.

Summary

  • Lock fixed inputs and lookup tables with $ so that copied formulas stay correct.
  • Use IF with AND or OR for multi-part conditions, and the plural functions (SUMIFS, COUNTIFS) for several criteria.
  • INDEX with MATCH or XLOOKUP find values; always ask for an exact match unless you really want bands.
  • Text work is a chain of small functions: TRIM, FIND, MID, VALUE, PROPER.
  • The checker is a JavaScript stand-in for Excel; the formulas are what you would type in a real sheet.

Next: Data Analysis Exercises with Solutions.