Absolute Reference in Excel – 4 Examples

The Absolute reference locks the referred cell. If you use an absolute reference, the cell won’t change after dragging the Fill Handle.

To create Absolute references, enter a Dollar sign ($) before the column number and another Dollar sign($) before the Row number.

Example 1 –  Using a Keyboard Shortcut

• Select the cell to use as absolute reference and press F4.

Example 2 –

Absolute Column Reference Only

When you put the Dollar sign only before the column number you will get an absolute column with relative row reference. It means when you drag the formula cell, only the row of the selected cell in the formula bar will change, the column will remain unchanged. It will look like $B4.

When you want to change the row but want to keep the column constant you can use absolute column reference only. It is generally used when you copy your data vertically.

Example 3 –

Absolute Row Reference Only

When you put the Dollar sign only before the row number you will get an absolute row with relative column reference. It means when you drag the formula cell, only the column  of the selected cell in the formula bar will change, the row will remain unchanged. It will look like B$4.

When you want to change the column but want to keep the row constant you can use absolute row reference only. It is generally used when you copy your data horizontally.

Example 4 -Using Absolute reference

The dataset below showcases different principal amounts and years of deposit.

To find how much Interest each person will get at a specific interest rate, you need to multiply the principal by the years of deposit at fixed interest rate.

• You can find the Interest for the first person by using the formula “=B8*C8*B4” in D8

• To find the interest of the other entries, if you we drag the Fill Handle to the end of the dataset,

it will show multiple errors, because B4 had changed.

• To avoid this, use an absolute reference in B4

• Drag the Fill Handle to see the results.

Absolute Reference for a Range

You can use absolute reference for a range of cells.

Find, in the dataset below, the total price of pen, pencil and notebook for different customers. The Unit price is given in B4:B6

• To apply an absolute reference to this range, select D9 and enter the formula:

$B$4:$B$6 has an absolute reference.

• You will see the value in D10 and D11.

• To get the value for other customers, copy the formula in D9.
• Select the first cell of each customer and paste the formula.

Switching Reference from Absolute to Relative and Relative to Absolute

• Select the cell in the formula bar and press F4, it will have an absolute reference.
• Press F4 again, the cell will have an absolute row reference with a relative column reference.
• Press F4 again, the cell will have an absolute column reference with relative row reference.
• Press F4 again, the cell will have a relative reference.

See the example:

B4>press F4> $B$4>press F4>B$4>press F4>$B4>press F4>B4

Reference a Cell across Different Worksheets

You can refer to a cell in a formula from a different worksheet of the same workbook.

You have a value in the New Deposit List sheet and want to use the value in B4 of the Deposit List sheet.

• Select the cell you want to refer to and enter the formula:

•  Press F4.

The selected cell will have an absolute reference.

Press ENTER to see the value.

Drag down the Fill Handle to see the result in the rest of the cells.

Download Workbook

Absolute Reference in Excel: Knowledge Hub

• Excel F4 Absolute Reference Not Working
• Excel VBA Formula R1C1 Absolute Reference

<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel