Spreadsheet Formulas Every Beginner Needs
Budgets, grades, sign-up lists: a few formulas turn a spreadsheet from a table into a calculator. These work the same in Excel and Google Sheets.
Cells and references
Every cell has an address: its column letter plus its row number. B3 is column B, row 3.
Every formula starts with =. Refer to cells by address and the result updates automatically when they change.
=B2 * C2 multiply two cells
=B2 + B3 + B4 add three cells
=(B2 - B3) / B3 brackets work like in normal mathA range is a block of cells written with a colon: B2:B10 means everything from B2 down to B10.
The five functions you'll use most
| Formula | What it does |
|---|---|
=SUM(B2:B10) | Adds them up |
=AVERAGE(B2:B10) | The mean |
=MIN(B2:B10) / =MAX(…) | Smallest / largest |
=COUNT(B2:B10) | How many cells contain numbers |
=COUNTA(B2:B10) | How many cells aren't empty (text too) |
Shortcut: select a column of numbers and look at the bottom-right of the window. Both Excel and Google Sheets show the sum, average and count without any formula.
Making decisions with IF
=IF(B2 >= 50, "Pass", "Fail")
=IF(C2 > D2, "Over budget", "OK")Text answers go in quotes. Related favourites: SUMIF (add only rows that match, like all “Food” spending) and COUNTIF (count matches).
Copying formulas and the $ sign
Drag a formula down (using the small square at the bottom-right of the cell) and its references shift automatically: =B2*C2 becomes =B3*C3, and so on. That's a relative reference.
To keep a reference fixed, like a tax rate in one cell, add $: =B2*$F$1. That's an absolute reference. Press F4 in Excel (⌘ T on Mac Excel) to add the $ signs for you.
Edit the numbers in column B, then pick a formula.
| A | B |
|---|