Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Check Excel Data Types with pandas

Find out what pandas actually read from a spreadsheet — object columns hiding mixed types, numbers stored as text, Excel date serials — and coerce them with a record of every failure.

df.dtypes after a read_excel is the shortest description of what a spreadsheet actually contained. A column of object where you expected float64 means at least one cell is not a number, and a column silently converted to int64 may have thrown away the leading zeros of an account code. This guide, part of Validating Excel Data with Python, turns that signal into a set of checks you can run on every import.

How one bad cell changes a whole column's dtype A quantity column containing 4, 2 and 7 is read as int64. The same column with one cell containing the text n/a is read as object, so every value in it becomes a Python object and arithmetic silently stops working. all cells numeric Quantity 4 2 7 dtype: int64 — arithmetic works one cell is text Quantity 4 n/a 7 dtype: object — sums silently fail

Prerequisites

Bash
pip install pandas openpyxl

Build a workbook with type problems

Python
import pandas as pd

pd.DataFrame([
    {"Code": "00412", "Quantity": 4, "Unit_Price": 19.99, "Order_Date": "2026-01-14"},
    {"Code": "00733", "Quantity": "2", "Unit_Price": "49,50", "Order_Date": "15/01/2026"},
    {"Code": "01120", "Quantity": "n/a", "Unit_Price": 12.25, "Order_Date": 45678},
    {"Code": "02001", "Quantity": 5, "Unit_Price": None, "Order_Date": "2026-02-30"},
]).to_excel("types.xlsx", index=False, sheet_name="Orders")

Four rows carrying the four classic type problems: a code with leading zeros, a number typed as text, a European decimal comma, and dates in three incompatible forms including an Excel serial number and an impossible date.

Look before you convert

Read once with everything as object, then describe what arrived. Nothing is guessed, so nothing is hidden:

Python
import pandas as pd

df = pd.read_excel("types.xlsx", sheet_name="Orders", dtype=object)

def profile(frame):
    rows = []
    for column in frame.columns:
        series = frame[column]
        present = series.dropna()
        kinds = sorted({type(v).__name__ for v in present})
        rows.append({
            "column": column,
            "dtype": str(series.dtype),
            "non_null": int(series.notna().sum()),
            "python_types": ", ".join(kinds) or "—",
            "sample": present.iloc[0] if len(present) else None,
        })
    return pd.DataFrame(rows)

print(profile(df).to_string(index=False))

The python_types column is the useful one. A column reporting float, str is a mixed column, and it is far better to learn that here than from a TypeError three transformations later. Columns whose values are all str may still be numbers — they are simply numbers that were typed as text in Excel.

Coerce, and keep the failures

Conversion and validation are the same operation when you record what did not convert:

Python
import pandas as pd

def to_number(series, decimal_comma=False):
    text = series.astype(str).str.strip()
    text = text.str.replace(r"[£$€\s]", "", regex=True)
    if decimal_comma:
        text = text.str.replace(".", "", regex=False).str.replace(",", ".", regex=False)
    else:
        text = text.str.replace(",", "", regex=False)
    return pd.to_numeric(text.replace({"": None, "nan": None, "n/a": None, "-": None}),
                         errors="coerce")

def coerce_with_report(frame, numeric=(), dates=()):
    out, failures = frame.copy(), []
    for column in numeric:
        converted = to_number(frame[column])
        broke = converted.isna() & frame[column].notna() & (frame[column].astype(str).str.strip() != "")
        failures += [{"row": int(i) + 2, "column": column, "value": frame.loc[i, column],
                      "problem": "not a number"} for i in frame.index[broke]]
        out[column] = converted
    for column in dates:
        converted = pd.to_datetime(frame[column], errors="coerce", format="mixed", dayfirst=True)
        broke = converted.isna() & frame[column].notna()
        failures += [{"row": int(i) + 2, "column": column, "value": frame.loc[i, column],
                      "problem": "not a date"} for i in frame.index[broke]]
        out[column] = converted
    return out, failures

typed, failures = coerce_with_report(df, numeric=["Quantity", "Unit_Price"], dates=["Order_Date"])
print(typed.dtypes)
for failure in failures:
    print(failure)

Stripping currency symbols and thousands separators before to_numeric is what makes the difference between "three quarters of the column failed" and "one genuinely bad value failed". Note the decimal_comma switch: a European export where 49,50 means forty-nine and a half needs the opposite treatment to a US file where 49,500 means forty-nine thousand five hundred, and no heuristic can tell them apart reliably. Ask the source, then set the flag.

The same string means different numbers in different locales The text 49,50 becomes 49.5 when read with a decimal comma and 4950 when the comma is treated as a thousands separator. The text 1,234.50 is only valid in the dot-decimal reading. The locale must come from the source, not from a guess. as typed read as dot-decimal read as comma-decimal "49,50" 4950.0 49.5 "1,234.50" 1234.5 NaN Both readings are "correct" — only the source can say which applies. Record the locale per feed; never infer it per file.

Dates: three forms in one column

Excel stores real dates as serial numbers counted from 1899-12-30. When a column mixes serials, ISO strings and day-first strings — which happens whenever people paste between files — handle the numbers separately:

Python
import pandas as pd

EXCEL_EPOCH = pd.Timestamp("1899-12-30")

