The moment you write numbers in a list, your eye looks for the total. Formulas exist for exactly that: you tell the table "add up these cells" and it does the math for you, keeping the result current as the numbers change. If you have never written a formula before, don't worry, we start from scratch. And if this is your first time with tables, take a look at the Add and edit tables guide first.
1
Get to know formulas: the = rule
Writing a formula is far easier than you think. If what you type into a cell starts with
=, the table treats it as a formula and does the math. If it doesn't start with =, whatever you typed stays plain text. That's the only rule.To see the difference, type the same thing two ways into two cells: the cell where you typed
2+2 shows the text "2+2"; the cell where you typed =2+2 shows 4. Click the cell and you'll see the formula itself in the formula bar above the table, where you can edit it too.A2fx=2+2
A
B
1
2+2
2
4
Same content: plain text without
=, a calculation with =2
Add up your numbers: SUM
The formula that adds up is called SUM. Picture a small expense table: column A holds Groceries, Fuel and Coffee; column B holds 25, 40 and 8. To see their total, you can type this into B4, right below them:
=SUM(B1:B3)The colon in the middle (
:) means "everything from here to there". So B1:B3 covers every cell between B1 and B3. Press Enter and B4 shows 73.You don't have to memorize addresses. Type
=SUM( and stop, then click cell B1 and drag down to B3: the range writes itself into the formula and the cells you picked light up. Close the parenthesis, press Enter, done.B4fx=SUM(B1:B3)
A
B
1
Groceries
25
2
Fuel
40
3
Coffee
8
4
Total
=SUM(B1:B3)
B4fx=SUM(B1:B3)
A
B
1
Groceries
25
2
Fuel
40
3
Coffee
8
4
Total
73
On the left the cells highlighted as the formula is typed, on the right the total after Enter
3
Formulas stay alive
The lovely thing about a formula is that writing it once is enough. Change the 40 in B2 to 45 later and the total below becomes 78 by itself; no need to redo the math. Even if you add or delete rows, the formula follows the cells it points to.
If you type something wrong, the table doesn't leave you stranded. Misspell a formula name, for instance, and a short code like
#NAME? appears in the cell; select that cell and the formula bar tells you why: "Unknown function name: check the spelling (e.g. SUM)". Fix the formula and the result comes straight back.B2fx40
A
B
1
Groceries
25
2
Fuel
40
3
Coffee
8
4
Total
73
B2fx45
A
B
1
Groceries
25
2
Fuel
45
3
Coffee
8
4
Total
78
The total updates by itself the moment you change the number in B2
4
All formulas
The table has 24 formulas and they all work the same way:
=, the formula's name, then cells inside parentheses. Below you can see at a glance what each one does.Every name works in English, and so does its equivalent in the other app languages. The
Σ menu in the toolbar writes the English name into the cell.Maths
SUMAdds up the cells you pickAVERAGEGives the average of the numbersMINFinds the smallest numberMAXFinds the largest numberMEDIANGives the middle valueROUNDRounds to the decimal place you wantABSDrops the minus signSQRTTakes the square rootPOWERRaises a number to a powerCounting and conditions
COUNTCounts how many cells hold numbersCOUNTACounts how many cells are filledCOUNTIFCounts the ones matching a conditionSUMIFAdds up only the ones matching a conditionIFWrites one of two results based on a conditionText
CONCATENATEJoins pieces of text end to endLENCounts how many characters there areUPPERTurns text into upper caseLOWERTurns text into lower caseTRIMRemoves spaces at the start and endLEFTTakes characters from the startRIGHTTakes characters from the endMIDTakes a piece from the middleDate
TODAYWrites today's dateNOWWrites the date and timeFor the days you don't feel like writing formulas, there's the
Σ (Functions) button on the right of the toolbar. Pick Sum, Average, Count, Max or Min from the menu and the formula gets written for you.The Functions menu that opens from the Σ button; the grey name on the right is the formula that gets written
When a formula separates more than one value, leave a space after the comma:
=IF(B4>50, "Over", "Fine"). Decimal numbers can be written with a comma too, so that space keeps the two from getting mixed up. Working with cell addresses, that confusion never comes up anyway.Frequently asked questions
How do you write a formula?
You start by typing
= into a cell. Then you write the formula's name and, in parentheses, the range of cells to calculate, and press Enter, for example =SUM(B1:B3). Anything that doesn't start with = stays plain text.Do the English and localized names differ?
No, they do the same job. Each formula has a name in every app language and they all resolve to the same function, so use whichever sticks in your head.
Do I have to type addresses by hand?
You don't. Type
=SUM( and stop, then click the first cell and drag to the last one; the range writes itself into the formula and the cells you picked light up. Close the parenthesis, press Enter, done.My formula returned an error, what should I do?
The short code in the cell tells you why:
#NAME? means the formula name is misspelled, #DIV/0! means dividing by zero, and #REF! means a row or column the formula pointed to was deleted. Select the cell to read the explanation in the formula bar; fix the formula and the result comes back.