by Allen Wyatt
(last updated November 29, 2018)
Some of Jerry's workbooks keep reverting to showing both columns and rows as numbers. (In other words, he doesn't see columns A, B, or C anymore. Instead he sees columns 1, 2, and 3.) Jerry wants to forbid this from ever happening and he has spent quite a bit of time trying to find someone who can tell him how to NEVER use that option nor have it available.
The bothersome (to Jerry) way that he is seeing columns displayed is actually a feature in Excel. It even has a name—R1C1 format, which means "row 1 column 1." It is a way of referring to rows and columns that is a "holdover" from older spreadsheet programs. Some people prefer this method of cell reference, while it drives other people (like Jerry) crazy.
To control the R1C1 setting, follow these steps:
Figure 1. The Excel Options dialog box.
The R1C1 setting is stored as part of a workbook, but interestingly enough it is not always paid attention to by Excel. For instance, let's say that I open a workbook, turn on R1C1, save the workbook, and exit Excel. When I next start Excel, R1C1 will still be turned on—the program apparently remembers what setting I last used.
If I open Excel by double-clicking on a workbook, then whatever R1C1 setting is stored in that workbook is used. This only works for the first workbook opened in the session of Excel. Thus, if the workbook I open to start my Excel session has R1C1 turned, on, then R1C1 is turned on for all the workbooks I subsequently open during that session, regardless of whether it was turned on for those workbooks or not.
The reverse is also true—if the first workbook I open in a session has R1C1 turned off, then it remains turned off for any other workbooks I open during the session, regardless of what R1C1 setting they had when they were saved.
This can lead to some very frustrating results, and I suspect it is the frustration felt by Jerry. Excel seems to automatically use R1C1 format if any of the following conditions are met: (1) you have changed the default workbook to use R1C1 format; (2) if there is some sort of AutoOpen macro that turns on R1C1 format; (3) if you have some automatically loading workbooks (XLSX or XLTX, including the Personal workbook) that has R1C1 turned on; or (4) if you start Excel by double-clicking, in Windows, on a workbook that was saved with R1C1 turned on.
Note, especially, conditions 3 and 4. Excel may very well be starting with R1C1 turned off, but it is overridden by the setting within the file that is first opened. If that workbook has R1C1 turned on, then Excel presumes you want R1C1 used for that session. The only solution to this problem is to open those workbooks, clear the R1C1 setting, save them, and restart Excel.
Confusing, huh? (And, remember, this is all apparently by design!)
Perhaps the best thing to do is to create a small macro that turns off R1C1 mode. Let's say that you get a workbook from someone via e-mail and then you open that workbook. If it is the first workbook of your Excel session and it has R1C1 turned on, then you are stuck with R1C1 turned on for the duration of your session. While you could follow the four steps earlier, a macro on your Quick Access Toolbar could be used to turn off R1C1. Here's an example:
Sub TurnOffR1C1() Application.ReferenceStyle = xlA1 End Sub
Click it, and you are back to "normal" for Excel, for that workbook. If you prefer a macro that simply switches between normal and R1C1 format, you could use the following:
Sub SwitchR1C1() With Application If .ReferenceStyle = xlR1C1 Then .ReferenceStyle = xlA1 Else .ReferenceStyle = xlR1C1 End If End With End Sub
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9960) applies to Microsoft Excel 2007, 2010, and 2013.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When you make changes in a worksheet, Excel automatically recalculates everything that may be affected by that change. If ...Discover More
Have you ever pressed the Tab key, expecting to move to the next cell in your worksheet, only to have Excel completely ...Discover More
You can edit cell information either in the Formula bar or in the cell itself. Here's how you can configure Excel to ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.