Fix read_excel Returning an Empty DataFrame

pd.read_excel("report.xlsx") runs without an error and returns Empty DataFrame, or a frame with the right columns and no rows, or the right shape filled with NaN — while the file, opened in Excel, plainly contains data. No exception means no message pointing at the cause, so people try arguments at random. There are only a handful of causes, though, and each leaves a recognisable signature: which sheet was read, how many columns came back, whether the header is right, whether values are missing or the rows themselves are. This page reproduces each cause on one workbook, explains the signature, and builds a small diagnostic that inspects the file with openpyxl and says where the data actually is. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

pip install pandas openpyxl

The sample workbook deliberately has the shapes that cause trouble in real files: an empty Cover sheet first, a Sales data sheet whose table starts on row 4 under a title and a note and ends with a totals row, and a Calc sheet of formulas written by Python and never opened in Excel.

Step 1 — The wrong sheet

import pandas as pd

df = pd.read_excel("report.xlsx")
print(df.shape, "| columns:", list(df.columns))
with pd.ExcelFile("report.xlsx") as xf:
    print(xf.sheet_names)

read_excel reads the first sheet unless told otherwise, and here the first sheet is an empty cover page, so the result is an empty frame with no columns. Listing the sheet names with pd.ExcelFile(...).sheet_names shows where the data is — or, to see each sheet's header without loading anything, use the approach in List Sheet Names and Column Headers Without Loading the Data; pass the name with sheet_name="Sales data". Reading by name rather than by position also protects the script when someone adds a sheet in front. If the sheet name itself varies — Sales data, Sales Data (2) — match it from the list with a case-insensitive comparison instead of typing it, and fail with the list of available names when nothing matches. Signature: shape (0, 0) and no error.

Step 2 — The header is not on the first row

raw = pd.read_excel("report.xlsx", sheet_name="Sales data")
print(raw.shape, list(raw.columns)[:2])
print(pd.read_excel("report.xlsx", sheet_name="Sales data", skiprows=10).shape)
print(pd.read_excel("report.xlsx", sheet_name="Sales data", header=3).head(2).to_string())

Read without arguments, the title in A1 becomes the only column name and the real header ends up as a data row — a frame with the wrong columns and Unnamed labels rather than an empty one. Overshooting in the other direction is what produces an empty result: skiprows=10 skips past every data row, leaving nothing. header=3 — the zero-based row index of the header, so Excel row 4 — gives the right columns and data. skiprows=3 achieves the same here by skipping the three rows above the header, after which the next row is used as the header; the two differ only when blank rows are involved, because header counts rows as they appear in the sheet. Notice that Units reads as 120.0 rather than 120: the totals row further down holds a formula without a saved result, which puts NaN in the column and forces it to floats — one more reason to trim the footer, as the next step does. When the header row varies between files, find it rather than hard-coding it, as the diagnostic in Step 4 does and as covered in Skip Rows and Set the Header When Reading Excel with pandas.

Step 3 — Arguments that trim everything, and formulas without results

args = dict(sheet_name="Sales data", header=3)
try:
    pd.read_excel("report.xlsx", usecols="F:H", **args)
except Exception as e:
    print("usecols='F:H'   ", type(e).__name__, str(e).split(". (")[0][:70])
print("nrows=0         ", pd.read_excel("report.xlsx", nrows=0, **args).shape)
print("skipfooter=10   ", pd.read_excel("report.xlsx", skipfooter=10, **args).shape)
print("skipfooter=2    ", pd.read_excel("report.xlsx", skipfooter=2, **args)["Region"].tolist())
calc = pd.read_excel("report.xlsx", sheet_name="Calc")
print(calc.to_string(index=False))

usecols pointing at columns beyond the data is not silent in current pandas — it raises a ParserError naming the out-of-bounds columns, which at least says where to look; older versions returned an empty frame. nrows=0 keeps only the header — useful for reading column names, surprising otherwise. A skipfooter larger than the data removes every row; the right value here is 2, which drops the blank row and the totals row and keeps the four regions. The Calc sheet shows the other signature: the right shape, with the formula column entirely NaN. pandas reads the cached results of formulas, and a workbook written by openpyxl or XlsxWriter has none until Excel or LibreOffice has opened, calculated and saved it. The data is not missing — it has never been calculated; Fix Formula Cells Reading as NaN in pandas covers recalculating such files.

