Absolute References and the $ Sign in Excel

An Excel absolute reference, written with dollar signs like $F$1, points to the same cell no matter where you copy the formula. You need it whenever many rows share one input, such as a tax rate or a grand total. This guide explains what the $ sign does, the F4 shortcut, and mixed references, with the examples employers use in Excel tests.

What is an absolute reference in Excel?#

An absolute reference is a cell address with $ signs that doesn't change when you copy or fill a formula. Excel has three kinds of reference:

The $ sign has nothing to do with money. It means "lock the part that comes after me": $F locks the column, $1 locks the row.

Why copied formulas break without the $ sign#

By default, references are relative: they move with the formula. =B2*2 copied one row down becomes =B3*2. That's what you want for row-by-row data, but not for a single shared cell.

Here is a price list with an 8% tax rate in F1:

Type =B2*(1+F1) in C2 and fill down with Ctrl+D (Mac: ⌘D). Only the laptop is right. Click C3 and the formula is =B3*(1+F2): both references moved down a row. F2 is empty, so no tax is added. Further down, C6 points at F5, which is empty too.

How to fix it with an absolute reference#

Lock the tax rate and leave the price relative:

=B2*(1+$F$1)

Fill this down and every row points at F1, while B2 moves to B3, B4 and so on:

Now change F1 to 10%, and every price updates at once. That's the point of keeping a shared input in one cell.

Don't lock everything. =$B$2*(1+$F$1) filled down gives the laptop's price in every row. Ask yourself one question for each reference: "should this move when I copy the formula?"

The F4 shortcut for $ signs#

You don't have to type the dollar signs. While editing a formula, put the cursor on a reference and press F4 on Windows. On a Mac, press ⌘T, or fn+F4 on many keyboards.

Each press moves to the next option:

1. $F$1: column and row locked
2. F$1: row locked
3. $F1: column locked
4. F1: back to relative

Stop at the one you need. You can also select a whole formula in the formula bar and press F4 to change every reference at once.

Mixed references: lock only the row or the column#

A mixed reference has one $. It's useful when you fill a formula both down and across.

Say you want commission for three sales amounts at three rates:

Type this in B2 and fill it to D4:

=$A2*B$1

• $A2 locks column A, so every cell uses the sales amount in column A, but the row moves.
• B$1 locks row 1, so every cell uses the rate in row 1, but the column moves.

One formula fills all nine cells correctly. This "grid" question is a favourite in Excel interview questions. For a deeper comparison of all three types, read relative vs absolute vs mixed references.

The same idea applies when you fill right. Say three discounts, 10%, 15% and 20%, sit in B8, C8 and D8 below the price list. To show the laptop's price after each one, use =$B$2*(1-B8) in B9 and fill right to D9. The price stays fixed while the discount moves: 809.10, 764.15 and 719.20.

A real example: share of a budget total#

Absolute references show up constantly in budgets. Here is a department's actual spending:

Each share divides the row's actual by the total in C7, so the total must be locked:

=C2/$C$7

Fill down to E6. Without the $, E3 would divide by C8, an empty cell, and show #DIV/0!. The totals in row 7 are plain SUM formulas; see the Excel SUM function, or create them in one key press with the AutoSum shortcut. Finance teams build sheets like this every month, as covered in Excel skills for finance and accounting jobs.

Common mistakes with absolute references#

• Locking the wrong cell. =$B2*(1+F1) locks the column of the price, which doesn't help when you fill down. The tax rate needs the lock.
• Half-locking a shared cell. $F1 still lets the row move when you fill down. A single shared cell usually needs both signs, $F$1.
• Locking a total inside its own range. If a SUM range includes its own cell, the result is a circular reference, with or without $. See why your Excel total is wrong.
• Fixing one cell, not the column. After you correct C2, fill down again so every row gets the new formula.

Practise it#

Practise relative and absolute references in a live spreadsheet: you'll watch a tax formula break, fix it with F4, test it by changing the rate and fill a discount row to the right. Then put it all together in the department budget practice, where you lock the total to work out each category's share.