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, and mixed.
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:
This means that a cell refers to the value inside cell B3.
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.
In this example, cell A1 contains the number 2, and cell B1 contains a formula: =A1+2. The value 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.
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, the cell reference has been moved by the same distance as the copied cell.
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.
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.
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.
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:
Locked to a column:
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.
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.
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.
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:
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.
|Mixed, locked on row
|Mixed, locked on column
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.