Software

Challenge solution: How to set all workbooks to R1C1 referencing

This week, learn the solution to the recent challenge: Can you help a user who wants all workbooks to default to R1C1 referencing?

This challenge asked for way to eliminate a user's need to change the referencing style for each new workbook. Specifically, this user prefers R1C1 referencing, but it isn't the default.

Most suggested changing the template's default setting, but Jean-paul.pagnac was the first reader to respond with this solution. As Jean-paul suggested, you must first change the default that determines this setting as follows:

  1. Click the File tab and choose Options under Help. In Excel 2007, click the Office button and click Excel Options. In both 2010 and 2007, choose Formulas in the left pane. In Excel 2003, choose Options from the Tools menu and click the General tab.
  2. In the Working With Formulas section, check the R1C1 Reference Style option. In Word 2003, this option is in the Settings section.
  3. Click OK.

Then, refer to the "Open with a custom template" section in Reduce start up tasks by customizing the way Excel launches for specific instructions on how to save the altered workbook as the new template. Once you do, every new workbook will open using the R1C1 referencing style. This change will not change the referencing style for  existing workbooks.

Hkammann suggested using code in a startup macro. If you'd rather do it that way, use the statement

Application.ReferenceStyle = xlR1C1

Thanks to everyone for another great Office challenge.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks