How to quickly convert column number to letter?
When working with Excel, users may sometimes notice that the column headers are displayed as numbers instead of the traditional letters (for example, "1,2,3..." instead of "A, B, C..."). This can happen because Excel provides two reference styles: the default A1 style, which uses letters for columns and numbers for rows, and the alternative R1C1 style, where both rows and columns are numbered. Depending on your workflow or the need to work with formulas that reference cells in a specific way, you might need to switch between these two styles. Understanding how to quickly change the column headers from numbers to letters— or vice versa— can improve navigation and clarity, especially in collaborative environments or when troubleshooting formulas.
➤ Alternative: Excel formula (convert number → letter)
Switch column headers: A1 (letters) ↔ R1C1 (numbers)
If you need to revert to the common setting where columns are shown as letters (A, B, C...), or conversely, want to switch to a numeric column display, you can easily adjust this within your Excel settings. The process is straightforward:
1. Click the File tab in the top-left corner of the Excel window to open the backstage view. Then select Options from the sidebar. This opens the Excel Options dialog where you can modify advanced settings for Excel.
2. In the Excel Options dialog, click Formulas on the left to access formula-related preferences. Under the Working with formulas section, find R1C1 reference style. By default, this box is unchecked (columns show as letters). If the box is checked, columns appear as numbers.
3. To display column headers as letters, make sure the R1C1 reference style box is unchecked. After adjusting, click OK to apply your changes and close the dialog.
Now your worksheet will show columns as letters (A, B, C...), which tends to be more intuitive and is the standard display for most users. This format can make reading and writing formulas easier, especially when sharing workbooks with others who expect the default reference style.
If you ever need to switch the column headers back to numbers—such as for compatibility with certain macros or formula editing tasks—simply return to the same Options dialog under Formulas and check the R1C1 reference style option. Remember to click OK to confirm your choice. The headers will immediately update to display numbers for columns.
Note: Switching between A1 and R1C1 styles affects how existing and new formulas reference cells. Most users and external references expect the A1 style, so be cautious if collaborating or using third-party macros. Some older VBA scripts or documentation may refer to R1C1; if formulas behave unusually, double-check your reference setting.
Applicable scenarios for switching styles include debugging complex formulas, migrating data from systems that use numeric columns, or certain programming tasks. For general spreadsheet work, A1 (letters for columns) is usually preferred for clarity and ease of use.
If you experience unexpected formula errors after changing this setting (such as references not updating as intended), try toggling the option again or reopening your workbook. Also confirm collaborators are aware of the column reference style, as it is an application-wide setting and not tied to individual workbooks.
Alternative: Excel formula (convert number → letter)
When your workbook uses the R1C1 reference style (column headers are numbers), use the formula below to turn a column number in the left cell into its column letters in the current cell.
1. Make sure the column number is in the cell to the left of your result cell.
2. In your result cell, enter:
=SUBSTITUTE(ADDRESS(1,RC[-1],4),"1","")3. Press Enter. Fill down as needed.
How it works: ADDRESS(1,RC[-1],4) returns a relative reference like E1 or AA1; SUBSTITUTE(...,"1","") removes the row number and leaves just the column letters.
- Fixed-cell variant: If your number is in a specific cell, replace
RC[-1]with its R1C1 address, e.g.=SUBSTITUTE(ADDRESS(1,R2C3,4),"1",""). - Text numbers: If the source is text like "5", wrap with
VALUE:=SUBSTITUTE(ADDRESS(1,VALUE(RC[-1]),4),"1",""). - Locale separators: If your Excel uses semicolons, change commas to semicolons:
=SUBSTITUTE(ADDRESS(1;RC[-1];4);"1";""). - Want letters back in headers? That’s the display mode: File ▸ Options ▸ Formulas ▸ uncheck R1C1 reference style.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in