You might know that Excel has two different reference styles for formulas:
- A1 (default)
- R1C1
But why should you care? You might think the default A1 reference style is sufficient for you, but that would leave opportunities only the R1C1 reference style allows.
Let’s first see the difference between both reference styles
| Reference Type | A1 Style | R1C1 Style |
|---|---|---|
| Relative | =A1 | =R[ ± x ]C[ ± x] |
| Row-Absolute | =A$1 | =RiC[ ± x] |
| Column Absolute | =$A1 | =R[ ± x ]Ci |
| Absolute | =$A$1 | =RiCi |
- R[ ± x ]: Row offset
- C[ ± x ]: Column offset
- Ri: Row i
- Ci: Column i
For example, in R1C1 reference style, an absolute reference to cell B2, would be =R2C2 and a relative reference to the cell above would be =R[-1]C.
I suggest, you try it with simple formulas in Excel to familiarize yourself with its structure.
I rarely use the R1C1 reference style to design my formulas, but with time, I’ve found different uses for it, such as:
Check for identical formulas
Look carefully in the formula bar and you’ll notice every copy of a formula is identical to the original. When I’m not familiar with a model, I like to check complex formulas in R1C1 before modifying them to make sure that they are in fact identical, not just looking like it, and doesn’t simply look like it.
A common catch could be as simple as the one in the next picture, where the hidden row made the formulas in cells B14 and B16 look identical
Quick Find & Replace
You can also quickly modify the same formulas copied over multiple range by leveraging the uniqueness of the R1C1 reference.
As an example, you might have a model with monthly and quarterly formulas next to each other just like in the picture below. In R1C1, you simply highlight the entire row and do a quick Find & Replace to modify your them.
Conditional Formats
When modifying conditional format formulas in the Conditional Formatting Rules Manager (available in Excel 2007 only), it’s often easier to work in R1C1 so as not to be dependant on a cell from which the reference is established, just like you would in A1 reference type.
VBA Code
You’ll also find that using R1C1 references will give you more robust code that won’t crash on the first column insertion.
It can also be handy when you want to know the column number instead of its letter reference (e.g. A=1, Z=26, …)
2 Trackbacks
[...] you want to learn more about R1C1 reference style, I encourage you to read this post. var addthis_pub = 'slabonne'; var addthis_language = 'en';var addthis_options = 'email, [...]
[...] if I had formulas that might be referring to a wrong column. In order to do so, I switched Excel in R1C1 reference style and simply used Excel’s Find functionality to look for C[1] and C[-1] across the [...]