When referencing a cell in a formula in Excel, the default reference type is a relative reference. These references will change when the formula is copied to other cells based on their relative column and row. If you want to keep a reference constant, regardless of where the formula is copied, you need to use the absolute reference.

• What is an absolute reference
• How to make absolute references
• Use absolute reference with examples
• Calculate percentage of total
• Look for a value and return to corresponding match value
• 2 clicks to batch make cell references absolute with Kutools
• Relative reference and mixed reference
• Things to remember

Free download the sample file

Video: Absolute reference

What is an absolute reference

An absolute reference is a type of cell reference in Excel.

Compared to a relative reference that will change based on its relative position when a formula is copied to other cells, an absolute reference will remain constant no matter where the formula is copied or moved.

An absolute reference is created by adding a dollar sign ($) before the column and row references in the formula. For example, to create an absolute reference for cell A1, you should represent it as $A$1.

Absolute references are useful when you want to refer to a fixed cell or range in a formula that will be copied to multiple cells, but you do not want the reference to change.

For example, the range A4:C7 contains products' prices, and you want to get the payable tax of each product based on the tax rate in cell B2.

If you use relative reference in the formula like "=B5*B2", some wrong results are returned when you drag auto fill handle down to apply this formula. Since the reference to cell B2 will change relative to the cells in the formula. Now, the formula in cell C6 is "=B6*B3", and the formula in cell C7 is "=B7*B4"

But if you use an absolute reference to cell B2 with the formula "=B5*$B$2" will ensure that the tax rate stays the same for all cells when the formula is dragged down using the auto fill handle, the results are correct.

Using relative reference		Using absolute reference

How to make absolute references

To make an absolute reference in Excel, you need to add dollar signs ($) before the column and row references in formula. There are two ways to create absolute reference:

Manually add dollar signs to the cell reference

You could manually add dollar signs ($) before the column and row references that you want to make absolute while typing the formula in a cell.

For example, if you want to add the numbers in cell A1 and B1 and make both of them absolute, just type the formula as "=$A$1+$B$1". This will ensure that the cell references remain constant when the formula is copied or moved to other cells.

Or if you want to change references in an existed formula in a cell to absolute, you can select the cell, then go to the formula bar to add the dollar signs ($).

Using shortcut F4 to convert relative reference to absolute

1. Double click the cell with the formula to enter the edit mode;
2. Place the cursor at the cell reference you want to make absolute;
3. Press F4 key on the keyboard to switch the reference types until the dollar signs are added before both column and row references;
4. Press Enter key to exit edit mode and apply the changes.

F4 key can toggle reference between relative reference, absolute reference and mixed reference.

A1 → $A$1 → A$1 → $A1 → A1

If you want to make all references absolute in a formula, select the whole formula in the formula bar, press F4 key to toggle reference types until the dollar signs are added before both of column and row references.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

Use absolute reference with examples

This part provides 2 examples to show when and how to use absolute references in an Excel formula.

Example 1 Calculate percentage of total

Suppose you have a data range (A3:B7) containing the sales of each fruit, and the cell B8 contains the total sales amount of these fruits, now you want to calculate the percentage of each fruit sale of the total.

The generic formula to calculate the percentage of total:

Percentage = Sale/Amount

Use the relative reference in the formula to get the percentage of the first fruit like this:

=B4/B8

When dragging the auto fill handle down to calculate the percentage of other fruits, #DIV/0! errors will be returned.

Since when you drag auto fill handle to copy the formula to cells below, the relative reference B8 is automatically adjusted to other cell references (B9, B10, B11) based on their relative positions. And the cell B9, B10 and B11 are empty (zeros), when the divisor is zero, the formula returns to an error.

To fix the errors, in this case, you need to make cell reference B8 absolute ($B$8) in the formula to keep it from changing when you move or copy the formula to anywhere. Now the formula is updated to:

=B4/$B$8

Then drag the auto fill handle down to calculate percentage of other fruits.

Example 2 Look for a value and return to corresponding match value

