Excel Column Headings Show Numbers

Have you ever opened a workbook and found that the column headers 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

________________________

You may also like...

67 Responses

  1. Tara says:

    Thank You!!!!!!!!!!! Huge Help

  2. Carmen Jones says:

    Help – How do I change this in version 2010? This is really disruptive!

  3. Marney says:

    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

  4. PM says:

    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…

    • @PM, it sounds like someone changed the Normal font to a graphic font, such as Wingdings.
      There are instructions on my website for changing the Normal font in Excel.

      • PM says:

        Brilliant, thanks Debra! That was exactly it.

      • Kathleen says:

        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!

        • @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.

  5. satya says:

    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.

  6. Norman says:

    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

  7. Dora says:

    Thank you for help.

  8. Bella says:

    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.

  9. rk says:

    Thanks a ton! This helped

  10. Ron German says:

    Thanks so much. I had looked at other helps before yours and none were as clear as your. Many thanks!

  11. Debra H says:

    Great help. Thanks for the simple and precise instructions.

  12. waterlily says:

    i want to change the A,B,C etc column on the top to word ID,Name,Address etc. is that possible?

  13. Miss Trish says:

    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!

  14. desten says:

    thanks you. . .

  15. Milad says:

    Thanks, this was really helpful.

  16. Kong You says:

    Yes, That solve my problem. Thanks.

  17. aron says:

    tks verry much

  18. Sabs says:

    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..

  19. Wayne says:

    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

  20. priya says:

    dear madam thnk you very much it was heplful for me

  21. Seamus says:

    Saved my sanity! Thanks! Your explaination was clear and correct.

  22. Me says:

    Many thanks! You saved my day (and my weekend too!!)

  23. Chris says:

    very helpful advice I am grateful for this, your advice was so much clearer than microsofts own website

  24. Lanelle says:

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

  25. Dale says:

    Thank you that helped a lot.

  26. Ovais says:

    Thanks !!!! great help :)

  27. Prashant says:

    Thanks for the solution for 2010 Excel.

  28. Azee says:

    this proved to be a very big help. many, many thanks! :) – azee

  29. Panagiotis says:

    Tks it REALLY helps a lot.

  30. Kimba says:

    Thanks for your help Debra!

  31. Lingaraja says:

    Thanks a lot.. I was struck because of this for half an hour.

  32. David Tindall says:

    Yeah! Thanks for the help! Very helpful!

  33. Md Ferdous Habib Khan says:

    Thanks a lot. :)

  34. Eeswar Achanta says:

    Thanks a lot…I am searching like anything for this option.

  35. MHENDRA PREMATHILAKA says:

    It’s Working Fine and Very Usefull…………….

    ……….Thanks……………

  36. shishpal says:

    Thanks a lot….very helpfull.

  37. Rafik Sheth says:

    Thanks

  38. Atul Kesharwani says:

    Thanks A Lot

  39. Trevor Nicholaidis says:

    Great. Much appreciated.

  40. Alex says:

    Thanks! You are very helpfull :)

  41. omer says:

    coooooool
    resolved

  42. RAUF says:

    THANK YOU SO MUCH…

  43. Ms. Karma says:

    Hello, just wanted to say thanks, you saved our sanity at work!

  44. Miqdad says:

    Thank you very much it is helpfull

  45. Aideen says:

    Thanks that was really useful.

  46. Allen Kenney says:

    Thank you thank you thank you…life is back to normal

  47. Ghulam Abas says:

    Thank you so much.

    very helpful for me.

    Thanks again.

  48. Commander says:

    Thanks!!!!!! now I can fix the last night issue :D

  49. Madhu says:

    Thanks

  50. Antonios Sylvitis says:

    THANK YOU SOOOOOO MKUCH. I HATE THE R1C1 STYLE. BLIAAAH, DISGUSTING.

  51. Chuck Hamdan says:

    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

  52. Chuck Hamdan says:

    Debra,o, replaced the 1 by “l” “L” and that made it work.

    Thanks,

    Chuck

  53. Sajid says:

    It worked and solved my problem. Thanks

  54. Phuthita says:

    Thank you very very much! It was a frustating moment where you don’t know how to remove the style. You made my day!

  1. September 17, 2012

    […] […]

  2. May 13, 2013

    […] Take a look here. […]

Leave a Reply to Carmen Jones Cancel reply

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