How to Convert New Excel From R1C1 to A1
Microsoft Excel 2010 gives you two ways to reference cells on the spreadsheet from within formulas: A1 and R1C1. When using the A1 method, you reference cells by placing the column letter in front of the row number, so the top left cell in the spreadsheet is "A1." When using the R1C1 method, you enter the row number and column number after "R" and "C" respectively, so the top left cell in the spreadsheet is "R1C1." Excel 2010 defaults to the A1 reference method, but if your spreadsheet is still using the R1C1 method, you can change this setting in the Options menu.
Double-click the Excel file you want to work with to open it using Excel 2010.
Click the "File" tab at the top of the Excel ribbon, then click "Options" from the bottom of the list on the left side of the screen. The Excel Options window appears.
Select "Formulas" from the list on the left side of the window, then look for the "Working with formulas" area on the right side of the window. Click the check next to "R1C1 reference style" to remove the check.
Click "OK" to close the Excel Options window. Your spreadsheet now uses the A1 reference style and Excel automatically changes all the current references to the A1 style.
Tips & Warnings
- When Excel converts your R1C1 references to the A1 style, it uses absolute references in the new style, which appear as dollar signs in front of each reference element. To convert these to relative references, click on the reference in the formula bar and press "F4" three times.
- When you convert from one reference style to another, if you are using named ranges it is possible to wind up with a range that is no longer properly named. For example, "AB1" is an acceptable range name on older versions of Excel using the R1C1 reference style, but cannot be used with the A1 style of referencing. You need to manually alter these names, along with any formulas that point to these names, before converting the spreadsheet.