## How to use absolute and relative references

What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD and rows have numbers, 1 to 1 048 576. The total number of columns in Excel 2007 are 16384.

A cell can contain a value, formula or constants. A formula starts with a = (equal) sign. You can use values from other cells in your formula. To use another value you create a reference to the cell containing the value you want to use in your formula.

A formula starts with a "=" equal sign. A reference is a column letter and a row number, like B1. B is the column B and 1 is row number one.

**Example:**

- Double click cell A1 and type =B1 then press Enter
- Double click cell B1 and type 11 then press Enter

You have now created a reference to cell B1. B1 contains the value 11. A1 contains the reference to B1 so both cells now show the number 11. Excel uses relative references by default. =B1 is a relative reference.

### What is a relative reference in excel?

When you copy a cell containing a relative reference and paste it into another cell, the relative reference changes. Let me show you an example.

Now copy cell A1 and paste it to cell A3.

- Right click on cell A1 and click Copy
- Right click on cell A3 and click Paste
- Press Escape to remove selection.

When you copy a formula containing relative references the reference changes when you paste it into a another cell. Select cell A3. The formula window now show =B3. See picture above.

Cell A3 has the value 0 (zero) and the formula field shows the relative reference =B3. Cell B3 is empty and that is why cell A3 displays 0 (zero).

When you copied the cell reference in cell A1 to A3, the relative reference changed to B3. The reference changed two rows down. The exact same number as of rows between the copied cell A1 and cell A3.

How much the reference changes is relative to the position of the copied cell and the position of the new cell the formula is pasted into. Excel uses relative references by default.

**How to change a relative reference to an absolute reference.**

- Double click A1.
- Press F4
- Press Enter

The relative reference =B1 changes to an absolute reference =$B$1.

### What is an absolute reference in excel?

An absolute reference in a formula stays the same if copied to another cell.

Example:

- Right click cell A1 and click Copy
- Right click cell A3 and click Paste
- Press Escape to remove selection.

Select cell A3

The cell reference in cell A3 stays the same (is locked to cell B1). An absolute reference does not change when copied or filled into other cells.

### Insert a row or column

Remember, if you insert a row or column into your excel sheet the absolute reference changes.

Example

Cell A1 contains an absolute reference to B1 (=$B$1).

The absolute reference changed to =$B$2.

To keep absolute references use INDIRECT function.

- Type =INDIRECT("$B$1") + ENTER in cell A1

- Select row 1
- Right click on row 1 and select "Insert"
- Done!

The absolute reference is still referencing $B$1. $B$1 is empty and $B$1 returns a 0 (zero).

There is a downside using INDIRECT. INDIRECT is a volatile function. A volatile function is recalculated every time a recalculation is made. You might think that is alright. But if your excel sheet has many calculations and INDIRECT is often used, the calculation can become very slow.

How to quickly select a non contiguous range

A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]

How to find errors in a worksheet

Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]

The picture above shows data in column B, some cells contain nothing, they are blank. I will now go through […]

How to replace part of formula in all cells

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]

How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]

Drop down list changes cell formatting

You can apply different conditional formatting formulas and formatting to a cell range, using a drop down list. Here is […]

Prevent duplicate records [Data Validation]

Debra Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share […]

Use Conditional Formatting to do lookups in related tables

It can be really hard trying to follow a lookup in related tables. I will show you how to use […]

### 9 Responses to “How to use absolute and relative references”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

[...] D25:$D$25 is a relative and absolute cell reference. [...]

[...] can enter a reference to a single cell or a cell range. The ROW function returns an array of numbers if you enter a [...]

HOW TO AUTO SEARCH NAME THE DISPLAY

LALIT,

CAN YOU EXPLAIN IN GREATER DETAIL?

[…] There are relative cell references in the CONCATENATE function and they change in each cell. Don´t know much about relative and absolute cell references? Read this: Absolute and relative references in excel […]

[…] insert or delete a row or column in excel the cell references in formulas changes, even if you use absolute cell references. The Indirect function helps you solve that problem. The following picture demonstrates what […]

[…] in cell E5: =$E$3+C5/24 Copy this cell and paste to E6:E8. There are both absolute and relative cell references in this […]

[…] formula is valid for cell A3, when you copy it to cell A4 the cell refs change. Check out blog post absolute and relative cell references and learn how to build smarter […]

[…] formula basics in conditional formatting. It is really good if you have some knowledge about absolute and relative cell references but it is not necessary. I think the examples shown here explains a lot when it comes to cell […]