Global reference style toggle in Excel

Updated: April 26, 2022
3 mins read

There many applications for using A1 and R1C1 reference styles in Excel. Here is how I made a global toggle to easily switch between the two on all Excel workbooks.

This is what my reference style toggle looks like:

Developer tab

If you cannot see the Developer tab in the ribbon in Exel, here are the steps to make it visible.

Right click on the ribbon > Click Customize the Ribbon > On the far right hand pane, make sure the Developer checkbox is ticked > Click “OK”.

Personal workbook

In order to have a macro available to you every time you open up an Excel file, you need to have the macro saved in your Personal Workbook. The easiest way to do this is to create a dummy macro in your personal workbook via Developer tab (under the Code section) > Record Macro > Enter a random letter in the Shortcut key (I used the letter P) > Select Personal Workbook under Store macro in and then stop the recording via Developer tab > Stop recording.

Visual basic panel

We now need to edit this dummy macro to actually have the VBA code to switch between the reference style.

To do this open up the Visual Basic panel via Developer tab (under the Code section). You will notice some directories in the tree. Expand the directory called VBAProject (PERSONAL.XLSB), then expand the Modules directory, and then double click Module 1 to view the code.

VBA code

The VBA editor will display the code behind your recorded macro. We actually do not need any of this.

Replace all othe existing code with the below snippet:

Sub ReferenceToggle()
If Application.ReferenceStyle = xlA1 Then
    Application.ReferenceStyle = xlR1C1
Else
    Application.ReferenceStyle = xlA1
End If
End Sub

You can exit out of the Visual Basic panel by clicking the cross on the right and then exit the Excel file.

Toggle

Right click any blank area of the Ribbon/tab and then select Customize the Ribbon.

In the left pane, select Quick Access Toolbar and then under Choose commands from, select Macros in the dropdown. Now select PERSONAL.XLSB!ReferenceToggle and then click the Add>> button.

To take it a step further, we will customise the icon for this. With the macro still being selected (in the right hand side box), click the Modify button, select an icon, and then click the OK button. Now click the OK button here also. As you will notice from the image of my toggle (beginning of this post), I chose a venn-diagram icon because it made sense to me.

Saving the global macro

Exit the Excel file by clicking the close icon on the top right and you will be given 2 prompts. Firstly, if you would like to save the current Excel file – do so if you need the current file. Second prompt will ask if you would like to Save the changes you made to the Personal Macro Workbook? – this is important – select Save.

You are done!

Now open up a new Excel file and provided you followed the above steps, you should see the toggle on the quick access on the top of the screen and when you click the icon, the view should switch between the 2 reference styles.

I will explore some reference style use cases in the near future – if you use VBA on Excel then I would definitely recommend to watch this space.