def to_datetime_mixed(series, dayfirst=True):
    values = series.copy()
    numeric = pd.to_numeric(values, errors="coerce")
    from_serial = EXCEL_EPOCH + pd.to_timedelta(numeric, unit="D")
    from_text = pd.to_datetime(values.where(numeric.isna()), errors="coerce",
                               format="mixed", dayfirst=dayfirst)
    return from_serial.fillna(from_text)

parsed = to_datetime_mixed(df["Order_Date"])
print(parsed)

45678 becomes a real date instead of failing, 15/01/2026 is read day-first, and 2026-02-30 stays NaT because no calendar has that day. The dayfirst flag carries the same warning as the decimal comma: 03/04/2026 is either March or April depending on who typed it, and a wrong guess produces dates that are plausible, wrong, and unnoticeable.

Assert the schema you expect

Once coercion is done, state the contract. A single assertion catches a source system that changes a column's meaning between runs:

Python
import pandas as pd
from pandas.api import types as pdt

EXPECTED = {
    "Code": pdt.is_string_dtype,
    "Quantity": pdt.is_numeric_dtype,
    "Unit_Price": pdt.is_numeric_dtype,
    "Order_Date": pdt.is_datetime64_any_dtype,
}

def assert_schema(frame):
    problems = [
        f"{column}: expected {check.__name__.replace('is_', '').replace('_dtype', '')}, got {frame[column].dtype}"
        for column, check in EXPECTED.items()
        if column in frame.columns and not check(frame[column])
    ]
    if problems:
        raise TypeError("schema check failed — " + "; ".join(problems))
    return True

typed["Code"] = typed["Code"].astype("string")
assert_schema(typed)
print("schema OK")

Keeping Code as a string is the point of that first line. Read without care, 00412 becomes the integer 412, and the join against a reference table quietly matches nothing. Any identifier — account numbers, SKUs, postcodes, phone numbers — should be a string from the moment it is read, which is one more reason to read with dtype=object and convert deliberately.

Report the type failures with the data

Python
import pandas as pd

with pd.ExcelWriter("types_checked.xlsx", engine="openpyxl") as writer:
    typed.to_excel(writer, sheet_name="Typed", index=False)
    pd.DataFrame(failures).to_excel(writer, sheet_name="Type_Issues", index=False)

print(f"{len(failures)} type failure(s) written to Type_Issues")

The row numbers in that sheet are spreadsheet rows, so a submitter can jump straight to the cell. Pair it with highlighting the invalid cells on the original data and the fix takes seconds rather than an email exchange.

What each dtype is telling you

The dtype after a read is a summary of the whole column, and each value carries a specific message about the data behind it:

What each pandas dtype implies about the spreadsheet column int64 means every cell was a whole number. float64 means numbers with at least one decimal or blank. object means mixed or text content and needs investigation. datetime64 means every cell parsed as a date. string means the column was read or cast as text, which is what identifiers need. int64 every cell was a whole number — safe to sum float64 numbers, with decimals or blanks present object mixed or text — profile it before trusting anything datetime64[ns] / string parsed dates, or text you chose to keep as text

The pair to watch is int64 and object in the same column across two runs of the same report. It means one submission was clean and the next contained a stray note, and any code that indexed or joined on that column will behave differently between the two — usually without raising anything.

Common pitfalls and gotchas

SymptomCauseFix
Numeric column has dtype objectOne or more non-numeric cellsProfile the column, then coerce with a failure report
Leading zeros disappearedColumn was inferred as integerRead as object/str and keep it a string
TypeError when summingMixed str and float in one columnCoerce before any arithmetic
Dates off by four yearsWorkbook uses the 1904 date systemUse the 1904 epoch for that file
European decimals ten times too largeComma treated as a thousands separatorSet the decimal convention per source feed
to_datetime warns about inconsistent parsingGenuinely mixed formatsPass format="mixed", and split serials out first
NaN and NaT counted as validCoerced values never checkedCompare against the original to find what broke

Performance and scale notes

Profiling with a type() set per column is a Python-level loop, so on very large frames sample it — frame.head(5000) is enough to reveal a mixed column. Coercion itself is vectorised and fast; the slow part is usually the regular expression stripping currency symbols, which you can skip for columns that arrive clean. Reading with dtype=object costs more memory than letting pandas infer, so for a genuinely large workbook validate a sample with nrows, then read the full file with the dtypes you have just proven correct.

Conclusion

Type checking a spreadsheet is three deliberate moves rather than one hopeful read: read raw so nothing is guessed, profile what actually arrived, then coerce each column deliberately while recording every value that refused to convert. Keep identifiers as strings, get the decimal and date conventions from the source rather than from a guess, and finish with a schema assertion so a changed source system fails loudly instead of quietly reporting the wrong numbers.

Frequently asked questions

Why is my numeric column dtype object? At least one cell is not a number — usually a stray "n/a", a dash, or a number typed with a thousands separator. pandas falls back to object for the whole column.

How do I see which values broke the conversion? Coerce with errors="coerce", then compare the result against the original: rows that are NaN afterwards but were non-blank before are exactly the failures.

Should I pass dtype to read_excel? Pass dtype=object when validating, so nothing is guessed, then coerce explicitly. Passing a strict dtype up front raises on the first bad value and tells you nothing about the rest.

Why did my ID column lose its leading zeros? It was inferred as numeric. Read it as a string with dtype={"Code": str} — or read everything as object and never let the inference run.

Up to the parent guide:

Related guides: