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.

Vishal Monpara is a full stack Solution Developer/Architect with 12 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user’s mind leveraging geographically dispersed team members.

Leave a Reply

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