Python Excel AutomationPractical guides for developers
Fix
Getting Started With Python Excel AutomationDeep dive

Fix "Usecols do not match columns" in pandas read_excel

ValueError: Usecols do not match columns, columns expected but not found — and its cousin "Defining usecols with out-of-bounds indices is not allowed" — mean the names or positions you asked for are not in the header pandas actually read. Print the real header, account for title rows, trailing spaces and renamed columns, and select columns with a forgiving callable so a cosmetic change in the source does not break the job.
Errors & fixesReading Excel files

Selecting columns at read time is good practice — pd.read_excel(path, usecols=["Region", "Units"]) reads less and fails early if the layout changes. The failure it produces is this one:

Text
ValueError: Usecols do not match columns, columns expected but not found: ['Region'] (sheet: 0)

The message is literal: among the column names pandas read from the header row, there is none called Region. The usual reasons are that pandas read the wrong row as the header, that the real header has an invisible difference such as a trailing space, or that someone renamed the column in the source. A related error, Defining usecols with out-of-bounds indices is not allowed, appears when you select by letter or position past the last column. This page shows how to see what pandas saw, and how to make the selection robust. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

Bash
pip install pandas openpyxl

q3_sales.xlsx has a title in row 1, an empty row 2 and the real header in row 3. The header has the usual imperfections: Region with a trailing space, and Revenue (EUR) rather than Revenue.

Step 1 — Reproduce the error

Python
import pandas as pd

for kwargs in ({"usecols": ["Region", "Revenue"]},
               {"usecols": ["Region", "Units"], "header": 2}):
    try:
        pd.read_excel("q3_sales.xlsx", **kwargs)
    except ValueError as exc:
        print(kwargs, "->", exc)

The first call reads row 1 as the header — where the only name is the title — so neither column is found. The second call points header=2 at the right row (zero-based, so the third row), and Units is now found, but Region still is not, because the header cell actually says Region with a space.

Step 2 — Print the header pandas actually read

Every fix starts with the real names. nrows=0 reads only the header, which is fast even on big files:

Python
for header in (0, 2):
    cols = pd.read_excel("q3_sales.xlsx", header=header, nrows=0).columns
    print(f"header={header}:", [repr(c) for c in cols])

With header=0 the only column name is the report title — a sure sign that the header is further down; when the title row is followed by data in other columns you would also see Unnamed: n placeholders, as explained in Fix "Unnamed: 0" Columns When Reading Excel. With header=2 the real names appear, and repr makes the trailing space visible: 'Region '. If the header row moves between files, find it programmatically with the approach in Find the Header Row in an Excel Sheet with a Shifting Layout.

The header pandas has to match against The sheet with a title in row 1, an empty row 2 and the header in row 3 reading Region with a trailing space, Product, Units and Revenue (EUR). usecols names must match row 3 exactly, including the space. A B C D 1 Sales report, Q3 2026 2 3 Region␣ Product Units Revenue (EUR) 4 North Drill 3 267.0 5 South Saw 5 227.5 header=2 zero-based row index names must match exactly

Step 3 — Match names exactly, or forgivingly

The quick fix is to use the exact names, space included:

Python
df = pd.read_excel("q3_sales.xlsx", header=2, usecols=["Region ", "Units", "Revenue (EUR)"])
print(list(df.columns))

That works today and breaks the day someone tidies the header. A callable is more robust: usecols accepts a function that receives each column name and returns True to keep it. Normalise the names inside it:

Python
def norm(name):
    return " ".join(str(name).split()).lower()

wanted = {"region", "units", "revenue (eur)"}
df = pd.read_excel("q3_sales.xlsx", header=2, usecols=lambda c: norm(c) in wanted)
df.columns = [norm(c) for c in df.columns]
print(df)

The callable ignores case, leading and trailing spaces and doubled spaces, so Region , REGION and Region all match. Normalising the resulting column names straight away means the rest of the code uses one spelling. The trade-off: a callable never raises — if a column truly disappears, it is silently missing — so check afterwards.

Exact names versus a callable in usecols A list of exact names fails loudly when any name differs, even by a space. A callable that normalises names tolerates spacing and case differences but never fails, so the result must be checked for missing columns. usecols=["Region ", …] fails loudly when a column is missing also fails on a stray space or case change breaks when the header is tidied usecols=lambda c: … tolerates spacing and case one spelling for later code never raises — check what came back

Step 4 — Check for missing columns yourself

Pair the forgiving read with an explicit check that produces a message people can act on:

Python
missing = wanted - set(df.columns)
if missing:
    raise ValueError(f"q3_sales.xlsx is missing columns: {sorted(missing)}; "
                     f"found {sorted(df.columns)}")
print("all required columns present:", sorted(df.columns))

Listing what was found next to what was expected turns a cryptic failure into an obvious one: "missing revenue (eur); found region, revenue, units" tells the owner of the file exactly what changed. For full schema checks — types, ranges, allowed values — see Validate Excel Columns Before Import with pandas.

