## Absolute and relative references in excel

### What is a refererence in excel?

Excel has a A1 reference style.

Columns are named letters A to XFD. Total number of columns in Excel 2007 are 16384.

Rows have numbers, 1 to 1 048 576. You guessed it right, the total number of rows in excel 2007 are 1 048 576.

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 cell A1 and click Copy
- Right click 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.

### Category: Excel

This post explains how to lookup a value and return multiple values. No array formula required.

Comments(441) Filed in category: Excel, VLOOKUP and return multiple values

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. […]

Comments(249) Filed in category: Concatenate, Excel, Textjoin

Question: How do I create a chart that dynamically adds the values, as i type them in the workbook? Answer: […]

Comments(161) Filed in category: Charts, Excel, Interactive

### 12 Responses to “Absolute and relative references in excel”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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

[...] Copy cell B2 and paste B2:K4. Read more about relative and absolute cell references. [...]

[...] $C6 is a relative AND absolute cell reference. [...]

[...] Relative and absolute cell references [...]

[...] Read more about relative and absolute cell references [...]

[...] 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 […]