An Excel column calculator converts a column number to its letter label, or converts a label such as AA back to its number. In Excel for Microsoft 365 on Windows, enter a positive whole-number column index in A2 and use =SUBSTITUTE(ADDRESS(1,A2,4),"1","") to return its column letters; for example, 27 returns AA.
What an Excel column calculator does
Excel identifies columns with letters, while many calculations and programming tasks use column numbers. The first column, A, is number 1; Z is 26; AA is 27; and XFD is 16,384, the last column available in a standard Excel worksheet. A column calculator is simply a way to translate between these two forms.
The numbering system can seem odd because it is not ordinary base 26. In a conventional base-26 system, each position would have a zero digit, but Excel’s labels use A through Z with no zero label. After Z, the sequence rolls over to AA, then AB, and eventually AZ, BA, and so on. That is why subtracting one before each division is necessary when writing a conversion algorithm, and why a built-in Excel function is usually more convenient than doing the arithmetic by hand.
Excel worksheets contain 16,384 columns and 1,048,576 rows. The final column label, XFD, is useful as a quick check: a number-to-letter result beyond 16,384 is outside the limits of a worksheet. The same conversion method is useful for formulas, reports, import/export work, and scripts that refer to columns by index.
Convert a column number to letters
For a reusable calculator, put a whole-number index in one cell and place the conversion formula in another. The formula uses ADDRESS to create a cell address, then removes the row number from that address. The final argument, 4, requests a relative-style address without dollar signs.
With the column number in A2 and the result expected in B2, enter =SUBSTITUTE(ADDRESS(1,A2,4),"1","") in B2. If A2 contains 27, the result is AA. If A2 contains 702, the result is ZZ; if it contains 703, the result is AAA. The formula works with valid worksheet column numbers, from 1 through 16,384.
To make a small lookup table, place a series of positive integers in A2:A10 and copy the formula down from B2. Each row then shows the Excel letter label for the number beside it. You can also format the input column as a whole number to make accidental decimal entries less likely. The formula is not a general-purpose text parser: an empty cell, a negative value, or a number beyond the worksheet limit will not produce a valid column label.
Convert column letters back to a number
The reverse conversion is useful when a file, script, or instruction gives you a label such as BC but you need its numeric index. One short Excel formula turns the letters into a reference and asks COLUMN for that reference’s position.
With a column label in A2 and the numeric result required in B2, enter =COLUMN(INDIRECT(A2&"1")) in B2. If A2 contains BC, the result is 55. The calculation is correct because B is the second letter and C is the third: 2 × 26 + 3 = 55. For XFD, the result is 16,384.
INDIRECT interprets text as a cell reference, so the input must be a valid Excel column label. A misspelling or a label beyond XFD produces an error rather than a meaningful worksheet column number. The formula is also volatile: Excel may recalculate it whenever the workbook recalculates. For a modest conversion table this is usually manageable, but a very large workbook with many INDIRECT formulas can be less efficient.
Excel letters are not case-sensitive as column references, so lowercase input such as bc is accepted. If the value comes from pasted data, extra spaces or other characters can still make the reference invalid. Clean the source text before converting it, and check a few results against known column positions when accuracy matters.
Use Excel to identify an existing column
If the column already exists in a worksheet, you may not need to convert a label manually. COLUMN returns the numeric position of a cell reference. With any reference in column D, such as D7, enter =COLUMN(D7) in another cell to return 4. The row number does not affect the result.
To obtain the letter label for an existing column, combine COLUMN with the number-to-letter method. With a reference to D7 and the result in another cell, enter =SUBSTITUTE(ADDRESS(1,COLUMN(D7),4),"1",""). It returns D. This is handy when the reference itself changes as a formula is copied, although it is often clearer to use a direct column number as the input when building a standalone conversion tool.
You can also read the column letter from the worksheet’s column headings, but that approach is manual and less reliable for instructions or repeatable calculations. A formula is easier to copy down a list, audit, or update when the source value changes.
Understand the conversion with examples
Excel’s labels can be calculated from left to right using a place-value idea, with each letter assigned a value from 1 to 26. For a two-letter label, multiply the first letter’s value by 26, then add the second letter’s value. Thus BC is 2 × 26 + 3, which equals 55. For three letters, the first letter’s value is multiplied by 26 twice, the second once, and the third is added without another multiplier.
Some reference points help make a conversion easier to verify:
- A is 1, Z is 26, and AA is 27.
- AZ is 52, BA is 53, and ZZ is 702.
- AAA is 703, and XFD is 16,384.
These boundaries are especially useful for spotting an off-by-one error in a custom calculation. A result of 26 should be Z, not AA; 27 should be AA, not AB. If your result is one position too high or low, check whether the method accounts for Excel’s lack of a zero-valued letter.
Common problems and practical checks
A conversion formula may fail because the input is not a valid column index or label, rather than because Excel cannot handle the conversion. Check the value’s type and range first. A number copied from another system may be stored as text; conversely, a label may include a leading or trailing space. Correct the source data or clean it before relying on the output.
- Number outside the worksheet: Excel’s maximum column number is 16,384. Values above that cannot represent a worksheet column.
- Wrong starting point: Excel counts A as 1, not 0. A zero-based index from software needs adjustment before it is treated as an Excel column number.
- Invalid label: The reverse formula expects letters that form a real column reference. Punctuation, spaces, or letters beyond XFD can cause an error.
- Misread result: Column letters can look like abbreviations. Check the number against a known boundary such as Z = 26 or AA = 27.
- Unintended reference: INDIRECT builds a reference from text. Make sure the input is trusted and is meant to be interpreted as a cell address.
Also distinguish a column’s index from its width. A number-to-letter calculator identifies a position; it does not calculate how wide the column should be or how many characters will display. Width depends on Excel’s column-width units and the workbook’s formatting, so it is a separate task.
Column labels versus column width
People sometimes search for a “column calculator” when they want to fit text or numbers into a worksheet. For that problem, select the relevant column or columns, then choose Home > Format > AutoFit Column Width. Excel adjusts the width to fit the contents it considers when applying AutoFit. You can instead choose Home > Format > Column Width to set a specific width.
Column width is not a direct measurement in pixels or a simple character count that applies identically to every font and display setting. Excel bases its width units on the workbook’s default font, and the displayed result can vary with font and formatting. A column label such as XFD says where the column is, not how wide it is. On Excel for Mac, the Home tab also provides formatting controls, though the ribbon layout can vary slightly by release; the same distinction between position and width applies.
AutoFit is helpful for readable tables, but it can create very wide columns when a cell contains a long text string. For presentation, wrapping text, setting a deliberate width, or adjusting row height may be a better choice than allowing one long entry to dictate the column width.
Choosing a method for your workbook
For an ordinary worksheet, the ADDRESS and SUBSTITUTE formula is concise and widely compatible. It is a good choice when you need to turn a number into a label and want the output to update when the input changes. COLUMN is the simplest option when you already have a cell reference and only need its number.
For label-to-number conversion, COLUMN with INDIRECT is straightforward, but the volatility and text-reference behavior are worth knowing. If the conversion is part of a large model or a controlled data pipeline, consider whether the labels can be stored alongside their numeric indexes, or whether a nonvolatile custom calculation is justified. Avoid adding complexity when a small, transparent formula meets the need.
Excel for Microsoft 365 on Windows supports the formulas above. ADDRESS, SUBSTITUTE, COLUMN, and INDIRECT are long-standing worksheet functions, not functions limited to Microsoft 365. Excel for the web also supports these formulas, though editing and navigation controls differ from the Windows desktop application. If a workbook is shared across platforms, keep the formula simple and verify the result in the version your recipients use.
Frequently Asked Questions
What number is column AA in Excel?
AA is column 27. Z is 26, so AA is the first column after Z.
What is the last column in Excel?
The last column in a standard Excel worksheet is XFD, which is column number 16,384.
How do I find the column number of a cell?
Use COLUMN with a cell reference. For example, with the reference D7, =COLUMN(D7) returns 4.
Can Excel convert a column number to letters without a macro?
Yes. The ADDRESS and SUBSTITUTE formula converts a valid column number to its letter label without VBA or an add-in.
Why does my column conversion return an error?
Check that a numeric input is a whole number within 1 to 16,384, or that a text input is a valid column label with no extra characters. An invalid reference built by INDIRECT can return an error.
Related
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete a Single Page in Word SafelyTo delete a single page in Word, click anywhere on it, press Ctrl+G on Windows (Option+Command+G on Mac),…
- How to Change the View in Microsoft Outlook DesktopTo change the view in Outlook desktop, open the folder or calendar you want to adjust, select the…
- How to Add Turabian Footnotes in Word, Step by StepTo add a Turabian footnote in Word, place your cursor after the sentence punctuation, choose References > Insert…
- Add Percentage in Google Sheets: Formulas and FormattingTo display a value as a percentage in Google Sheets, select the cell and choose Format > Number…
- Excel Page Break Dotted Lines: How to Turn Them OffTo hide page-break dotted lines in Excel for Microsoft 365 on Windows, select the affected worksheet, then go…
- Create a 3 x 8 Sheet of Labels in WordTo create a 3 x 8 sheet of labels in Microsoft Word, open Mailings > Labels > Options,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.