Software

Office challenge: Can you help a user who wants all workbooks to default to R1C1 referencing?

This week, text your Excel skills by providing a permanent solution for a user's R1C1 referencing preference.

One of your Excel users prefers working with R1C1 referencing. As you know, setting the current workbook to use this referencing is simple; getting Excel to always default to this setting is a different matter. Excel retains this setting until you change it or you close Excel. The next time you launch Excel, it reverts to the default A1 style. Right now, your user has to reset this option for most new workbooks. Can you help him avoid this startup task?

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.

8 comments
daniel
daniel

Create a new workbook and set the referencing to R1C1 in Excel Options Formulas (General in 2003). Save this work book as a Template (.xltx) in the ... /START folder. The name of the workbook MUST be BOOK.XLTX and nothing else. Typically in Windows 7 the path would be: C:\Users\[user login]\AppData\Roaming\Microsoft\Excel\XLSTART. In Windows XP it would be: C:\Document and Settings\[Login Name]\Application Data\Microsoft\Excel\XLSTART. The next time you open Excel all new workbooks will use the R1C1 (so useful for VBA programmers) notation. Didn't test this in 2003. Only in 2007 using Win 7.

hkammann
hkammann

You could run the following VBA script on startup (Google will explain you on how to do this on startup): Application.ReferenceStyle = xlR1C1 Should make the deal.

DClapp
DClapp

Wow, this request takes me back. Microsoft MultiPlan was what used the R1C1 location references and , perhaps, it was better. But its time to move on.

sbsinc
sbsinc

So you want R1C1 style of cell nominclature; all you do is open Excel and then click the Office Button, the upper left corner of the application, then select Excel Options, then select Formulas and then in the Working with formulas section, check the box R1C1 style and lastly click OK.

jean-paul.pagnac
jean-paul.pagnac

From a new file, keep the setting R1C1 in settings save the file as MODEL in : C:Program FilesMicrosoft OfficeOFFICE11XLSTART File name must be : workbook.XLTS All worksheet will get this style but not old files yet registered.

hkammann
hkammann

I suggested one and provided the code above.

mpala007
mpala007

I saved the Book1.xlsx in XLStart folder after setting it to R1C1 style I did this in both Office 2010 on Win7 & Office 2007 in XP More Changes than just this can be done to the Book1 file