Excel Column Headings Show Numbers

Excel Column Headings Show Numbers

Have you ever opened an Excel workbook and found that the column headings show numbers instead of letters? The formula look strange too, showing references like RC[-1] instead of D2.

R1C1Formula

This is R1C1 reference style -- a handy feature, and I use it sometimes when programming or setting up a workbook.

Numbers on the column headings make it easier to set up formulas that need a column number, such as VLOOKUP. I don’t have to get fingerprints on my screen, as I count across to column R, where the lookup value is.

Video: Change Excel Column Headings from Numbers to Letters

To see why this happens, and how to switch the column headings back to letters, watch this short video tutorial. The written instructions are below the video.

Why It Happens

Maybe you’ve never heard of R1C1 reference style, and certainly didn’t change any settings. If you didn’t turn that option on, why did the numbers suddenly appear? Probably because someone sent you a workbook, and that’s the first Excel file that you opened today.

The first workbook that you open, when opening Excel, sets the reference style. For example, perhaps I built a workbook for you, and saved it while I was using R1C1 reference style.

I sent you the workbook overnight, and it was the first thing you opened this morning. Surprise! There are numbers in the column headings.

Turn R1C1 Reference Style On or Off

If you close Excel, then open a workbook that you created yourself, with letters in the column headings, that will change the reference style back to A1, which has letters in the column headings.

Or, to manually change the reference style, you can change the option setting.

In Excel 2010:

  1. At the left end of the Ribbon, click the File tab, then click Options.
    • Excel Options 2010
  2. Click the Formulas category.
  3. In the Working with Formulas section, add or remove the check mark from 'R1C1 reference style'
  4. Click OK to close the Options window.

In Excel 2007:

    1. At the left end of the Ribbon, click the Office Button, then click Excel Options.
    2. Click the Formulas category.
    3. In the Working with Formulas section, add or remove the check mark from 'R1C1 reference style'
    4. Click OK to close the Options window.

R1C1RefStyle

In Excel 2003 and earlier versions:

  1. On the Tools menu, click Options and select the General tab.
  2. Add or remove the check mark from 'R1C1 reference style'
  3. Click OK to close the Options dialog box.

R1C1

Use a Macro to Switch Headings

If you frequently change the headings from numbers to letters, or letters to numbers, you can create a macro to do the work for you.

There are instructions in this blog post: Excel VBA: Switch Column Headings to Numbers
________________________

67 thoughts on “Excel Column Headings Show Numbers”

  1. Thank you! My Excel 2010 had a glitch that resulted in autosaving and recovering all the files I had open. And SURPRISE! I had the R1C1 format showing. Well, I never saw that before and my first thought was "#$%@#$$%, my spreasheets are corrupted, ruined, etc (!)". Not a good feeling. Anyway, your site has clearly explained it (not how it happened, but How to undo it) for which I am extremely grateful, and moreso to know nothing is wrong with my recovered/saved files (at least not from the R1C1 designation). Thanks for putting my mind at ease and for the very clear explanation.
    M

  2. I just received a spreadsheet from someone that has icons instead of the standard column/row headers. For example, a picture of a folder, a keyboard, a mouse, etc.
    Any idea how that was done? Every article I read says it's not possible to change the headings - your blog is the only one that even comes close the issue I'm seeing...

      1. Sometimes, I get a file forwarded to me. Column titles/headers are there (LastName, FirstName, Address, etc.), but when I want to sort from A-Z by last name, Excel alphabetizes the row with the column titles as if it were a separate entry. E.g. LastName comes in under the Ls. I give the title row bold, centered, and a different font to designate it from the other entries, but it still comes in under the Ls. I’ve tried using the help button, but I can never find the answer I’m looking for. In the past, I’ve just copied the format from another file. Seems it should be easier to get an answer to what I think is a simple question. Frustrating!

      2. @Kathleen, do you know how/where the original file was created?
        Also, if you're using Excel 2003 or later, you can create a List, or insert a Table, based on the data, and that should add arrows to the heading row. You can use those to sort and filter.

  3. Thank you so much for your help.
    I got excel from someone and the column headings are in numbers.
    I was struggling how to get it back to letters.This blog helped me a lot.

  4. Hi
    Spent an hour at work trying to find out why and how my spreadsheets had suddenly aquired number columns instead of alpha, found your blog in 15 minutes and problem solved.
    Many thanks

  5. I never thought this will actually help me with this. I have been having difficulties chaging excel heading back to letters now google has shown me a whole different world to excel....promise ill use this when facing problems with excel sheets.

  6. Pingback: counting columbs
  7. That is exactly what happened, thank you! I opened a file sent to me and then the next file of my own that I had opened had number headers for the columns. All fixed and the world is as it should be!

  8. This is awesome! I was trying to crack this for more than an hour and this helpmed to click in minutes.. Thanks for posting this.... Appreciate it..

  9. Hi,
    after running a macro, excel inserted little picture of a worksheet and and an arrow pointing to another cell. I tried clicking, right clicking but can get no information. What is this and what does it mean? Thanks

  10. Thank you for the clear and concise explanation. I should have checked online first and saved myself a lot of time!

  11. Hi Debra,
    I wanted to see how it works and checked the reference as mentioned in the video clip but the following macro did not work for me unless I go to the "Referencestyle" and check or uncheck the R1C1 style. but the macro won't change the state of the "Referencestyle". I am using Excel 2007. Am I missing something?
    Thanks in adsvance,
    Chuck

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.