The short answer is that it doesn’t.
The long answer is more interesting. In a previous post I explained how Index can be a replacement to the Offset function and how it can improve performance significantly because it is not volatile. In order to improve performance of this Excel file that uses Offset, we needed to change =OFFSET(Sheet1!$A$1,B3,C3) to =INDEX(indextable,B3+1,C3+1) in all cells that contain this formula.
What is the best way to do this? The obvious approach would be to change the formula in D3 and copy down to all other rows. But in this case, headers are mixed with the formulas and so copying down is not an option. We might have hundreds of non-contiguous ranges with these formulas.
Our next option is find/replace all, but this will not work because in D4 we have
=OFFSET(Sheet1!$A$1,B4,C4) and so on. Since the formula appears different on every row, it cannot be updated by a simple find and replace.
This is where R1C1 reference can be useful.
What is R1C1?
If you go to options/formulas you’ll see a checkbox.
The default way to reference cells is called A1 because the columns are referred to by letters and the rows by numbers. This default is used by the vast majority of Excel users. The R1C1 reference style actually preceded A1, but is used almost exclusively when writing VBA code. You can read this article to learn more about R1C1.
For our purposes, a deep understanding of R1C1 is not necessary; just the knowledge that when using it, many formulas look identical, while they look different in A1 style.
Assume that you have a list of numbers in column A and a formula calculating the running total in B.
| Value | Running Total |
| 81 | 81 |
| 5 | 86 |
| 83 | 169 |
| 88 | 257 |
| 83 | 340 |
The A1 formulas are :
| Value | Running Total |
| 81 | =A3 |
| 5 | =A4+B3 |
| 83 | =A5+B4 |
| 88 | =A6+B5 |
| 83 | =A7+B6 |
Note that every formula looks different because it is referencing different rows.
With R1C1 reference style the formulas are:
| Value | Running Total |
| 81 | =RC[-1] |
| 5 | =RC[-1]+R[-1]C |
| 83 | =RC[-1]+R[-1]C |
| 88 | =RC[-1]+R[-1]C |
| 83 | =RC[-1]+R[-1]C |
Except for the first row, all formulas are identical.
These running total functions should be read as, “Add the cell one column to my left with the cell one row before me.” It is exactly the same formula in A1, but it is adjusted to refer to the relative rows each time. Therefore, it looks different.
Back to Changing Offset to Index
As you see above we want to change =OFFSET(Sheet1!$A$1,B3,C3) to =INDEX(indextable,B3+1,C3+1).
Indextable is a name I created over the area that includes all the cells that can be potentially referenced. This is something I need to know in advance, unlike the original case using Offset, when I needed to know only the upper left cell in the range.
The solution involves switching to R1C1 notation and thus making the formula in all rows appear identical.
Now the offset formula shows =OFFSET(Sheet1!R1C1,RC[-2],RC[-1]) in all rows.
You can use Ctrl+H (replace all ) to replace this string to =INDEX(indextable,RC[-2]+1,RC[-1]+1)
I need to add one to both the row and column offset because Offset starts counting from zero, while Index counts from one. A single replace will do the trick. It is a good practice to move to manual calculation before applying a global replace to formulas.
More Complex Cases
Your actual case may be more complex than my example. The spreadsheet may contain multiple Offset formulas and in multiple sheets. You can still use the same technique, only you’ll need to use a separate replace all operation on every unique Offset formula.
In order to find all the unique functions, you can create a list of all functions using the formulatext function, which exists in the Excel 2016 O365, or use a VBA function.
Function FormText(Cell As Range) as String
FormText = Cell.FormulaR1C1
End Function
Once you have all the Offset functions in R1C1, you can remove duplicates and see the short list of unique functions.
For each one you’ll have to create the equivalent formula using Index and run replace all. It requires more work, but the improvement in speed and responsiveness is well worth the effort.
