Absolute vs relative references: when to use $ in formulas

You write a formula once, copy it down a column, and everything works. Until it doesn’t. One row returns the right number, the next row returns zero, and the row after that pulls from a completely empty cell. The formula looked correct, so what went wrong?

The answer is almost always about how Excel handles cell references when you copy a formula. Understanding the difference between relative, absolute, and mixed references is one of those foundational skills that makes everything else in Excel click. Once you get it, you stop debugging broken formulas and start building sheets that work on the first try.

Relative references: the default behavior

Every cell reference you type is relative by default. When you write =B2*C2 in cell D2, Excel does not actually store “multiply B2 by C2.” It stores something closer to “multiply the cell two columns to my left by the cell one column to my left.”

That is why copying the formula down works. Each row adjusts the reference to match its own position. Here is a small price list where every Total cell uses the same pattern, and each one automatically points to its own row:

Click on any cell in the Total column to see its formula. D2 contains =B2*C2, D3 contains =B3*C3, and so on. Excel shifted each reference by one row when the formula was copied down. For most calculations, this is exactly the behavior you want. You write one formula, drag it down, and the whole column fills in correctly.

The pattern breaks, however, when your formula needs to reference a cell that should not move. A tax rate sitting in one cell, an exchange rate at the top of the sheet, a discount percentage in a settings area. If that reference shifts along with everything else, your formula suddenly points at an empty cell or the wrong value entirely.

Locking references with $

The $ sign tells Excel “do not adjust this part of the reference when I copy the formula.” You can place it before the column letter, before the row number, or both.

The most common scenario is a constant that lives in one cell while every row in your table needs to use it. Think of a tax rate in B1 that applies to every product in a price list. You want each row’s formula to reference its own price (relative, shifts per row) and the single tax rate cell (fixed, should never shift). The way to express that is $B$1, which tells Excel to always point at column B, row 1, no matter where the formula ends up.

Try it yourself. The spreadsheet below has an 8% tax rate in cell B1. Write a formula in each blue cell that calculates the total price including tax. A formula like =B4*(1+$B$1) in C4 does the job. Without the dollar signs, copying the formula to C5 would shift the B1 reference to B2, which is an empty row.

Once you have solved that, you already understand the most important use of $. Whenever a formula works in one cell but breaks when copied, the fix is usually adding dollar signs to the reference that should stay put.

There are actually four ways to write a reference, depending on which parts you lock. The table below uses B1 as an example. Imagine you have a formula that contains B1 in some cell, and then you copy that formula one row down or one column to the right. Each row shows what happens:

Plain B1 is fully relative: both parts shift. $B$1 is fully absolute: nothing shifts. The two middle rows are “mixed” references, where you lock only the column or only the row. Mixed references are less common in everyday work, but they become essential in one specific scenario.

Before we get to that, a quick shortcut: you do not have to type dollar signs by hand. While editing a formula, click on a cell reference and press F4 (or Cmd + T on Mac). Each press cycles through the four types: B1 to $B$1 to B$1 to $B1 and back. It is one of those shortcuts that pays for itself on the first day.

Mixed references: one formula for an entire grid

Mixed references shine when you need to build a table where rows and columns both contribute to the calculation. Multiplication tables, currency converters, pricing matrices, distance grids: anything where one axis of values runs across the top and another runs down the side.

The trick is to lock the column for the values along the side and lock the row for the values along the top. That way, a single formula works everywhere in the grid without manual adjustments.

Fill in the multiplication table below. Write a formula in B2 that multiplies the row header in column A by the column header in row 1. If your dollar signs are in the right places, you can use the same formula pattern for every cell in the grid.

The formula you need is =$A2*B$1. Here is why it works:

• $A2 locks column A (the row multipliers on the left side) but lets the row number shift as you copy down. Row 2 reads from A2, row 3 from A3, and so on.
• B$1 locks row 1 (the column multipliers across the top) but lets the column letter shift as you copy across. Column B reads from B1, column C from C1, and so on.

Enter that formula in every blue cell. Each copy adjusts the free part of the reference while keeping the locked part fixed, so every intersection calculates correctly. This exact pattern shows up whenever you build a cross-tabulation: commission grids where rates vary by region and product tier, conversion tables where amounts run down one side and exchange rates run across the top, or distance matrices between cities.

Picking the right reference type

Most of the time you will use plain relative references. They are the default, they handle the common case, and you do not need to think about them. When a formula breaks after copying, check which cell reference shifted when it should not have, and add $ to lock it.

Here is the quick decision:

• Relative (B1): the formula is copied within a column or row, and every row calculates independently from its own data.
• Absolute ($B$1): the formula references a constant, a rate, or a summary cell that should never change no matter where you copy the formula.
• Mixed ($A1 or A$1): you are building a grid where one axis is in a row and the other is in a column. Lock the part that should not move, leave the other part free.

The fix is almost always one or two dollar signs away. If you remember nothing else, remember F4.

Want more hands-on practice? Try a few guided exercises on our Excel exercises page.