Tips & Tricks 109 – Macro to Change between A1 and R1C1 Notations

Sometimes, you find that all of a sudden your column headings are changed into numbers and your formulas are Rs and Cs. And you need to change them back to alphabetical columns.

The option to do it very simple –

File > Excel Options > Formulas > Uncheck R1C1 Reference Style

But if that happens very often, you can use a macro which you can click and toggle between A1 and R1C1 style effortlessly. The macro code to do it one line only –

Sub ChangeReferenceStyle()
Application.ReferenceStyle = xlA1 + xlR1C1 – Application.ReferenceStyle
End Sub

Credit – Rick Rothstein (http://blog.contextures.com/archives/2009/12/04/excel-vba-switch-column-headings-to-numbers/)

The above link also talks about how to add the macro to QAT so that it is always available to you.