Step 4 — A diagnostic that finds the data

When a file arrives and a read comes back empty, inspect the workbook rather than guessing arguments:

from openpyxl import load_workbook

def where_is_the_data(path, min_filled=2):
    wb = load_workbook(path, read_only=True)
    report = []
    for ws in wb.worksheets:
        rows = list(ws.iter_rows(values_only=True))
        filled = [sum(v is not None for v in r) for r in rows]
        header = next((i for i, n in enumerate(filled) if n >= min_filled), None)
        data_rows = sum(1 for n in filled[header + 1:] if n >= min_filled) if header is not None else 0
        report.append({"sheet": ws.title, "state": ws.sheet_state, "used rows": len(rows),
                       "header (0-based)": "" if header is None else header, "data rows": data_rows,
                       "suggest": f'sheet_name="{ws.title}", header={header}' if header is not None else "no table found"})
    wb.close()
    return pd.DataFrame(report)

print(where_is_the_data("report.xlsx").to_string(index=False))

For every sheet, the diagnostic reports how many rows are used, the first row with at least two filled cells — the likely header — and how many data rows follow, and suggests the matching read_excel arguments. The Cover sheet has no table; Sales data has its header at index 3 with five filled rows below it (the four regions and the totals row); Calc has its header at row 0. Raise min_filled for wide tables whose title rows also span a couple of cells. Running this on an unfamiliar file takes a second and replaces trial and error with a direct answer. It reads with openpyxl's read_only=True, so it stays fast on large workbooks, and it reports hidden sheets, which are another place data turns up unexpectedly.

Check you read the file you think you read

One more cause produces exactly the same symptoms and has nothing to do with arguments: reading a different file from the one you opened in Excel. A relative path resolves against the script's working directory, which differs between an IDE, a terminal and a scheduler, so report.xlsx can mean an older copy elsewhere. Cloud-synced folders can hold a placeholder until the file is downloaded, and a file being written by another process may be read half-finished. Print Path(path).resolve(), the file's size and its modification time before reading when something looks wrong; if they do not match what you expect, the arguments were never the problem. Scripts that run unattended should use absolute paths built from a configuration value rather than relying on the current directory.

Common pitfalls

• Relying on the first sheet. Pass sheet_name by name.
• Guessing skiprows. Find the header row; overshooting empties the frame.
• skipfooter larger than the footer. It removes data rows too.
• usecols on the wrong letters. Check where the table really is; out-of-range columns raise an error.
• NaN in formula columns. The workbook was never calculated; recalculate it first.

Conclusion

An empty result from read_excel almost always means the arguments point away from the data: the default first sheet is empty, skiprows or header miss the header row, usecols, nrows or skipfooter trim everything, or formula cells have never been calculated. Recognise the signature — no columns, no rows, Unnamed columns or NaN formulas — list the sheets, find the header row, and pass sheet_name and header explicitly; a small openpyxl diagnostic finds them for any workbook.

Frequently asked questions

Why does pandas read_excel return an empty DataFrame? Usually because it read the first sheet, which is empty, or because skiprows, header, usecols, nrows or skipfooter point away from the data. List the sheets and pass sheet_name and header explicitly.

How do I see which sheets an Excel file has in pandas? Use pd.ExcelFile(path).sheet_names, then read the one you need with sheet_name.

Why are my formula columns NaN when reading with pandas? pandas reads saved formula results, and files written by Python libraries have none until opened and saved in Excel or recalculated with LibreOffice.

How do I find the header row automatically? Scan the sheet with openpyxl for the first row with several filled cells and pass its zero-based index to read_excel as header.

Related

• Up one level: Troubleshooting Common Python Excel Errors — fixes for common errors.
• Fix Unnamed: 0 Columns When Reading Excel — the header's other symptom.
• Read All Sheets from an Excel File into DataFrames — reading every sheet.
• Stop Reading at Notes and Footers Below the Data — trimming the bottom.