Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Convert Excel Column Letters and Numbers in Python

Turn 28 into "AB" and "XFD" into 16384 with openpyxl's get_column_letter and column_index_from_string, parse cell and range addresses, and map DataFrame columns to letters for formulas — plus why naive base-26 code breaks at Z and the 0-based/1-based traps between pandas, openpyxl and xlsxwriter.
openpyxlFormulas in workbooksWriting Excel files

Sooner or later every script that writes formulas needs to turn a column position into a letter. pandas knows the amount column is the fourth; the formula has to say D2:D200. And every script that reads a configuration like "the totals are in column AB" needs the reverse. It looks like a one-line conversion to base 26, and that one line is wrong for every column after Z. This page, part of Using openpyxl for Excel File Manipulation, covers the library helpers, the correct algorithm, address parsing, and mapping DataFrame columns to letters.

Where column letters change length Milestones along the column axis. Column 1 is A and column 26 is Z. Column 27 is AA, the first two-letter column. Column 702 is ZZ and column 703 is AAA. Column 16,384 is XFD, the last column Excel supports. 1 → A first column 26 → Z last single letter 27 → AA no zero digit, so AA follows Z 702 → ZZ last two-letter column 703 → AAA three letters begin 16,384 → XFD Excel's last column

Prerequisites

Bash
pip install openpyxl pandas xlsxwriter

Step 1 — Use the openpyxl helpers

Two functions cover the everyday cases. Both count from 1, like Excel:

Python
from openpyxl.utils import get_column_letter, column_index_from_string

print([get_column_letter(n) for n in (1, 26, 27, 52, 53, 702, 703, 16384)])
# ['A', 'Z', 'AA', 'AZ', 'BA', 'ZZ', 'AAA', 'XFD']

print([column_index_from_string(s) for s in ("A", "Z", "AA", "AZ", "ZZ", "AAA", "XFD", "xfd")])
# [1, 26, 27, 52, 702, 703, 16384, 16384]

Lower-case letters are accepted, which helps when addresses come from configuration files or user input. get_column_letter(0) raises ValueError, which is usually the first sign that a 0-based index from pandas has leaked into openpyxl code.

Step 2 — Understand why naive base-26 fails

Column letters look like base 26 but have no zero digit: after Z comes AA, not "BA" or "A0". The common first attempt maps 1–26 correctly and then goes wrong:

Python
def naive_letter(n):
    s = ""
    while n:
        n, r = divmod(n, 26)
        s = chr(64 + r) + s
    return s

print(naive_letter(26), naive_letter(27), naive_letter(52))
# A@ AA B@  — wrong for every multiple of 26

The fix is to subtract one before each division, which turns the 1–26 digits into 0–25:

Python
def col_letter(n):
    if not 1 <= n <= 16384:
        raise ValueError(f"column {n} is outside Excel's range 1-16384")
    s = ""
    while n:
        n, r = divmod(n - 1, 26)
        s = chr(65 + r) + s
    return s

def col_number(letters):
    n = 0
    for ch in letters.upper():
        n = n * 26 + (ord(ch) - 64)
    return n

assert all(col_letter(n) == get_column_letter(n) for n in range(1, 16385))
assert all(col_number(get_column_letter(n)) == n for n in range(1, 16385))
print(col_letter(28), col_number("AB"))
# AB 28

The two asserts check every valid Excel column against openpyxl, so the functions are safe to use where openpyxl is not installed — in a small validation script, say. Most of the time, use the library.

The bijective base-26 conversion A six-line Python function converting a column number to letters. Callouts mark the range check against Excel's 16,384 columns, the subtraction of one before divmod that makes the digits run from 0 to 25, and the use of 65 (the code for A) to turn each digit into a letter. col_letter.py 1 def col_letter(n): 2 1 if not 1 <= n <= 16384: 3 raise ValueError("outside A-XFD") 4 s = "" 5 while n: 6 2 n, r = divmod(n - 1, 26) 7 3 s = chr(65 + r) + s 8 return s 1 Excel has 16,384 columns 2 n - 1 makes each digit 0-25: no zero letter 3 chr(65) is "A"

