What does `$A$1`

mean in a spreadsheet formula in Excel or Google Sheets?

The syntax `$A$1`

is simply an absolute reference to cell `A1`

.

The reason why there are dollar signs prefixed in front of the column and row labels is to prevent the cell from changing its reference as the original cell is copied to another cell.

If a cell containing a reference to `A1`

is not needed to be copied, or if the reference to cell `A1`

is not needed to be **fixed** when copied elsewhere, then the dollar signs are not required.

## Fixing Reference To Cell `A1`

The primary reason why you would want to make a cell reference absolute is that the formula needs the value contained in cell `A1`

and if the cell with the formula containing the reference to cell `A1`

is either copied or moved this reference will not change.

Here’s an example demonstrating what would happen if I copied the cells with formulas in column `B`

across to column `C`

:

A | B | C | |
---|---|---|---|

1 | 2022 | 2024`=$A$1+COLUMN()` | 2025`=$A$1+COLUMN()` |

2 | 2024`=A1+COLUMN()` | 2027`=B1+COLUMN()` |

As you can see the values in column `C`

for the first row contain the same formula (though they have different values due to the changing value of the `COLUMN()`

formula). Whereas the value in the second row have different values when comparing cell `C1`

to `C2`

because cell `C2`

does not have an absolute reference to cell `A1`

, instead it changes across with the direction of the copy and moves the column reference across to cell

.**B**1

If cell `B1`

were copied **down** to cell `B3`

what would happen to the formulas cell reference to `A1`

? This would change the row reference from `A1`

to `A`

as shown below:**2**

A | B | C | |
---|---|---|---|

1 | 2022 | 2024`=$A$1+COLUMN()` | 2025`=$A$1+COLUMN()` |

2 | 2024`=A1+COLUMN()` | 2027`=B1+COLUMN()` | |

3 | 2`=A2+COLUMN()` |

`B2`

down to cell `B3`

produces a result where the cell reference in the formula points to `A2`

not `A1`

As the reference in the formula in `B3`

has changed to `A2`

which is an empty cell this equates to the value of 0 and the `COLUMN()`

formula is 2, therefore `0 + 2 = 2`

.

Whereas if we copied cell `B1`

down to cell `B4`

this would be the result of that operation:

A | B | C | |
---|---|---|---|

1 | 2022 | 2024`=$A$1+COLUMN()` | 2025`=$A$1+COLUMN()` |

2 | 2024`=A1+COLUMN()` | 2027`=B1+COLUMN()` | |

3 | 2`=A2+COLUMN()` | ||

4 | 2024`=$A$1+COLUMN()` |

`B4`

is in the same column as `B1`

it has the same resultAs you can see from the above formula that was copied from cell `B1`

to `B4`

the result is the same (as the copied cell is in the same column). While the formula `COLUMN()`

will change depending upon where you copy it to the absolute reference `$A$1`

stays the same.

## Absolute Reference Keyboard Shortcut

To make it easier to enter an absolute reference for a cell you can use the shortcut key `F4`

to have the spreadsheet program automatically insert the necessary dollar signs for the active range you’ve clicked or typed in your formula.

After you’ve clicked on your cell reference, or typed in the `A1`

notation, and provided your cursor is at the very end of the cell reference you can tap `F4`

on your keyboard and you will see the absolute reference dollar symbols wrap your last entered reference.

## How To Reference `$A$1`

Without Dollar Signs?

If using the absolute reference syntax for a cell, aka the dollar signs, makes your formula difficult to understand there are two other alternatives available: use named ranges, or use `INDIRECT`

formula.

### What Are Named Ranges?

Every cell in a spreadsheet has a reference and this is commonly seen with alphabetic characters followed by numeric characters. For example, `AA149`

references the cell found at the intersection of cells in column `AA`

and cells in row `149`

.

However, cell references can be difficult to manage when you’ve got lots of them in a formula.

I prefer using named ranges whenever I have a formula containing more than a couple of absolute references. I find I can easily get things messed up when there are too many absolute references in one formula.

Therefore, instead of using absolute references you can change these cells (or ranges) to a **named range**.

To create a named range simply select the cell (or cells) and in the white area that contains the reference of the cell (or range) enter a name you would like to call it.

A type of nomenclature that I have adopted when naming cells that are referenced throughout the spreadsheet is to prefix the name of the cell (or range) with an abbreviation of the sheet. For example, if I have a **Settings** sheet and it has individual cells that reference specific details I would click on each cell and start the name of the range as ** setStartYear **– if the cell was to hold the starting year.

I’ve found when you can adopt a naming system that makes sense for you it can be easy in knowing where to go to check the value of that cell AND it prevents name clashes.

Whatever strategy you adopt be mindful of naming clashes where two named ranges have the same name on different sheets. Some spreadsheets, such as Google Sheets, will allow you to have the same name, but the named range would need to be prefixed with the name of the sheet.

For example, ** Settings!StartYear** if the named range

**was used on another sheet.**

`StartYear`

To me it kind of defeats the purpose of a named range if you have to prefix it with the reference of the sheet name, however, I’m doing this anyway – just in an abbreviated way!

### Use `INDIRECT`

Formula

If naming ranges doesn’t meet your taste then another option to consider would be the `INDIRECT`

formula.

The `INDIRECT`

formula takes two parameters: a cell reference (as a string), and a boolean value if the reference is in `A1`

notation (default for this parameter is `TRUE`

if nothing is entered).

Therefore, instead of using `$A$1`

you could use `INDIRECT("A1")`

.

As the reference contained within the formula is static (i.e. a string value pointing to a cell reference) it will not change when this formula is copied or moved.

The `INDIRECT`

formula can also take a named range as its first parameter, i.e. using the named range `setStartYear`

this would look like this with the `INDIRECT`

formula: `INDIRECT("setStartYear")`

.

At least if you’re unsure of which alternative to use you could use both!

## Summary

The absolute reference `$A$1`

points to cell `A1`

in its formula. The dollar signs wrapped inside the reference merely fix the reference so that should the formula move or be copied the reference to `A1`

will not change.

If you find there are just too many dollar symbols in your formula two alternative ways of using the absolute reference is to use named ranges or the `INDIRECT`

function.