A reference is a cell that points to a value inside another cell. When the value in the first cell has changed all values in all cells that point to that cell, also change, and if it’s deleted, there will be a reference error (#REF!).
For references in Google Sheets, we use the dollar ($) operator.
There are three types of cell references: relative, absolute, and mixed.
Relative references
Take a look at the following example.
It has 3 names split into the first names and last names.
Let’s create another column, called Name, that will merge these two columns into a single one.
Type “Name” inside cell C1 and format it the same way as two other column headers.
Enter this formula into cell C2:
|
1 |
=A2&" "&B2 |
We are using ampersand to join text inside cells. There is also a space between cells. It’s needed because otherwise the first and last name will be joined into a single word.
This is the result:
Hover over the little blue square in the lower-right corner and drag it down to fill the other two cells. You can also double-click it and the cells will also be filled with names.
If you click cell C3, you can see that the referenced row changed accordingly.
In the formula bar the formula for cell C3 is:
|
1 |
=A3&" "&B3 |
Absolute references
In the previous example, the values in cells were relative. This means, that when you drag the square to fill the remaining cells were addressing values from the row that is relative to the raw you dragged.
Now, let’s try to use absolute reference.
As the name suggests it always references the same cell.
Let’s modify our example. In this case, there are three people from the same family. We want all names to be merged with a single name.
If we wanted to use the relative reference, we would have to fill cells B3 and B4 with the last name “Dean”, otherwise in C3 and C4, there would be only first names, because the cells for last names are empty.
The best way, in this case, is to use the relative reference for the first name and absolute for the second name.
You have to change the second reference in cell C2 to:
|
1 |
=A2&" "&$B$2 |
You can do it by either typing these two dollar signs in front of column name and row number, or click cell C2, click inside the cell, and press F4. It can be done both in the formula bar or cell.
If you drag cell C2 to fill the other two cells, the last name will always reference to cell B2.
Mixed references
A mixed cell reference is a mix between relative and absolute references, but not in the way as we did in the previous example, where we joined and relative and absolute reference. The mix is inside a single reference.
An absolute reference is locked to a particular cell, while mixed reference to particular row: A$2, or column $A2.
To create a mixed reference, locked to row, press F4 twice. To lock it to a column, press F4 thrice.
The INDIRECT function
The INDIRECT function returns a cell reference specified by a string.
So how it’s different from the standard reference? Let’s take a look.
We have two first names and two last names. In column C there are two references to first names from column A.
C2: =A2
C3: =INDIRECT(“A3”)
It seems that both references work the same, so using the INDIRECT function is just too much writing. But look at what happens when column A is deleted.
The INDIRECT function reference to cell A3. It doesn’t matter that the “old” cell A3 was delete, but for standard reference (=A2) it matters. If you remove the referenced cell, you are going to have an error.