Step 3 — Validate against Excel's real limit

openpyxl's helpers accept more than Excel does. They work up to ZZZ, column 18,278, while Excel stops at XFD, column 16,384:

Python
print(get_column_letter(16385), column_index_from_string("XFE"))
# XFE 16385 — accepted by openpyxl, beyond Excel's last column

A computed column past XFD produces a workbook Excel cannot represent. Check before writing whenever a column number comes from data — the width of a wide pivot, the number of days in a range:

Python
MAX_COL = 16384

def safe_letter(n):
    if not 1 <= n <= MAX_COL:
        raise ValueError(f"column {n} is beyond Excel's limit of {MAX_COL} (XFD)")
    return get_column_letter(n)

try:
    safe_letter(20000)
except ValueError as exc:
    print(exc)

The row limit is 1,048,576; Fix "This sheet is too large" When Writing Excel with pandas covers what to do when data exceeds either.

Step 4 — Parse cell and range addresses

openpyxl.utils.cell has helpers for the other half of the job — taking addresses apart and putting them together:

Python
from openpyxl.utils.cell import coordinate_from_string, range_boundaries, range_to_tuple
from openpyxl.utils import absolute_coordinate, quote_sheetname

print(coordinate_from_string("AB12"))             # ('AB', 12)
print(range_boundaries("B3:F20"))                  # (2, 3, 6, 20) = min_col, min_row, max_col, max_row
print(range_boundaries("C:E"), range_boundaries("2:4"))
print(range_to_tuple("'Sales Data'!B3:F20"))       # ('Sales Data', (2, 3, 6, 20))
print(absolute_coordinate("B3:F20"))               # $B$3:$F$20
print(quote_sheetname("Sales Data"), quote_sheetname("O'Brien"))

range_boundaries returns numbers in the order min_col, min_row, max_col, max_row — columns first, which trips people who expect row first — and None for the open ends of whole-column or whole-row ranges. quote_sheetname always adds quotes and doubles any apostrophe inside the name, which is exactly what a cross-sheet formula needs.

Step 5 — Map DataFrame columns to letters

The most common real use is writing formulas under a DataFrame that pandas has just written. Build the mapping once from the DataFrame's column positions, allowing for startcol and the index if it was written:

Python
import pandas as pd
from openpyxl import load_workbook

sales = pd.DataFrame({"region": ["North", "South", "East"], "units": [12, 7, 9], "revenue": [1200.0, 700.0, 950.0]})
start_col = 1                                     # write from column B
sales.to_excel("sales.xlsx", index=False, startcol=start_col)

letters = {name: get_column_letter(start_col + i + 1) for i, name in enumerate(sales.columns)}
print(letters)
# {'region': 'B', 'units': 'C', 'revenue': 'D'}

wb = load_workbook("sales.xlsx")
ws = wb.active
last = len(sales) + 1                             # header is row 1
total_row = last + 1
ws[f"{letters['region']}{total_row}"] = "Total"
for col in ("units", "revenue"):
    ws[f"{letters[col]}{total_row}"] = f"=SUM({letters[col]}2:{letters[col]}{last})"
wb.save("sales.xlsx")
print([c.value for c in ws[total_row]])

Deriving letters from the DataFrame rather than hard-coding "C" and "D" keeps the formulas right when a column is added or reordered next month. If the index is written, it occupies the first column, so add one more to every position.

DataFrame columns mapped to worksheet letters A worksheet written from a DataFrame with startcol=1, so the data begins in column B: region in B, units in C, revenue in D. A total row uses the mapped letters in its formulas, =SUM(C2:C4) and =SUM(D2:D4). sales.xlsx — written with startcol=1 A B C D 1 region units revenue 2 North 12 1200 3 South 7 700 4 East 9 950 5 Total =SUM(C2:C4) =SUM(D2:D4) letters from positions get_column_letter(start_col + i + 1)

Step 6 — Use letters when reading with pandas

