Excel R1C1 formula keeps coming back even when it is changed

By | September 26, 2014

If you have mix of spreadsheets some of which contains R1C1 formula and if you try to change the R1C1, it comes back in certain spreadsheets. It is extremely frustrating to fix this issue where it is not working as it supposed to work. Here is the solution.

You can change the R1C1 style reference from Excel 2010 > File > Options > Formulas > Working With Formulas > R1C1 Style Reference. This option is global but it also saves the value into each individual spreadsheet which is open at the time of changing this global option. So now when you open up a spreadsheet which has R1C1 style reference and other spreadsheet without it, and if you try to change this option, it will affect both the spreadsheets regardless of spreadsheet from which you changed this option. So the ideal way it to open only one spreadsheet at a time, make the change and close it.

Leave a Reply

Your email address will not be published. Required fields are marked *