Excel Tutorials
Every cell has an address, and every formula depends on referencing the right one correctly. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
A cell address is the combination of a column letter and row number that identifies exactly one cell, like B3 for column B, row 3. A cell reference is how you point to that address inside a formula, so instead of typing a number directly you type the cell's address and the formula pulls whatever value is there.
References are what make Excel powerful: change the value in the referenced cell, and every formula pointing to it updates automatically, without you touching the formula itself.
Step-by-step, matching the video above.
The Name Box in the top-left corner always shows the address of whichever cell is selected, letter first (column), then number (row).
Type = then click the cell you want to reference, or type its address directly, like =B3. Excel pulls in whatever value is currently in that cell.
A plain reference like B3 is relative, meaning it shifts automatically if you copy the formula to another cell. This is the default and most common type.
Typing $B$3 makes both the column and row absolute, so the reference stays fixed no matter where you copy the formula. Press F4 after clicking a reference to cycle through reference types automatically.
Type the sheet name followed by an exclamation point before the cell address, like Sheet2!B3, to pull a value from a different tab.
$B3 locks the column but lets the row shift, B$3 locks the row but lets the column shift. Useful in a table where you're copying a formula both across and down, like a multiplication table or a rate lookup.
The Name Box (top-left, next to the formula bar) isn't just a display, you can type into it too.
Click the Name Box, type an address like Z500, and press Enter. Excel jumps there instantly, faster than scrolling through a large sheet.
Select a range, click the Name Box, type a name like SalesData, and press Enter. You can now type =SUM(SalesData) instead of remembering the exact cell addresses.
This almost always means a reference that should have been absolute ($B$3) was left relative and shifted when the formula was copied.
A reference like B:B works but can slow down large workbooks. Reference just the rows you actually need, like B2:B500, when possible.
A reference like B3 only looks at the current sheet. Referencing another tab without Sheet2! in front of it will point to the wrong cell or throw an error.
$B3 vs B$3 are easy to swap by accident. Remember: the dollar sign locks whatever comes right after it, column letter or row number.
Every reference has a purpose. Updoot keeps it connected for you.
Free 14-day trial. No credit card required.
A cell address identifies one specific cell using its column letter and row number, like B3.
A relative reference (B3) shifts when you copy a formula to a new cell. An absolute reference ($B$3) stays fixed no matter where the formula is copied.
Click on the reference inside a formula and press F4 to cycle through relative, absolute and mixed reference styles automatically.
Type the sheet name followed by an exclamation point, then the cell address, like Sheet2!B3.
This is usually a reference that should have been locked with dollar signs ($B$3) left as a plain relative reference, so it shifted when copied.
A reference with only one dollar sign, like $B3 or B$3, that locks either the column or the row but lets the other side shift when copied.
Click the Name Box in the top-left corner, type the cell address, and press Enter to jump there instantly.
Every business starts with a spreadsheet. Updoot is where you scale past it, with data that stays connected automatically.
Start Your Free Trial →