How to Use Absolute Reference in Excel (Step-by-Step)

Excel is a spreadsheet of cells – and each sheet has 17,179,869,184 cells.

That’s 17.2 billion. A big number 🤯

You can use these cells to populate data and then refer them around your worksheet or workbook by using their cell reference.

A cell reference is a unique combination of a cell’s column and row number that identifies it. And to your surprise, a cell reference can be relative, absolute, and mixed.

This guide aims to help you master absolute references in Microsoft Excel. What they are and how do you use them to optimize your Excel jobs, we have covered it all.

Grab your free practice workbook for this guide here and read along with me until the end.

Table of Contents

What is an absolute reference?

Each cell in Excel is identified by a cell reference which is a combination of the column and row reference that make up that cell.

You click on a cell and the cell reference for it begins to appear before the formula bar.

C4 is the cell reference for the selected cell above. This tells that the cell stands at the intersection of Column C and Row 4.

This is a relative reference 🏸

When you add a dollar sign ($) before the column and row reference making it $C$4, it becomes an absolute reference.

Why use them

An absolute reference locks the column and row reference in place.

If a cell contains an absolute reference, moving that cell from its place, copying it, and pasting it to another place, or deleting any other row or column beside it won’t change this reference. It stays as it is.

Whereas, a relative reference changes as you move its position 🎯

For example, below is a list of prices in USD that I want to convert to CAD.

The Exchange rate (from USD to CAD) is given below in Cell B9.

Step 1) To calculate the prices in CAD, write a simple formula to multiply these prices by the USD/CAD Exchange rate.

Step 2) Drag this formula down the whole list of prices.

Looks like the prices are no longer being converted, we are getting 0s.

As I have dragged the formula down, Excel has updated the cell reference for each successive row. For every next row, the row number in the formula (A2*B9) adds up by 1.

For the second price on the list, this becomes A3*B10.

While this is correct to the extent of Cell A3 (as we want the row number for the list of prices to change) B10 is an empty cell (we don’t want the cell with exchange rate to change).

The automatic updating of cell references in this case distorts the results 😨

If we convert the Cell Reference B9 into an absolute reference ($B$9) and then drag the formula down, everything will be sorted.

Now, the cell reference $B$9 doesn’t change even when the formula changes cells.

Hope this answers why and when absolute references are to be used in Excel.

How to use Absolute references in Excel

Turning a relative reference into an absolute reference in Excel is all about a click.

Step 1) Launch the formula by double-clicking the cell that contains it (or go to the formula bar).

Step 2) Within the formula, go to the cell reference that you want to convert to absolute.

Step 3) Press the F4 key on your keyboard ⌨

Step 4) That cell reference will turn into an absolute reference.

Step 5) Pressing the F4 on the same cell reference twice again will convert it into a mixed reference. The third time you repress it, the absolute reference will convert back into a relative reference.

Alternatively, you can also type the dollar sign ($) before the column and the row reference to convert a relative cell reference into an absolute cell reference.

How to use Mixed references in Excel

An absolute reference is a cell reference where both the column and row reference are preceded by a dollar sign ($).

A relative reference is where none of the column or row reference is preceded by a dollar sign ($).

A mixed reference is a reference in between – where either the column letter or the row number is preceded by a dollar sign (and hence is locked) 💲

For example, $F4 and F$4, both are mixed references.

Mixed references are used when you partially want to lock a cell (only the column reference or row reference).

Step 1) To create a mixed reference in Excel, activate the cell containing the reference.

Step 2) Within the formula, go to the cell reference that you want to convert into a mixed reference.

Step 3) Press the F4 key on your keyboard to switch between reference types.

You can also manually type in the mixed reference by using the dollar sign on your keyboard.

Use a named cell instead of an absolute reference

If you have a cell that you need to refer to very frequently across your worksheet or workbook, you can also turn it into a named cell and refer to it by that name (instead of its absolute reference).

For example, to turn the cell containing the Exchange rate into a named cell 📝

Step 1) Select the cell.

Step 2) Go to the Formulas tab > Defined Names > Define Name.

Step 3) In the New Name dialog box, type in the name you want for that Excel (no spaces).

Step 4) Set the scope to a certain worksheet or the whole workbook (where you want Excel to recognize the cell behind this name).

Step 5) Double-check the sheet and cell reference in the ‘Refers to’ box.

Step 6) Press okay.

Step 7) Excel recognizes this cell by its name now.

You can now type in this name in your formulas instead of using the cell reference for it 🚀

Best part! The name of the cell doesn’t move or change as the formula moves around cells.

Cool substitute for absolute cell references.

Conclusion

You can safely call yourself a pro at using cell references in Excel (particularly absolute ones).

Besides absolute references, relative references in Excel are a blessing I count on twice. Imagine having to write a formula hundreds of times for hundreds of cells (in case there were no relative references in Excel) and no drag-down feature 📚

To learn more about different types of cell references in Excel and how to use them, read my Excel tutorials listed below. You’ll love them.

• Cell References in Excel: Relative, Absolute, and Mixed (2024)
• How to Reference Tab & Sheet Names in Excel Formulas