Excel COLUMN Function: Return a Column Letter from a Number

Formulas & functions · Updated November 18, 2026

Excel’s COLUMN function returns a column number, not a letter. To display a column letter, combine it with ADDRESS and SUBSTITUTE: with a column number in A2, enter =SUBSTITUTE(ADDRESS(1,A2,4),"1",""). For the letter of the cell containing the formula, use =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","").

Why COLUMN returns a number

Excel identifies worksheet columns with letters in the grid, but many formulas and functions work with column positions as numbers. In that numbering system, A is 1, B is 2, Z is 26, AA is 27, and so on. The COLUMN function reports that numeric position; there is no built-in worksheet function named COLUMNLETTER that directly returns the letter.

This distinction matters when you need to build a label, create a reference, or show a column location in a report. The formula above translates the number into the letters Excel uses in its headings. It works for single-letter and multi-letter columns, including columns such as AZ, BA, and XFD.

In current Microsoft 365 Excel, a worksheet can have up to 16,384 columns, ending at XFD. Older Excel 97–2003 workbooks in the .xls format were limited to 256 columns, ending at IV. A formula intended for a modern .xlsx workbook can therefore produce a letter beyond IV, while an older-format workbook cannot contain that column.

How the formula turns a number into a letter

With the column number stored in A2, the formula is =SUBSTITUTE(ADDRESS(1,A2,4),"1",""). It works in three steps. First, ADDRESS constructs a cell reference using row 1 and the column number in A2. The final argument, 4, asks for a relative reference without dollar signs. For example, if A2 contains 28, ADDRESS returns the text A1.

Next, SUBSTITUTE replaces the character 1 with an empty string. Removing the row number from A1 leaves A. If A2 contains 28, the constructed reference is AB1 and the result is AB. Setting the row to 1 makes the part removed predictable: the address ends in the single character 1.

The formula’s result is text, not a cell reference that Excel can use directly for arithmetic. This is useful for display, labels, or building other text. If the goal is to refer to a cell in a calculation, it is usually better to use a reference-based function such as INDEX rather than convert a column number into text and try to turn it back into a reference.

Get the letter of the formula’s own column

To show the letter for the column where a formula is placed, use =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""). The formula assumes it is entered in the worksheet cell whose column you want to identify. COLUMN() with no reference returns the number of the column containing the formula, and the rest of the expression converts that number into a letter.

For example, entered in D5, this formula returns D. Copy it one column to the right and the copied formula returns E. This is handy for a heading row, a diagnostic worksheet, or a template where formulas may be moved across columns. If you copy the formula down within the same column, the letter stays the same because the column position has not changed.

You can also give COLUMN a reference. With the formula entered anywhere, =SUBSTITUTE(ADDRESS(1,COLUMN(B2),4),"1","") returns B because B2 is in the second column. This is different from putting the number 2 in the formula: COLUMN(B2) calculates the position from a cell reference, while a numeric input lets you convert a value supplied elsewhere.

Use a number in a cell and fill the formula down

For a list of column numbers, keep the numbers in one column and calculate the letters beside them. Assume A2:A7 contains 1, 2, 26, 27, 52, and 16384. In B2, enter =SUBSTITUTE(ADDRESS(1,A2,4),"1",""), then fill the formula down through B7. The results are A, B, Z, AA, AZ, and XFD, respectively.

To fill down in Excel for Microsoft 365 on Windows, select B2 and drag the fill handle at the lower-right corner of the cell down to B7, or copy B2 and paste it into B3:B7. Excel adjusts the relative reference from A2 to A3, A4, and so forth. On Mac, the formula and fill-handle behavior are the same; the precise mouse gesture depends on the pointing device. Excel for the web also supports the formula and fill handle.

If the number is entered in a different cell, change the reference accordingly. For instance, with the number in D4, the formula in E4 should refer to D4. The input must represent a valid worksheet column number: a whole number from 1 through 16,384 in a current .xlsx workbook. Column number 0 does not correspond to an Excel worksheet column.

Examples at important column boundaries

