Five formulas cover most of what entry-level office work needs. Knowing them is often the difference between an hour of adding up and three seconds.
Every formula starts with =. That is how the spreadsheet knows you want a calculation rather than text.
=SUM(B2:B20) adds everything from B2 down to B20. =AVERAGE(B2:B20) gives the average. =COUNT(B2:B20) counts how many cells hold numbers. =MAX and =MIN give the biggest and smallest.
The colon means through, so B2:B20 is every cell from B2 through to B20.
You need the total of 200 rows of sales.
What happens: It takes twenty minutes, you will make at least one error, and you have to start again completely when one number changes.
What happens: Three seconds, no errors, and it updates itself the moment any number changes. This one formula is most of the value of spreadsheets.
My formula shows #### or an error
#### just means the column is too narrow, so widen it by dragging the edge. Errors starting with # such as #VALUE or #REF usually mean the formula is pointing at text where it expected a number, or at a cell that has been deleted.
Do I have to type every formula?
No. Write it once, then drag the small square at the corner of the cell downwards and it repeats for every row, adjusting automatically. This is how one formula fills a whole column in a second, and it is the moment spreadsheets start feeling powerful.
Before you move on
- Every formula starts with =
- Learn SUM first, it covers most needs
- Drag the corner square to copy a formula down
Check what you know
4 questions. Have a look at what this lesson asks.
Sign in to take this quiz
Reading is free and always will be. Answering counts towards your progress, your streak and your certificate, so we need to know who you are.
Sign in or create a free accountIt takes a minute and costs nothing.