Assuming you want to look for the names list in D4:D5 and return their corresponding salaries based on the staff names and corresponding annual salary provided in range (A4:B8).

The generic formula to lookup is:

=VLOOKUP(lookup_value, table_range, column_index, logical)

If you use the relative reference in the formula to lookup a value and return corresponding match value like this:

=VLOOKUP(D4,A4:B8,2,FALSE)

Then drag the auto fill handle down to lookup the value below, an error will be returned.

When you drag the fill handle down to copy the formula to the cell below, the references in the formula automatically adjust down by one row. As a result, the reference to the table range, A4:B8, becomes A5:B9. Since "Lisa: cannot be found in the range A5:B9, the formula returns an error.

To avoid the errors, use absolute reference $A$4:$B$8 instead of the relative reference A4:B8 in the formula:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Then drag the auto fill handle down to get the salary of Lisa.

2 clicks to batch make cell references absolute with Kutools

Whether you choose to type manually or use F4 shortcut, you can only change one formula at a time in Excel. If you want to make cell references in hundreds of formulas absolute in Excel, the Convert Refers tool of Kutools for Excel can help you handle the job with 2 clicks.

Select the formula cells that you want to make cell references absolute, click Kutools > More (fx) > Convert Refers. Then choose the To absolute option and click Ok or Apply. Now all cell references of the selected formulas have been converted to absolute.

Relative reference and mixed reference

Apart from absolute reference, there are other two reference types: relative reference and mixed reference.

Relative reference is the default reference type in Excel, which is without dollar signs ($) before row and column references. And when a formula with relative references is copied or moved to other cells, the references will automatically change based on their relative position.

For example, when you type a formula in a cell such as "=A1+1", then drag autofill handle down to fill this formula to the next cell, the formula will auto change to "=A2+1".

Mixed reference is made up of both an absolute reference and relative reference. In other words, mixed reference uses the dollar sign ($) to fix either the row or column when a formula is copied or filled.

Take a multiplication table as an example, the rows and columns list the numbers from 1 to 9, which you will multiply each other.

To start, you can use the formula "=B3*C2" in cell C3 to multiply 1 in cell B3 by the number (1) in the first column. However, when you drag the autofill handle to the right to fill the other cells, you'll notice that all of the results are incorrect except for the first one.

This is because when you copy the formula to the right, the row position won’t change, but the column position changes from B3 to C3, D3, etc.. As a result, the formulas in the right cells (D3, E3, etc.) change to "=C3*D2", "=D3*E2", and so on, when you actually want them to be "=B3*D2", "=B3*E2", and so on.

In this case, you need to a add dollar sign ($) to lock the column reference of “B3”. Use the formula as below:

=$B3*C2

Now when you drag the formula to the right, the results are correct.

Then you need to multiply the number 1 in cell C2 by the numbers in the rows below.

When you copy the formula down, the column position of cell C2 won't change, but the row position changes from C2 to C3, C4, etc. As a result, the formulas in the cells below change to "=$B4C3", "=$B5C4", etc. which will produce incorrect results.

To solve this problem, change “C2” to “C$2” to keep row reference from changing when dragging auto fill handle down to fill the formulas.

=$B3*C$2

Now you can drag the auto fill handle right or down to get all results.

Things to remember

• Summary of cell references

  Type	Example	Summary
  Absolute Reference	$A$1	Never change when formula is copied to other cells
  Relative Reference	A1	Both of row and column reference change based on relative position when formula is copied to other cells
  Mixed Reference	$A1/A$1	Row reference changes when formula is copied to other cells but column reference is fixed/Column reference changes when formula is copied to other cells but row reference is fixed;

• Generally, absolute references never change when a formula is moved. However, absolute references will automatically adjust when a row or column is added or removed from the top or left in the worksheet. For example, in a formula "=$A$1+1", when you insert a row at the top of the sheet, the formula will auto change to "=$A$2+1".

  

• The F4 key can switch between relative reference, absolute reference and mixed reference.