One of the commonly misunderstood things about using formulas within Excel is writing cell references. By this, I mean, many people don’t know when to and when not to use the dollar sign before a column name or row number.
The dollar sign is used to make the identifier after it stay static no matter where you copy the formula to. Let’s use the following example table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | First Name | Last Name | First Last | Last, First |
| 2 | Jarid | Vurnelson | ||
| 3 | Chelsea | Marten | ||
| 4 | Rian | Zammroz | ||
| 5 | Susannah | Maisol | ||
| 6 | Curran | M’Cally | ||
| 7 | Ed | Orkitt | ||
| 8 | Megan | Pintur | ||
| 9 | Lucie | Rosenbergur | ||
| 10 | Gail | Wiffin | ||
| 11 | Kris | Wess |
As you can see, we have two columns to fill out, and as you have probably guessed, we are going to use formulas to do so.
Absolute Columns & Absolute Rows
If you don’t want the column nor the row to change relative to where the formula is copied to, you should place a dollar sign in front of both the column name and the row number. For example, if cell A2 has a formula of “=$A$1” and the cell is copied to cell G6, the formula in this cell will be “=$A$1”.
Let’s try the following just to see what happens when we use dollar signs both before the column name and row number:
- Enter
=CONCATENATE($A$2," ",$B$2)into cell C2. - Make sure cell C2 is selected and then use the CTRL + C key combination to copy the cell.
- Select cells C3 to C11 and then use the CTRL + V key combination to paste the formula into all of these cells.
Notice that cell C2 shows the value that we are looking for, but cells C3 to C11 don’t because they are duplicates of what is found in C2. The value didn’t change as we copied to formula to other cells because of the dollar sign appearing both in front of the column name and the row number. While it is true that we always want the column that is referenced to stay the same if we copy the formula to other columns, we don’t want the row number to remain as 2 in those cases.
Absolute Columns & Relative Rows
If you don’t want the column to change, but you want the row to change relative to the place to where the formula is copied, you should place a dollar sign in front of the column name and not the row number. For example, if cell A2 has a formula of “=$A1” and the cell is copied to cell G6, the formula in this cell will be “=$A7”.
This is what we want to do. Let’s try it out:
- Enter
=CONCATENATE($A2," ",$B2)into cell C2. - Make sure cell C2 is selected and then use the CTRL + C key combination to copy the cell.
- Select cells C3 to C11 and then use the CTRL + V key combination to paste the formula into all of these cells.
Now you should see everyone’s full name in column C. In fact, in our case, we will want to use absolute references for columns and relative references for rows in the next formula as well:
- Enter
=CONCATENATE($B2,", ",$A2)into cell D2. - Make sure cell D2 is selected and then use the CTRL + C key combination to copy the cell.
- Select cells D3 to D11 and then use the CTRL + V key combination to paste the formula into all of these cells.
Now, if you did everything correctly, you should have the following:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | First Name | Last Name | First Last | Last, First |
| 2 | Jarid | Vurnelson | Jarid Vurnelson | Vurnelson, Jarid |
| 3 | Chelsea | Marten | Chelsea Marten | Marten, Chelsea |
| 4 | Rian | Zammroz | Rian Zammroz | Zammroz, Rian |
| 5 | Susannah | Maisol | Susannah Maisol | Maisol, Susannah |
| 6 | Curran | M’Cally | Curran M’Cally | M’Cally, Curran |
| 7 | Edd | Orkitt | Edd Orkut | Orkut, Edd |
| 8 | Megan | Pintur | Megan Pintur | Pintur, Megan |
| 9 | Lucie | Rosenbergur | Lucie Rosenbergur | Rosenbergur, Lucie |
| 10 | Gail | Wiffin | Gail Wiffin | Wiffin, Gail |
| 11 | Kris | Wess | Kris Wess | Wess, Kris |
Relative Columns & Absolute Rows
If you need the column to change relative to where the formula is copied to, but you want the same row to alway be referenced, you would need to place the dollar sign only in front of the row number. For example, if cell A2 has a formula of “=A$1” and the cell is copied to cell G6, the formula in this cell will be “=G$1”.
Relative Columns & Relative Rows
If you need the column and row to change relative to where the formula is copied to, you should not use any dollar signs for the cell reference. For example, if cell A2 has a formula of “=A1” and the cell is copied to cell G6, the formula in this cell will be “=G7”.
