You’ve just finished writing a formula, copied it to the whole spreadsheet, formatted everything and you realize that you forgot to make a reference absolute: every formula needed to reference Cell B2 but now, they all reference different cells.
How are you going to do a Find/Replace on the cells, considering that one has B5, the other C12, the third D25, etc., etc.?
The easy way is to update your Reference Style to R1C1. The R1C1 reference works with relative positioning: R marks the Row, C the Column and the numbers that follow R and C are either relative positions (between [ ]) or absolute positions (no [ ]).
- RC refers to the cell two rows below the cell in which the formula’s in
- RC[-1] refers to the cell one column to the left
- R1C1 refers the cell in the first row and first cell ($A$1)
What does it matter? Well, When you wrote your first formula back in the beginning of this post, B2 was the cell 4 rows above the cell you wrote it in, i.e. R[-4]C. When you copy it across and down, while the A1 reference changes, the R1C1 reference doesn’t. Throughout the whole spreadsheet, it’s R[-4]C. If you switch to R1C1 Reference Style, you can replace R[-4]C by R2C2 ($B$2) with a simple Find / Replace and be done in one fell swoop.
How can you switch the Reference Style? Three Options in Excel 2010:
- Go to File \ Options \ Formula \ Working with Formulas, Check R1C1 reference style (this is the long way)
- Open Visual Basic Editor (Alt + F11), open an Immediate window (Ctrl+G), type application.ReferenceStyle=xlR1C1, Enter. This is the quick, easy fix. To reverse, type application.ReferenceStyle=xla1 and Enter
- Put the following macro in your workbook, and add a button for it in your Quick Access toolbar, it’s well worth it.
If application.ReferenceStyle = xlR1C1 Then
application.ReferenceStyle = xlA1
application.ReferenceStyle = xlR1C1
Try it, you might like it.
PS: for more on the history of the R1C1 evolution, and after you’ve pressed the Follow button, check out this blog post by excelmate (will open in new tab).