When you open an Excel spreadsheet, the first thing you see are cells. You can enter a value into a cell, or use a reference to a different cell by specifying the column letter and row number.

Excel will automatically refresh every reference in a worksheet allowing formulas to dynamically update their content every time you change something or refresh (**F9** key) a worksheet.

There are three types of cell references you can use in Excel:

**relative****absolute****mixed**

Contents

## Relative cell references

The first type of reference is a relative reference. This is the most common type of reference in Excel.

You can write it in the following way:

**=B3**

This means that a cell refers to the value inside cell B3.

### Example 1

Let’s take a look at a few examples to illustrate how this reference works.

In column A, there are integer and text values. In column **B**, there are references to those cells. It’s not visible in this example, but when you use the keyboard shortcut to display formulas – **Ctrl + `** (the key that is located below the ESC key), you can see that they are indeed references and not values.

To get back to the standard view, use the same shortcut again.

If you change values in cell **A2** or cell **A3**, Excel will automatically update cells **B2** and **B3**.

What I just showed you are just simple references to single cells. Of course, you can create more complicated formulas.

### Example 2

In this example, cell **A1** contains number **2**, and cell **B1** contains a formula: **=A1+2. **The value the cell **B1** refers to is 2.

2 + 2 = 4

This is the value you can see in cell **B1**.

If you change the value in cell **A1** to **5**, then the value in cell **B1** automatically changes to **7**, because **5 + 2 = 7**.

### Example 3

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 4

Look at the following example. Here, you can find the names of employees of a 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 – you can merge them by using the relative references.

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 + `** 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 cell, even if you copy the referring cells into a different position.

Absolute references use two dollar signs ($): one before the column letter and one before the row number.

**=$B$4**

### Example 5

I’m going to explain it using a simple example.

Cell **B1** refers to the contents of cell **A1** (**=$A$1**). After you copy cell **B1** to **B3**, it will still refer to **A1**, instead of **A3** as it was with a relative reference.

### Example 6

Let’s try another example. The following table shows the earnings of Tom Smith. We need to calculate how much tax he has 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 **D9.**

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

Use** AutoFill** to calculate the taxes for February and March and sum up all the months. Press **Ctrl + `** to display formulas.

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.

With absolute references, instead of using one column with the same value (tax rate), or changing references for each cell manually, you can use a single cell with data and reference to it using the absolute reference.

## Mixed cell references

Mixed cell references are locked to a row or a column, but not to both at the same time. There are two different types of mixed references:

Locked to a row:

**=A$1**

Locked to a column:

**=$A1**

If you want to create a mixed reference locked to a row – press the **F4** key on the formula bar twice:

For mixed reference locked on a column, press the **F4** key three times.

### Example 7

The following example shows a practical application of both types of mixed cell references.

In this table, there is a list of products and their prices. There are three different discounts for each product.

Enter the following formula into cell **C3**.

`=$B3-($B3*C$2)`

The formula is locked on column B (**$B3**) and row 2 (**C$2**).

If you try to autofill the cell below (C4), Excel will take the next row for the Price (**$B4**) but won’t change the row for (**C$2**), so there is always a 10% discount for this column.

`=$B4-($B4*C$2)`

Now, let’s take a look at how it works for the cell to the right (D3). Because there is a lock on column B (**$B3**), it won’t change.

The cell **C$2** will change to **D$2** because there is no lock on the column, only the row. In this case, we are in the same row, so the formula in D3 looks like this:

`=$B3-($B3*D$2)`

After you fill the rest of the cell, you will get the following result:

## The F4 key

Pressing the F4 key in the formula bar cycles through all types of cell references.

Reference type | Reference example |

Relative | =A1 |

Absolute | =$A$1 |

Mixed, locked on row | =A$1 |

Mixed, locked on column | =$A1 |

You have two options: you can either insert a dollar sign before the row number or column letter by hand, or you can use the F4 key.