Step 5 — Out-of-bounds letters and positions

Selecting by letter range or position avoids name problems but has its own failure:

Python
for spec in ("A:C", "A,E", [0, 5]):
    try:
        cols = list(pd.read_excel("q3_sales.xlsx", header=2, usecols=spec).columns)
        print(f"{spec!s:7} -> {cols}")
    except Exception as exc:
        print(f"{spec!s:7} -> {type(exc).__name__}: {exc}")

"A:C" works. "A,E" and [0, 5] ask for columns beyond the sheet's last used column (D), and pandas raises ParserError: Defining usecols with out-of-bounds indices is not allowed. [4] are out-of-bounds. — the index is zero-based, so [4] is column E. Positions also shift silently when a column is inserted, reading the wrong data with no error at all, which is why names (normalised) are usually the safer choice for files maintained by people. Column letters are covered in Read Specific Columns from Excel with pandas.

Which usecols form fails how Names fail with Usecols do not match columns when a name is absent. Letter ranges and positions fail with out-of-bounds indices when they go past the last column, and silently read the wrong column when columns are inserted. A callable never fails. missing name past last column column inserted list of names ValueError n/a still correct letters "A:C" n/a ParserError wrong data, no error positions [0, 2] n/a ParserError wrong data, no error callable silently skipped n/a still correct

Step 6 — Accept known alternative spellings

Files from different branches or different years rarely agree on headers: one says Revenue (EUR), another Revenue, a third Sales value. Rather than patching the code for each, keep a small alias table and resolve each file's real names against it before selecting:

Python
ALIASES = {
    "region": ["region", "area", "sales region"],
    "units": ["units", "qty", "quantity"],
    "revenue": ["revenue (eur)", "revenue", "sales value"],
}

def resolve(path, header):
    found = {norm(c): c for c in pd.read_excel(path, header=header, nrows=0).columns}
    mapping, missing = {}, []
    for target, options in ALIASES.items():
        match = next((found[o] for o in options if o in found), None)
        if match is None:
            missing.append(target)
        else:
            mapping[match] = target
    if missing:
        raise ValueError(f"{path}: no column for {missing}; header was {list(found.values())}")
    return mapping

mapping = resolve("q3_sales.xlsx", header=2)
df = pd.read_excel("q3_sales.xlsx", header=2, usecols=list(mapping)).rename(columns=mapping)
print(mapping)
print(df.head(2))

resolve reads only the header, picks the first alias present for each target name, and returns a mapping from the file's actual spelling to yours. The exact names then go into usecols — so the read itself still fails loudly if something changes between the two calls — and rename gives every file the same column names. When a new spelling appears, the error message shows the full header, and the fix is one more entry in the alias list rather than a code change. The same idea at a larger scale is covered in Merge Excel Files with Different Column Names.

Performance notes

usecols saves less time than people expect. The engine still has to parse every cell of the sheet's XML to find the columns you asked for; only building the DataFrame gets cheaper. On a 40,000-row, 20-column sheet, reading 2 columns instead of 20 took 2.5 rather than 2.8 seconds with openpyxl, and 0.42 rather than 0.45 seconds with calamine. Use usecols for clarity and early failure, and switch engines when speed matters, as measured in Speed Up pandas Excel Reads with the Calamine Engine.

Common pitfalls

  • Wrong header row. header is zero-based; a title above the table needs header=1, 2, and so on.
  • Invisible differences. Trailing spaces, non-breaking spaces and line breaks inside header cells; print with repr.
  • The same error from read_csv. The message and fixes are identical for CSV files.
  • Duplicate header names. pandas renames the second one to Name.1; select it by that name or by position.
  • Selecting by position on human-edited files. An inserted column shifts everything without an error.

Conclusion

"Usecols do not match columns" means at least one requested name is not in the header pandas read. Print the header with nrows=0 and repr, set header to the right row, and either use the exact names or — better for files people edit — a callable that normalises names, followed by an explicit check for missing columns. Letter and position selections fail with "out-of-bounds indices" past the last column and silently misread when columns move.

Frequently asked questions

What does "Usecols do not match columns, columns expected but not found" mean? pandas read the header row and none of its names matched some of the names you passed in usecols. Usually the header is on a different row, or a name differs by spaces, case or wording.

How do I see the column names pandas reads from an Excel sheet? Read only the header with pd.read_excel(path, header=n, nrows=0).columns and print each name with repr() so trailing spaces and odd characters are visible.

How can I make usecols ignore case and spaces? Pass a function: usecols=lambda c: " ".join(str(c).split()).lower() in wanted, where wanted holds the normalised names. Check afterwards that all required columns were found, because a callable never raises.

What does "Defining usecols with out-of-bounds indices is not allowed" mean? A column letter or position in usecols is beyond the last column of the sheet. The index in the message is zero-based, so 4 refers to column E.

More on errors & fixes

More on errors & fixes →