Webbo3 Data Analysis Bootcamp · Excel Module · Lesson 5

Cell Referencing in Excel: Relative, Absolute, and Mixed References Explained

A hands on lesson on how Excel decides what happens to a cell reference when you copy a formula, and exactly when to lock it with a dollar sign.

You now know nine functions and how to write formulas. But there is one concept sitting underneath every single formula you will ever build, and if you do not understand it properly, copying a formula down a column or across a row will quietly give you wrong numbers without throwing any error at all. That concept is cell referencing. Excel actually offers four distinct ways to refer to a cell, and the only difference between them is what happens to that reference the moment you copy the formula elsewhere. Get this one idea solid, and entire categories of confusing formula mistakes disappear permanently.

1. Relative References: How They Shift When Copied

Every cell reference you type in Excel is relative by default, meaning it has no dollar signs at all, for example simply A2 or B4. A relative reference does not actually store a fixed address. It stores a position relative to the cell the formula lives in. When you copy that formula to a different cell, Excel recalculates the reference based on the new position, preserving the same relative relationship.

A concrete example. Suppose column B holds the unit price of products and column C holds the quantity sold, and you want column D to calculate the total for each row. In cell D2, you type =B2*C2 and press Enter. Now select D2, copy it, and paste it into D3 through D8. Excel does not paste the literal formula =B2*C2 into every cell. Instead, it shifts the references to match each new row, so D3 becomes =B3*C3, D4 becomes =B4*C4, and so on down to D8. This is exactly the behaviour you want here, since each row's total should multiply that row's own price by that row's own quantity.

The rule to internalise. If you copy a formula one column to the right, every relative reference inside it shifts one column to the right. If you copy it three rows down, every relative reference shifts three rows down. The shift always matches the direction and distance of the copy, whether you do it by dragging the fill handle, using Ctrl + C and Ctrl + V, or using Ctrl + D to fill down. Relative references are correct for the vast majority of formulas you will write, which is exactly why Excel defaults to them.

2. Absolute References: Locking With the Dollar Sign

An absolute reference is locked completely, both the column letter and the row number, by placing a dollar sign directly in front of each part, for example $B$1. No matter where you copy that formula, an absolute reference never changes. It always points to that exact same cell.

A concrete example. Suppose cell B1 holds a fixed discount rate of 15 percent, written as 0.15, and column A from A3 to A7 lists five different product prices: 100, 250, 400, 75, and 320. In cell B3 you type =A3*$B$1 and press Enter, which calculates the discount amount for the first price. Copy B3 down through B7. Because A3 is a relative reference, it correctly shifts to A4, A5, A6, and A7 as you copy down, picking up each different price. But because $B$1 is absolute, it stays locked on cell B1 in every single copied formula, so all five calculations correctly use the same 15 percent rate. If you had left B1 as a plain relative reference instead, copying the formula down would have shifted it to B2, B3, B4, and so on, which are all empty cells, and your results would have silently broken.

When to use an absolute reference. Anytime a formula needs to refer to one single constant value that every other copy of the formula should also use, for example a tax rate, an exchange rate, a fixed bonus percentage, or a target figure sitting in one specific cell, that reference should be absolute. A useful test: if changing that one cell should immediately update every formula across the whole sheet that depends on it, it needs a dollar sign in front of both the column and the row.

3. Mixed References: Locking the Row or the Column Only

A mixed reference is the middle ground. Only one part of the address gets the dollar sign, leaving the other free to shift. There are two versions:

$A1 locks the column but leaves the row free. Copy this formula down, and the row number changes normally. Copy it across, and the column letter stays fixed on A no matter how far you drag.

A$1 locks the row but leaves the column free. Copy this formula across, and the column letter changes normally. Copy it down, and the row number stays fixed on 1 no matter how far you drag.

The clearest real example: a multiplication grid. Imagine you are building a small reference table. Down column A, starting at A2, you list the numbers 1 through 10 vertically. Across row 1, starting at B1, you list the numbers 1 through 10 horizontally. You want every cell inside the grid to show the product of its row label multiplied by its column label, the same logic as a times table. In cell B2, you would type =$A2*B$1. Look closely at what each part does. The $A in $A2 locks the column to A, so as you copy the formula across to C2, D2, E2, and onward, it always keeps pulling from column A, never drifting to B or C. The 2 in $A2 has no dollar sign, so as you copy the formula down to B3, B4, and so on, it correctly updates to A3, A4, matching each new row. Meanwhile B$1 works the opposite way: the $1 locks the row to 1, so copying down never moves it off row 1, while the column letter B is free to shift to C, D, E as you copy across. Type this one formula into B2, copy it across the entire top row of the grid, then copy that entire row down through every remaining row, and the whole multiplication table fills in correctly from a single formula.

