Start Here
A formula is an instruction that tells Excel to calculate something. Instead of typing the answer into a cell, you type the sum, and Excel works it out and keeps it up to date. This is what makes a spreadsheet a living document rather than a static page of numbers: change a number and every total that depends on it updates by itself.
Every formula begins with an equals sign, =. That = is how Excel knows you want a calculation rather than plain text. A formula can mix three ingredients: numbers you type directly, cell references that point at other cells, and operators that say what to do.
The real power is in cell references. =A1+B1 does not add "10 and 5". It adds whatever happens to be in A1 and B1 right now. Change A1, and the answer updates itself.
Your First Formula
Here is the whole move, start to finish:
- Click the cell where you want the answer to appear.
- Type
=to start the formula. - Build the calculation using numbers, cell references and operators, for example
=B2+B3+B4. You can click a cell instead of typing its reference; Excel inserts it for you. - Press Enter. The cell shows the result; click back on it and the Formula Bar shows the formula behind it.
The operators
An operator is the symbol that says what to do. There are four you will use constantly, plus two worth recognising:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Operator | Does | Example | Result |
| 2 | + | Add | =5+3 | 8 |
| 3 | - | Subtract | =10-4 | 6 |
| 4 | * | Multiply | =6*7 | 42 |
| 5 | / | Divide | =20/4 | 5 |
Note that multiply is a star (*), not an x, and divide is a slash (/). Here is a formula that adds three cells together; copy it and try it in your own sheet:
=B2+B3+B4If a cell literally displays =A1+A2 as text rather than working it out, the cell is probably formatted as Text. Set it back to General (Home tab, Number group), then re-enter the formula. More on number formats in Session 3.
See every formula at once: Ctrl + `
Normally a cell shows its answer, and you have to click each one to see the formula behind it in the Formula Bar. Sometimes you want to see all the formulas on a sheet at once, to check your work or understand a sheet someone else built. There is a shortcut for exactly that.
Press Ctrl + ` (the backtick or "tilde" key, top-left of the keyboard, just under Esc) to switch Show Formulas on. Every cell flips from showing its result to showing the formula inside it. Press Ctrl + ` again to switch it back. It is a toggle, so the same keys turn it on and off. (You can also find it on the Formulas tab as the "Show Formulas" button.)
Ctrl + ` is the fastest way to scan a sheet for mistakes: with every formula on show, you can read straight down a column and spot the one cell where a number was typed by hand instead of a formula, or where a reference drifted. Toggle it off again when you are done.
The Order of Operations (BODMAS)
Excel does not simply work left to right. It follows the standard maths order you may know as BODMAS (or PEMDAS), the same rule taught in school:
- Brackets first
- Orders (powers, like squared)
- Division and Multiplication, left to right
- Addition and Subtraction, left to right
So in =A1+A2*A3, Excel multiplies A2 by A3 first, then adds A1. If you want the addition to happen first, wrap it in brackets. These two formulas give different answers:
=A1+A2*A3=(A1+A2)*A3If A1 is 10, A2 is 4 and A3 is 2: the first gives 18 (it does 4×2 first, then adds 10), and the second gives 28 (it adds 10+4 first, then times 2).
Brackets cost nothing and make your intention obvious to anyone reading the sheet later, including future you. If you are unsure how Excel will read a formula, wrap the part you want done first in brackets.
Copying Formulas: the Dollar-Sign Lock
Here is a puzzle that trips up almost everyone at first. You write a perfect formula, copy it down a column, and some answers come out wrong. Nothing is broken; Excel is doing exactly what you told it. You just need to know the difference between two kinds of reference.
- A relative reference like
B2is a direction, not a fixed address. Copy a formula down a row andB2becomesB3, thenB4. This is usually exactly what you want, so each row uses its own number. - An absolute reference like
$E$1is a fixed address. The dollar signs pin it in place so it never moves, no matter where you copy the formula. Use this for a value every row must share, like a single rate or total.
The $ is the whole trick: a dollar sign before the column letter locks the column, and a dollar sign before the row number locks the row. You do not have to type the dollar signs by hand: while editing a reference, press F4 to cycle through A1 → $A$1 → A$1 → $A1.
Worked example: GST on a list of prices
Suppose the GST rate (10%) lives in cell E1, and your prices run down column B. In C2 you want the GST amount. Lock the rate so it does not drift when you copy down:
=B2*$E$1B2 is relative, so copying down gives B3, B4, B5, each row using its own price. $E$1 is absolute, so it always points at the single GST rate. Copy C2 down and every row is correct; change the rate in E1 once, and the whole column recalculates.
A forgotten dollar sign. If a copied formula returns 0, blanks, or wildly wrong numbers, check whether a reference that should have been locked has drifted. The fix is almost always a missing $. Lock the shared value before you copy.
The Five Everyday Functions
A function is a ready-made formula that Excel has already written for you. Rather than typing =B2+B3+B4+B5+B6+B7, you hand a function a range of cells and let it do the work: =SUM(B2:B7). Every function follows the same simple shape: =FUNCTION(arguments), where the arguments go inside the brackets, usually a range like B2:B7. The colon means "through", so B2:B7 is "B2 through B7".
These five cover the bulk of everyday work. Learn them and you can already answer most questions a spreadsheet of numbers will ever ask:
| A | B | |
|---|---|---|
| 1 | Function | What it does |
| 2 | =SUM(B2:B7) | Adds all the numbers in a range |
| 3 | =AVERAGE(B2:B7) | Works out the mean (the average) |
| 4 | =COUNT(B2:B7) | Counts how many cells hold numbers |
| 5 | =MAX(B2:B7) | Finds the largest value |
| 6 | =MIN(B2:B7) | Finds the smallest value |
To write one: click the result cell, type =, the function name and an opening bracket (for example =SUM(), select the range by dragging across the cells, type the closing bracket ) and press Enter.
COUNT and COUNTA, a useful pair
COUNT counts only cells that contain numbers; text and blanks are ignored. COUNTA counts every non-empty cell, including text. So to count how many people are in a contact list (names are text), use COUNTA. To count how many amounts were entered in a budget column, use COUNT.
=COUNTA(A2:A50)If you know what you want but not the formula for it, Microsoft Copilot (on newer, online versions of Excel) can suggest one from a plain-English description, and explain or fix a formula too. You do not need to know the function's name. See Using Copilot in Excel for how to ask well, with example prompts. Always check the result it gives you.
AutoSum and the Status Bar
AutoSum: the one-key total
For SUM especially, you rarely need to type anything. Click the empty cell below a column of numbers and press Alt + = (or click AutoSum on the Home tab, far right). Excel guesses the range, shows it to you, and you press Enter to accept. The AutoSum dropdown also offers Average, Count, Max and Min.
The status bar: the fastest calculator in Excel
You do not even need a formula for a quick check. Select a range of numbers and glance at the status bar along the very bottom of the window: Excel instantly shows their Sum, Average and Count, with nothing typed at all. It is the quickest way to sanity-check a column.
Functions use a cell's underlying value, not how it looks. So SUM happily adds cells that are formatted as currency, dollar signs and all. Formatting changes the costume, not the number; we look at that properly in Session 3.
Worked Example: a Stock List
Here is Dave's T-Shirt Shoppe, a small retail stock list. We want the profit on each line, which is (price minus cost) times the number of units, and then a grand total. The profit column uses a formula, and the total uses SUM.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Wholesale | RRP | Units | Profit |
| 2 | Widgets | $5.00 | $10.00 | 10 | =(C2-B2)*D2 |
| 3 | T-shirts | $3.89 | $25.00 | 10 | =(C3-B3)*D3 |
| 4 | Jumpers | $9.80 | $35.00 | 10 | =(C4-B4)*D4 |
| 5 | Hoodies | $13.40 | $40.00 | 10 | =(C5-B5)*D5 |
| 6 | Total | =SUM(E2:E5) |
The profit formula for the first line, with brackets so the subtraction happens before the multiply:
=(C2-B2)*D2You only type it once in E2, then double-click the fill handle to copy it down the whole column (remember that trick from Session 1). Each row's references shift to its own line. Then the grand total in E6:
=SUM(E2:E5)And to find the most and least profitable lines, and the typical profit:
=MAX(E2:E5)=AVERAGE(E2:E5)Try It at the Keyboard
Open the starter workbook for this session, which has the stock list and a small weekly budget ready to go.
Total a weekly budget, then find the extremes
- In the budget sheet, click the empty cell at the bottom of the amounts column and press Alt + = to drop in a SUM. Press Enter and read the total.
- In a spare cell, type
=AVERAGE(then drag across the same amounts, close the bracket and press Enter. That is your typical weekly amount. - Do the same with
=MAX(...)and=MIN(...)to find your biggest and smallest weeks. - On the stock sheet, type the profit formula
=(C2-B2)*D2in E2, then double-click the fill handle to copy it down. Watch each row calculate its own profit. - Change a price and watch the profit and the grand total update on their own. That is the spreadsheet earning its keep.
When you are happy, compare your sheet with the solution workbook for this session to check your formulas match.
Knowledge Check
A few quick questions to see what has landed. Have a think first, then click "reveal". No mark, no rush.
Q1What single character must every formula start with, and why does it matter?reveal
The equals sign, =. It is how Excel knows you want a calculation rather than plain text. Without it, Excel just stores what you typed as words.
Q2What does =(A1+A2)*A3 work out first, and how is that different from =A1+A2*A3?reveal
Because of brackets first (the B in BODMAS), =(A1+A2)*A3 adds A1 and A2 together first, then multiplies by A3. Without the brackets, =A1+A2*A3 multiplies A2 by A3 first, then adds A1, so the two give different answers.
Q3You copy =B2*E1 down a column to work out GST, but the lower rows go wrong. What is the likely cause and fix?reveal
The rate reference E1 drifted to E2, E3 and so on as you copied down, so the lower rows multiplied by empty cells. Lock it as an absolute reference: =B2*$E$1. The $ signs pin E1 in place. Press F4 while editing the reference to add them quickly.
Q4You want to count how many people are in a contact list, where the names are text. SUM, COUNT or COUNTA?reveal
COUNTA. COUNT only counts cells that hold numbers, so it would ignore the names. COUNTA counts every non-empty cell, including text, which is what you want for a list of names.
Q5What is the quickest way to total a column of numbers without typing a formula by hand?reveal
Click the empty cell just below the column and press Alt + = (AutoSum). Excel guesses the range and writes the SUM for you; press Enter to accept. For an even quicker check with no formula at all, just select the numbers and read the total on the status bar at the bottom.