# Excel: Relative, absolute and mixed cell references

Many formulas in Excel contain references to other cells. These references allow formulas to dynamically update their contents.

We can distinguish three types of cell references: **relative, absolute** and **mixed.**

## Relative cell references

This is the standard type of reference. Look at the following examples:

### Example 1:

If cell **A1** contains value **2**, and cell **B1** contains formula **=A1+2** (referring to cell **A1),** then the formula **B1** contains value **4**. If you change the value in cell **A1** to **5**, then the value in cell **B1** automatically changes to **7**.

### Example 2:

If cell **B1** refers to cell **A1**, then after copying cell **B1** to cell **D2,** the cell starts to refer to cell **C2.** In other words, cell reference has been moved by the same distance as the copied cell.

### Example 3:

Look at the following example. Here, you can find the names of employees of a fictional company.

If you want to merge the first name with the last name and place them in column **D**, you don't have to enter them manually, but you can merge them by using the relative references, instead.

In this case, enter the formula **=B2&" "&C2** into cell **D2**. It will merge cell **B2,** space, and cell **C2.** Now you can use **AutoFill** to fill the remaining cells.

You can display formulas instead of values by using the Ctrl + ` (the key that is located below the **ESC** key) keyboard shortcut.

As you can see, only the formula in cell **D2** refers to cells **B2** and **C2.** References in the next cells have been shifted accordingly.

## Absolute cell references

Absolute cell reference always points to the same place, even if you change the position of any of those cells. In other words, if you have cell **A1** which refers to the contents of cell **B1** (**=****$B$1**) and then you change the position of **A1** it will still refer to cell **B1.** If you drag cell **B1** to another location, for example, **B3,** then **A1** will point to the new location of the same cell (**=****$B$3**).

### Example 4:

Look at the following example: it shows the earnings of Tom Smith. We need to calculate how much tax he need to pay each month.

Look at the formula bar. It shows how much tax John needs to pay for January (**=C3*D7**). If you want to automatically fill the remaining months, you will notice that for February the reference doesn't point to cell **D7**, instead it points to cell **D8**, and for March to cell **D9.**

To create an **absolute reference,** click cell **D3,** then in the formula click text **D7.** Now press the **F4** key and confirm it by pressing **Enter.** This will change a **relative reference** to an **absolute reference**.

Use** AutoFill** to count the taxes for February, March, then sum all the months. Press Ctrl + ` to display the formula.

As you can see in the example above in all four cells, the first part of the formula is a relative cell reference and the second part is an absolute cell reference.

## Mixed cell references

A mixed reference is a reference that refer to a specific row or column. For example, **$A1** or **A$1**. If you want to create a mixed reference- press the **F4** key on the formula bar two or three times depending on whether you want to refer to row or column. Press **F4** one more time to go back to the relative cell reference.