Getting Rid of Numbered Columns

Written by Allen Wyatt (last updated June 27, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


5

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:

  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 or a later version display the File tab of the ribbon and then click Options.)
  2. Click Formulas at the left side of the dialog box. (See Figure 1.)
  3. Figure 1. The Excel Options dialog box.

  4. Clear (or set) the R1C1 Reference Style check box, under the "Working with Formulas" heading.
  5. Click OK.

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

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9960) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Understanding Pattern Matching

Pattern matching is a type of searching you can do in Word that is very powerful. Despite its power, it remains rather ...

Discover More

Running Macros on Hidden Worksheets

Excel allows you to hide worksheets so that they aren't visible to those using your workbook. Hiding worksheets has a ...

Discover More

Calculating a Geometric Standard Deviation

One of the areas in which Excel provides worksheet functions is in the arena of statistical analysis. You may want to ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Disappearing Ribbon Buttons

Excel allows you to configure your system, even to the point of adding macros to your ribbon area. What are you to do, ...

Discover More

Setting a Default File Format

Excel normally saves workbooks using a default file format that is peculiar to your version of the program. You can ...

Discover More

Slash Key No Longer Works as Expected

Press the slash key and Excel may display a series of keyboard commands near the ribbon. If this behavior drives you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 7?

2021-11-16 16:37:45

Gregory J Cotton

I'm a huge fan of Mr. Wyatt and his extensive Excel training/info, and excitedly stumbled across him again on this website! I was troubleshooting a strange "Workbook stuck in R1C1" issue when I found this article. As usual, the information is detailed and accurate. Thank you.

To share with others, we had a workbook with a named range that (somehow) conflicted with a reference (e.g. Named Range as 'D146'). This prevented disabling the R1C1 style until this naming conflict was corrected.


2021-05-24 11:18:01

Rave From Dave

I've also found that macros don't run as expected if the R1C1 option is switched on. I have a sheet that updates an Excel table, and occasionally the table just disappears . The headings and data remain, but the table design, formatting, table name...all disappear, so no longer even appears in the named range list. Consequently, any formulas referencing the table/columns by name now all show #REF!

What I couldn't understand is this sheet has been working perfectly for a number of years, never seen the issue before, I've narrowed it down to one user who often has R1C1 reference switched on, if they have this option on when they run the macros it removes the table.

I also wonder if this error is a new "feature" to Excel 2016...as we have lots of heavily automated sheets and frequently use tables and I've never seen the issue before.


2021-01-12 09:22:28

Gavin Denyer

Thank you John, for a clear and accurate solution.


2020-07-03 09:20:21

Peter J Moran

Hi John,

A quick Google!

Microsoft Multiplan
When Microsoft came out with its first spreadsheet product, Microsoft Multiplan, it used a very different method of cell addressing, known as R1C1. In the Microsoft system, the rows are numbered just as in the A1 system.

Fancy that!


2020-07-01 17:04:39

John Mann

Just as a matter of curiosity, I'm wondering which early spreadsheet used the R1C1 reference style. My original spreadsheet training was for Lotus 1-2-3 ver 5 and Excel 4.0, and I also had a very crude and simple spreadsheet on my Tiimex-Sinclair ZX81 (whicch I had expanded to all of 16K memory!). These all labeled their columns by letters.

Just curious


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.