Cell References in MS Excel
Ever notice that when you’re copying a formula in MS Excel the cell references in the formula will change?
I’m sure you have. They do this because a formula uses the cell addresses as relative cell references. The cell names are “directions” for the cells to use in the formula. The cell containing the formula is the starting point for the “directions”.
This is awesome, and many times it’s exactly what you need to happen. However, sometimes you need a cell reference to stay constant when it’s pasted.
Can this be done? Of course—one or two extra characters in the original formula will keep the cell reference constant no matter how many times it’s copied and pasted.
To keep a cell reference constant simply add a $ in front of the column letter and row number.
For example, to use cell B2 in a formula and prevent the cell reference from being changed during a copy/paste, simply change the cell reference to $B$2. Now you can copy and paste to your heart’s content without having the reference to B2 changed.
I would also like to note that you can use the $ on just the column letter or just the row number if keeping just one of them constant is necessary.
Consistency that’s versatile. Who ever thought we’d find that in the world?