Write real spreadsheet formulas on a live sheet. The same formulas work in Excel and Google Sheets.
THE SHEET
Twelve Orders, Six Columns
A small sales sheet, laid out with the same column letters and row numbers you would see in Excel.
A
B
C
D
E
F
1
Date
Region
Product
Rep
Units
Value
2
2026-03-02
North
Pro plan
Ana
2
480
3
2026-03-03
South
Starter
Ben
1
60
4
2026-03-05
North
Add-on
Ana
3
135
5
2026-03-08
East
Pro plan
Cara
1
240
6
2026-03-11
South
Pro plan
Ben
2
480
7
2026-03-14
North
Starter
Ana
1
60
8
2026-03-18
West
Add-on
Dev
4
180
9
2026-03-21
East
Pro plan
Cara
2
480
10
2026-03-24
South
Add-on
Ben
1
45
11
2026-03-27
West
Pro plan
Dev
1
240
12
2026-03-29
North
Pro plan
Ana
2
480
This runs a teaching subset of spreadsheet formulas in your browser: SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, ROUND, ABS, IF, AND, OR, COUNTIF, SUMIF, SUMIFS, COUNTIFS, AVERAGEIF, VLOOKUP, XLOOKUP, LEFT, RIGHT, LEN, UPPER, LOWER, TRIM and CONCAT. Nothing is sent anywhere, and your work is saved on this device.
SIX EXERCISES
Write, Run, Check
Each exercise teaches the formula first, then gives you a task to write yourself. Unlimited attempts, a hint whenever you want one, and the worked solution after three tries.
0 of 6 exercises solved
1
Exercise 1 of 6
Add up a column
Not attempted
Why this matters: Adding a column is the first thing anyone does with a spreadsheet, and the pattern behind every other formula.
First, the formula: SUM and AVERAGE
A formula always starts with an equals sign. SUM adds a range of cells; AVERAGE gives the mean of the same range. A range is written as the first cell, a colon, then the last cell.
The pattern
=SUM(F2:F13)
=AVERAGE(F2:F13)
A worked example
=SUM(E2:E13)
Adds every value in the Units column. Change E to F and you have total revenue instead.
Your task
Finance wants the total revenue for March. Add up every value in the Value column.
fx
Hint
SUM takes one range: first cell, colon, last cell.
The Value column is F, and the data runs from row 2 to row 13.
What this tells you: Total March revenue is £2,880 across twelve orders.
2
Exercise 2 of 6
Count rows that match
Not attempted
Why this matters: Counting matching rows answers most of the questions people ask of a small dataset.
First, the formula: COUNTIF
COUNTIF counts the cells in a range that match one condition. Text conditions go in double quotes; numbers do not. You can also use comparisons such as ">200".
The pattern
=COUNTIF(range, condition)
A worked example
=COUNTIF(C2:C13,"Starter")
Counts how many rows sold the Starter product. Try ">200" against the Value column.
Your task
How many orders were for the Pro plan? Count the rows where the product is Pro plan.
fx
Hint
Text conditions go in double quotes.
COUNTIF takes the range to look in, then what to look for.
What this tells you: Six of the twelve orders were Pro plan.
3
Exercise 3 of 6
Add up only some rows
Not attempted
Why this matters: This is the formula that turns a flat list into a report.
First, the formula: SUMIF
SUMIF adds up one range, but only the rows where another range matches your condition. The order is: where to look, what to look for, what to add.
The pattern
=SUMIF(look_in, condition, add_up)
A worked example
=SUMIF(D2:D13,"Ana",E2:E13)
Adds the units sold by Ana. Swap the last range for F2:F13 and you get her revenue.
Your task
The North manager wants their revenue. Add up the Value column, but only for rows in the North region.
fx
Hint
Look in one range, add up another.
SUMIF takes where to look, what to look for, then what to add.
What this tells you: The North region brought in £1,155, the largest share of the month.
4
Exercise 4 of 6
Two conditions at once
Not attempted
Why this matters: Real questions usually have more than one condition, which is where SUMIF runs out.
First, the formula: SUMIFS and COUNTIFS
SUMIFS handles more than one condition. This time the range to add comes first, then pairs of range and condition. COUNTIFS works the same way but counts instead.
The pattern
=SUMIFS(add_up, range1, cond1, range2, cond2)
A worked example
=SUMIFS(F2:F13,B2:B13,"North",C2:C13,"Pro plan")
Revenue from Pro plan sales in the North only. Note the add-up range moves to the front.
Your task
How much Pro plan revenue came from the South? Add the Value column where region is South and product is Pro plan.
fx
Hint
With SUMIFS, the range to add comes first.
SUMIFS puts the add-up range first, then pairs of range and condition.
What this tells you: £480 of Pro plan revenue came from the South, a single order.
5
Exercise 5 of 6
Look something up
Not attempted
Why this matters: Looking a value up beats scrolling, and it keeps working when the data changes.
First, the formula: XLOOKUP
XLOOKUP finds a value in one column and returns the matching value from another. Three parts: what to find, where to look, what to return.
The pattern
=XLOOKUP(find, look_in, return_from)
A worked example
=XLOOKUP("Cara",D2:D13,F2:F13)
Returns the first sale by Cara. It stops at the first match, so it suits unique keys.
Your task
What did Dev's first sale bring in? Find Dev in the Rep column and return the matching value.
fx
Hint
Find it in one column, return from another.
XLOOKUP takes what to find, where to look, then what to return.
What this tells you: Dev's first sale was £180. XLOOKUP returns the first match only.
6
Exercise 6 of 6
Answer a business question
Not attempted
Why this matters: A number on its own rarely answers the question. A test turns it into a decision.
First, the formula: IF and ROUND
IF tests something and returns one of two answers. ROUND cuts a number to a set number of decimal places. Both are often wrapped around another formula.
The pattern
=IF(test, "yes answer", "no answer")
=ROUND(number, places)
