- How to write a formula that refers to other cells
- The four building blocks of a formula
- How Excel names cells and ranges
- Relative, absolute and mixed references: what the dollar signs do
- Two reference styles you'll meet later
- The 10 functions Microsoft features first
- How Microsoft groups the rest
- My verdict
- Frequently asked questions
Every Excel formula starts with an equal sign, and it's built from up to four things: functions, cell references, constants and operators. Type =, click a cell, type an operator such as -, click another cell and press Enter, and you've written your first formula.
The part that catches most beginners isn't writing a formula. It's copying one. Excel quietly changes the cell references when you copy, unless you've told it not to, and that's what the dollar signs in $A$1 are for. I'll cover that below, once the basics are in place.
How to write a formula that refers to other cells
Microsoft's method is the same in every recent version of Excel:
- Select a cell and type
= - Click a cell, or type its address, then type an operator such as
+,-,*,/or^ - Click the next cell.
- Press Enter.
- Select the cell again to read the formula in the Formula Bar.
The result shows in the cell. The formula itself only shows in the Formula Bar when that cell's selected, which is why a sheet full of numbers can hide a lot of working.
Using a built-in function is almost the same. Select an empty cell, type = and the function name (for example =SUM), type an opening bracket, select the range, type the closing bracket and press Enter. My guide to adding up in Excel walks through SUM in more detail.
The four building blocks of a formula
A formula can contain any or all of these, and Microsoft gives a small example of each:
| Element | Example | What it does |
|---|---|---|
| Function | PI() | Returns the value of pi, 3.142 and so on |
| Reference | A2 | Returns the value in cell A2 |
| Constant | 2 | A number or text typed straight in |
| Operator | ^ and * | ^ raises to a power, * multiplies |
A constant is a value that isn't calculated and always stays the same: Microsoft's examples are the number 210 and the text "Quarterly Earnings". A value that comes out of an expression isn't a constant. In practice, the fewer constants you bury inside formulas, the easier your sheet is to update. Put the number in a cell and refer to it instead.
How Excel names cells and ranges
Excel uses the A1 reference style by default. Columns are letters, from A through XFD (16,384 columns), and rows are numbers, from 1 through 1,048,576. A reference is just the column letter followed by the row number. Here's Microsoft's cheat sheet:
| To refer to | Type |
|---|---|
| The cell in column A, row 10 | A10 |
| Column A, rows 10 to 20 | A10:A20 |
| Row 15, columns B to E | B15:E15 |
| All of row 5 | 5:5 |
| Rows 5 to 10 | 5:10 |
| All of column H | H:H |
| Columns H to J | H:J |
| A block from A10 to E20 | A10:E20 |
To point at another worksheet in the same workbook, put the sheet name first and an exclamation mark after it: =AVERAGE(Marketing!B1:B10). If the sheet name has spaces or numbers, wrap it in apostrophes, as in ='January Revenue'!A1.
Relative, absolute and mixed references: what the dollar signs do
This is the bit worth slowing down for. A relative reference such as A1 changes when you copy the formula, because Excel stores it as a position relative to the cell holding the formula. Copy it one row down and it points one row down. That's usually what you want, until it isn't.
An absolute reference such as $A$1 always points at the same cell, wherever you copy it. A mixed reference locks only one half: $A1 locks the column and A$1 locks the row.
| Reference type | Looks like | What happens when you copy the formula |
|---|---|---|
| Relative | A1 | Shifts with the new position |
| Absolute | $A$1 | Always points at the same cell |
| Mixed (column locked) | $A1 | Column stays, row shifts |
| Mixed (row locked) | A$1 | Row stays, column shifts |
Here's the everyday case. You've got a target in one cell and a column of figures to compare against it. Lock the target with an absolute reference and leave the figures relative, and you can copy one formula down the whole column. Forget the dollar signs and every row after the first compares against an empty cell.
Two reference styles you'll meet later
A 3-D reference adds the same cells across several sheets. =SUM(Sheet2:Sheet6!A2:A5) adds A2 to A5 on every worksheet from Sheet2 to Sheet6. Insert or copy a sheet between those two and its values are included; delete or move one out and they're removed. Microsoft notes 3-D references can't be used in array formulas.
The R1C1 style numbers both rows and columns, so R2C2 is an absolute reference to row 2, column 2. Microsoft says it's useful for computing positions in macros, and you turn it on or off in Excel's options under Working with formulas. Most people never need it.
Another page that helps here: this walkthrough of how to use COUNTIFS in Excel.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
The 10 functions Microsoft features first
Microsoft's function library is huge, but it puts ten functions at the top of its list. They're a sensible learning order for anyone starting out:
| Function | What it does |
|---|---|
| SUM | Adds the values in cells |
| IF | Returns one value if a condition is true, another if it's false |
| SUMIFS | Adds cells that meet multiple criteria |
| XLOOKUP | Searches a range and returns the matching item |
| COUNTIFS | Counts cells that meet multiple criteria |
| COUNT | Counts how many numbers are in the list |
| LET | Assigns names to calculation results |
| FILTER | Filters a range by criteria you define |
| UNIQUE | Returns a list of unique values |
| TEXTBEFORE | Returns text before a given character or string (TEXTAFTER returns the text after it) |
If you only learn one lookup function, make it XLOOKUP. It's on Microsoft's featured list for a reason.
How Microsoft groups the rest
Beyond the featured ten, Microsoft sorts worksheet functions into categories: Compatibility, Cubes, Database, Date and time, Engineering, Financial, Information, Logical, Lookup and reference, Math and trigonometry, Statistical, Text and Web. There are also user-defined functions installed by add-ins, which aren't available in Excel for the web.
One category deserves a warning. Compatibility functions are older functions that newer versions replaced with ones that have improved accuracy and better names. You can still use them if you share files with people on older versions, but Microsoft says to use the new functions if you don't need that backward compatibility. If a tutorial points you at a function in that group, look for its replacement first.
When you're ready to speed things up, my list of Excel shortcuts covers the keys that save the most clicks.
My verdict
You don't need to memorise hundreds of functions. You need five habits: start every formula with an equal sign, click cells instead of typing addresses, understand the dollar signs before you copy anything, read the Formula Bar when a result looks odd, and use the function library instead of guessing names. Get those right and the ten featured functions will take you further than most people ever go.
To see how it all fits together, read how to calculate CAGR in Excel.
Frequently asked questions
What do all Excel formulas start with?
An equal sign. Microsoft says formulas in Excel always begin with it.
What does the dollar sign mean in an Excel formula?
It locks part of a reference. $A$1 always points at the same cell when you copy the formula; $A1 locks the column and A$1 locks the row.
What's the difference between a formula and a function?
A formula is anything you type after the equal sign. A function, such as SUM or IF, is a built-in calculation you can use inside a formula.
How do I refer to a cell on another sheet?
Put the sheet name, an exclamation mark, then the range, as in =AVERAGE(Marketing!B1:B10). Wrap sheet names with spaces in apostrophes.
Which Excel functions should a beginner learn first?
Microsoft features SUM, IF, SUMIFS, XLOOKUP, COUNTIFS, COUNT, LET, FILTER, UNIQUE and TEXTBEFORE at the top of its function list.