The conversion also runs the other way when a colleague says "the figures are in column AB". read_excel accepts letters directly in usecols, including ranges and lists, so often no conversion is needed at all:

Python
wide = pd.DataFrame([[f"r{r}c{c}" for c in range(1, 31)] for r in range(3)],
                    columns=[f"col{c}" for c in range(1, 31)])
wide.to_excel("wide.xlsx", index=False)

print(pd.read_excel("wide.xlsx", usecols="AB").columns.tolist())
print(pd.read_excel("wide.xlsx", usecols="A,C:E").columns.tolist())

full = pd.read_excel("wide.xlsx")
print(full.iloc[:, column_index_from_string("AB") - 1].name)

When the whole sheet is already in a DataFrame, subtract one from the column number to get a pandas position for iloc. That subtraction — or the addition in the other direction — is the single place in a script where the two numbering systems should meet.

Step 7 — Keep 0-based and 1-based straight

Three libraries, two conventions. pandas positions (iloc, get_loc) and xlsxwriter's row/column arguments count from 0; openpyxl and Excel count from 1. xlsxwriter has its own helpers that follow its 0-based convention:

Python
from xlsxwriter.utility import xl_col_to_name, xl_rowcol_to_cell, xl_cell_to_rowcol

print(xl_col_to_name(0), xl_rowcol_to_cell(0, 0), xl_cell_to_rowcol("B3"))
# A A1 (2, 1)
print(get_column_letter(1), sales.columns.get_loc("units"))
# A 1

xl_col_to_name(0) and get_column_letter(1) both give "A". Mixing the two families in one script is the classic source of formulas that sit one column to the left of where they should. Pick the helpers that match the library you are writing with, and convert explicitly (+ 1 or - 1) at the one place where a pandas position crosses into openpyxl.

Common pitfalls

  • Passing a pandas position to get_column_letter. Position 0 raises; position 1 gives "A" for what pandas calls the second column. Add one.
  • Forgetting the index column. to_excel with the default index=True shifts every data column right by one.
  • Expecting row-first from range_boundaries. It returns columns first.
  • Addresses copied from formulas. Configuration values like $AB$2 or 'Q1 Sales'!AB2 carry dollar signs and sheet names. Strip the $ before calling column_index_from_string, and split off the sheet with range_to_tuple rather than with string slicing, which breaks on quoted names containing !.
  • Trusting openpyxl to reject XFE. It does not; validate against 16,384 yourself.

Performance notes

Both conversions are a few arithmetic operations; converting all 16,384 columns takes milliseconds. If a hot loop converts the same numbers repeatedly, a list of all letters built once — [get_column_letter(n) for n in range(1, 16385)] — turns each conversion into an index lookup. In practice the conversion is never the slow part of a report; the cell writes around it are.

Conclusion

Use get_column_letter and column_index_from_string (1-based) with openpyxl, or xl_col_to_name and friends (0-based) with xlsxwriter. If you write your own, subtract one before each divmod — column letters are base 26 without a zero digit. Validate computed columns against Excel's 16,384 limit, parse addresses with range_boundaries and range_to_tuple, and derive formula letters from DataFrame positions so they follow the data.

Frequently asked questions

How do I convert a column number to a letter in Python? Use get_column_letter(n) from openpyxl.utils, where n starts at 1: get_column_letter(28) returns "AB". With xlsxwriter, xl_col_to_name(n) does the same but counts from 0.

Why does my own conversion give wrong letters after Z? Column letters are not ordinary base 26: there is no zero digit, so AA follows Z. Code that uses divmod(n, 26) without subtracting one first produces "A@" or skips letters. Use the library functions or the corrected loop shown here.

What is the last column in Excel? XFD, which is column 16,384. openpyxl's helpers accept letters up to ZZZ (18,278), so check the limit yourself before writing to a computed column.

How do I get the column letter for a DataFrame column? Find its position with df.columns.get_loc(name), add one for openpyxl's 1-based numbering plus any start column offset, and pass the result to get_column_letter.

More on openpyxl

More on openpyxl →