When to use a mixed reference. Anytime your formula needs to be copied in two directions at once, both across and down, and one axis of your data should stay fixed while the other axis should move. Budget grids with months running across the top and expense categories running down the side are a common real example in this bootcamp's financial modelling work, where row 1 might hold fixed monthly targets and column A might hold fixed category names, each needing a different locking pattern.

4. The F4 Shortcut: Switching Between All Four Types Instantly

You never need to type dollar signs manually. Click into a cell that contains a formula, or double click it, or select it and press F2 to enter edit mode, then click your cursor anywhere inside the specific cell reference you want to change. It does not need to be highlighted, just placed somewhere inside that reference. Press F4, and Excel cycles through all four reference types in this exact order:

Press once: A1 becomes $A$1, fully absolute, both column and row locked.

Press twice: becomes A$1, mixed, row locked, column free.

Press three times: becomes $A1, mixed, column locked, row free.

Press four times: returns to A1, fully relative again, completing the cycle.

If your formula contains several different references, click into the specific one you want to change before pressing F4, since the shortcut only acts on whichever reference your cursor is currently sitting inside. On most Mac keyboards, the same toggle requires Fn + F4 instead of F4 alone, because the bare function keys are usually mapped to media controls by default.

5. Practical Use Cases You Will Actually Run Into

Calculating commission against a single fixed rate. If column D holds total sales per salesperson and G2 holds a single commission rate that applies to everyone, the formula in E2 would be =D2*$G$2, copied down through every salesperson's row. D2 shifts naturally with each row, while $G$2 stays anchored on the one cell holding the rate.

Looking up a value where the lookup range must never shift. Functions like VLOOKUP and XLOOKUP, which you will meet in a later lesson, almost always need their lookup range written as an absolute reference, for example $A$2:$B$50. Without locking that range, copying the lookup formula down a column would slowly shift the search range away from your actual data with every row, eventually searching the wrong cells entirely or returning errors.

A currency conversion column. If column C holds amounts in naira and a single exchange rate sits in F1, converting to dollars in column D needs the formula =C2/$F$1 copied down the whole column. Every row correctly divides its own naira amount, while every formula keeps referencing the same single exchange rate cell, so updating F1 once instantly recalculates every converted value below it.

The diagnostic habit worth building now. Whenever a formula you copied down or across returns results that look obviously wrong, for example a string of zeros, identical repeated values, or numbers that clearly do not match the pattern of the row above, click into one of the broken cells and look directly at the Formula Bar. In the vast majority of cases, you will find a relative reference that should have been absolute, or the opposite, and the fix is simply clicking into that reference and pressing F4 the correct number of times.

Using AI to Move Faster in Excel

Cell referencing is precisely the kind of concept where understanding the logic yourself matters more than usual, because a wrong dollar sign produces a wrong number with no warning, no red squiggly line, nothing. AI inside Excel is genuinely helpful here, but only once you can read its output and judge whether the references it chose actually make sense.

1. Ask Copilot to build the formula, then check its references specifically.
Instead of working out the dollar sign placement yourself for a new formula, describe the task to Copilot, for example "Calculate commission for each salesperson in column D, using the fixed rate in cell G2." Copilot will typically generate a formula with the correct mix of relative and absolute references already in place. Your job is then to click into the suggested formula and confirm exactly which references are locked and why, the same way you would check any formula you wrote yourself.

2. Use AI to diagnose a broken copied formula instead of hunting manually.
If a formula returns wrong or suspicious values after being copied down a column, select the cell and ask Copilot something like "Why does this formula give different results in row 5 compared to row 2" or "Check whether this formula's references are correct after copying." This is often faster than manually clicking through ten or fifteen cells comparing Formula Bar contents by eye.

3. Ask for an explanation when a mixed reference confuses you.
Mixed references are the part of this lesson most people need to revisit more than once. If you are unsure why a formula uses $A2 instead of A$2 in a specific case, ask Copilot to walk through what each part of that specific reference is doing in that specific formula. A concrete explanation tied to your actual sheet sticks better than a generic rule memorised in isolation.

4. Never assume the locking is correct just because the formula ran without an error.
This is the single most important habit from this entire lesson. A formula with the wrong reference type almost never throws a visible error. It simply calculates a wrong number quietly and confidently. Whether you wrote the formula yourself or AI suggested it, always copy it across the full range you intend to use it on, then manually spot check several of the resulting cells against numbers you can verify by hand, before trusting that range anywhere else in your workbook.

A habit worth carrying forward from this lesson: before you copy any formula across more than two or three cells, pause and ask which references inside it genuinely need to move, and which ones genuinely need to stay fixed. That one second of thinking, repeated consistently, is what separates a spreadsheet you can trust from one that quietly lies to you.

Next lesson: logical formulas, starting with IF, AND, and OR.