Most mistakes occur where the naming changes from one letter to two. Excel does not continue the alphabet by wrapping from Z back to A. Instead, it uses a base-26-style sequence: after Z comes AA, then AB through AZ, followed by BA. The numbering is not ordinary decimal notation, so multiplying letters as if they were digits will not reliably convert column numbers.

  • With 25 in A2, the formula returns Y.
  • With 26 in A2, it returns Z.
  • With 27 in A2, it returns AA.
  • With 52 in A2, it returns AZ.
  • With 53 in A2, it returns BA.
  • With 702 in A2, it returns ZZ; 703 returns AAA.
  • With 16,384 in A2, it returns XFD, the final column in a modern Excel worksheet.

These boundary examples are useful for checking a formula or validating a conversion routine. If a result is wrong only after Z, the issue is usually an attempted manual conversion that does not account for the multi-letter sequence. The ADDRESS-based formula handles those transitions without separate rules for one-, two-, or three-letter results.

Common errors and ways to avoid them

If the formula returns #VALUE!, check the input. It may be blank, text that Excel cannot interpret as a number, zero, negative, or greater than the workbook’s final column number. A blank input is not a valid column number for this purpose. If a list may contain empty cells, you can leave the output blank until a number is supplied.

With a column number in A2, a blank-safe version for B2 is =IF(A2="","",SUBSTITUTE(ADDRESS(1,A2,4),"1","")). It checks whether A2 is empty before asking ADDRESS to create a reference. An empty-looking cell containing a formula that returns an empty string is also treated as empty by this test. If A2 contains spaces, it is not truly blank, so clean or validate the input rather than assuming whitespace is empty.

A result such as $A$1 usually means the fourth argument to ADDRESS was omitted or set to a value that requests absolute references. The value 4 requests a relative reference, A1, allowing the formula to remove the row number cleanly. If the output includes a row number, verify that the second function is SUBSTITUTE and that the text being replaced is 1.

Excel formulas use commas between arguments in the examples here. Some regional settings use semicolons as the list separator, so Excel may require semicolons when you enter the same formula. This is a regional formula-entry difference, not a different conversion method. Excel may also translate function names in localized editions.

When to use a different method

If you only need to know a column’s letter while working in the grid, the simplest method is often to look at the column heading above the cells. To navigate directly to a known column, you may also use the Name Box to the left of the formula bar: type a cell address such as AB1 and press Enter. Neither approach requires a formula, but neither produces a reusable text result in a worksheet cell.

For formulas that need to choose values by position, prefer a function that works with a numeric index instead of converting that index into a letter. For example, with values in A1:D1 and a position number in F1, =INDEX(A1:D1,1,F1) returns the value at that position in the row. This avoids assembling a text address and is generally easier to maintain if the worksheet changes.

There are also newer text functions that can extract parts of an address, but they do not make the basic conversion more reliable for ordinary use. TEXTBEFORE, for example, is available in Microsoft 365 and Excel 2024, but not every older Excel release. The ADDRESS and SUBSTITUTE approach is familiar, works across many Excel versions, and accommodates the full modern worksheet width.

Frequently Asked Questions

Does the Excel COLUMN function return a letter?
No. COLUMN returns a number: A is 1, B is 2, and Z is 26. Combine it with ADDRESS and SUBSTITUTE to display the corresponding letter.

How do I get the letter of the current column?
Enter =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") in the cell where you want the result. It returns the letter for that cell’s column, and adjusts when copied horizontally.

How do I convert a column number such as 28 to letters?
Put 28 in A2 and, in another cell, enter =SUBSTITUTE(ADDRESS(1,A2,4),"1",""). The result is AB. Change A2 to any valid column number to convert a different value.

What is the column letter for number 16384?
In a modern Excel worksheet, column 16,384 is XFD, the last available column. Older .xls workbooks have a lower limit: column 256 is IV.

Can I use the result as a cell reference in another formula?
The conversion formula returns text, not a reference. For calculations, use reference-based functions such as INDEX where possible. If you genuinely need to turn text into a reference, Excel has INDIRECT, but text-based references can be harder to maintain when sheets or ranges change.

Why does Excel show a formula error after I paste it?
Check that the input is a valid column number and that the formula uses the right cell reference. Also check your regional list separator: some Excel settings require semicolons instead of commas between arguments.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.