Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 64 ยท Getting Started

Working with Excel Files

Read and write Excel workbooks with pandas: read_excel options, several sheets, ExcelWriter, number formats, column widths, frozen headers and common pitfalls.

Spreadsheets are the most common data format in business. Finance sends monthly reports as workbooks, colleagues keep lists in Excel, and your own results often need to end up there so that someone else can open them. pandas reads and writes .xlsx files with read_excel() and to_excel(), and with the ExcelWriter helper you can control sheets and formatting.

pandas does not read Excel files by itself. It uses a helper library, called an engine. For modern .xlsx files the engine is openpyxl. On your computer install it once with pip install openpyxl.

Note: The browser has no file system. In the examples below the workbook lives in memory, inside an io.BytesIO object that behaves like an open binary file. Replace it with a path such as "report.xlsx" on your computer and nothing else changes. The micropip.install("openpyxl") line is only needed in the browser runner (the first run downloads the library and takes a few seconds).

Writing a DataFrame to Excel

df.to_excel(target, index=False) writes one sheet. Use index=False unless the index carries real information:

Python Write and read back a workbook

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Excel stores numbers as numbers and text as text, so the types survive the round trip. Dates are stored as Excel date serial numbers with a format and come back as datetime64 columns.

Reading with read_excel

read_excel has the same spirit as read_csv, and many of the same options: usecols, skiprows, nrows, dtype, names, na_values, index_col. The extra option is sheet_name:

  • sheet_name=0 (the default) is the first sheet.
  • sheet_name="Sales" selects by name.
  • sheet_name=[0, "Costs"] returns a dictionary of DataFrames.
  • sheet_name=None returns a dictionary with all sheets.
Python Several sheets in one workbook

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The with pd.ExcelWriter(...) as writer: block collects several DataFrames, one per sheet, and saves the workbook once when the block ends. Writing with to_excel("file.xlsx") several times would overwrite the file each time. pd.ExcelFile opens a workbook once so that you can ask for its sheet names and parse several sheets without rereading the file.

Messy sheets: headers, junk rows and ranges

Business spreadsheets are made for people, not programs. They have titles above the table, blank rows, notes below, totals at the bottom and merged cells. Use the options below to pull out the table:

Python A report with a title and a total row

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

header=2 says that the header row is the third row (counting from zero). usecols accepts letters like "A:C" as well as names or positions. nrows stops the read before totals or notes. If you cannot tell where a table ends, read everything with header=None and look at it first.

Watch for these traps:

  • Formulas: pandas reads the last calculated value that Excel saved, not the formula. If the file was produced by a program that never calculated the formulas, the cells are empty (NaN).
  • Merged cells: only the top-left cell holds the value; the others are empty. Fill the gaps with df["col"] = df["col"].ffill().
  • Numbers stored as text: a column with a small green triangle in Excel can arrive as strings. Convert with pd.to_numeric(df["col"], errors="coerce").
  • Dates: Excel has no true date type, only formatted numbers. If dates arrive as plain numbers, the cell format was not a date format.
  • Old .xls files: need the xlrd package, and pandas cannot write them. Save the old files as .xlsx first.

Formatting what you write

to_excel writes plain data. For a result people will actually open, add a little polish through the engine. With openpyxl you get the underlying worksheet from writer.sheets and set widths, number formats and a frozen header:

Python Column widths, number formats and a frozen header

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The data in the cells stays numeric: Excel only changes how the numbers look. That is exactly what you want, because the people who open the file can still sort, filter and calculate. Another engine, xlsxwriter, is popular for write-only reports with charts and conditional formatting; see the section after this one.

Tip: When all you need is a quick table that opens in Excel, write a CSV instead (df.to_csv("report.csv", index=False)). CSV is faster, smaller and works everywhere. Use .xlsx when you need several sheets, formatting or formulas.

Reports with XlsxWriter: formats, conditional formats and charts

openpyxl can read and write, but XlsxWriter is the favourite for building a polished report from scratch. It only writes (it cannot read or edit an existing file), and in return it offers a clean API for formats, conditional formatting, tables and native Excel charts. Install it with pip install xlsxwriter and select it with engine="xlsxwriter". Through ExcelWriter you reach two objects: writer.book (the workbook, which creates formats and charts) and writer.sheets["Name"] (one worksheet).

Python A formatted sheet with a chart

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The recipe is always the same: write the DataFrame with to_excel, grab the workbook and worksheet from the writer, then decorate. Remember these points:

  • Formats apply to cells you write. to_excel writes the header with its own style, so rewriting the header cells with ws.write(0, col, name, format) is the way to restyle them.
  • Rows and columns are zero-based in XlsxWriter's numeric API (ws.write(0, 0, ...) is cell A1), while range strings such as "B2:C5" use Excel's own notation.
  • Charts and conditional formats stay live in Excel. They are real Excel objects, not pictures, so the reader can edit them.
  • Auto-fit widths. Neither library measures text for you. A common trick is width = max(len(str(v)) for v in column) + 2 per column, then ws.set_column(i, i, width).
  • Mixing engines. To change an existing workbook use openpyxl (load_workbook, edit, save); XlsxWriter always starts a new file.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does pd.read_excel(book, sheet_name=None) return?

    sheet_name=None means 'all sheets', and pandas returns a dict of DataFrames keyed by sheet name.
  2. 2. A sheet has a title in row 1, a blank row 2 and the header in row 3. Which option reads it correctly?

    header counts rows from zero, so the third row is header=2.
  3. 3. Why do you wrap several to_excel calls in with pd.ExcelWriter(...)?

    ExcelWriter keeps one workbook open and saves it once, so each to_excel call adds a sheet instead of overwriting the file.
  4. 4. A formula cell shows NaN after read_excel. What is the most likely reason?

    pandas reads the cached value Excel saved. Files written by programs that did not calculate formulas have no cached value.
  5. 5. Which library does pandas use as the engine for .xlsx files?

    openpyxl reads and writes .xlsx. xlrd handles only the old .xls format.

Summary

  • to_excel(path, index=False) writes a sheet; read_excel(path) reads one. .xlsx needs the openpyxl engine.
  • sheet_name selects by position or name; a list or None returns a dictionary of DataFrames.
  • with pd.ExcelWriter(...) as writer: writes several sheets into one workbook; pd.ExcelFile lists and parses sheets.
  • header, skiprows, usecols (names or letters like "A:C") and nrows extract a table from a human-made sheet.
  • pandas reads cached formula results, not formulas; merged cells and numbers stored as text need cleaning.
  • Format through the engine (writer.sheets[...]): widths, number formats, frozen panes. The cell values stay numeric.
  • engine="xlsxwriter" builds new workbooks with formats, conditional formats, autofilters and native charts via writer.book and writer.sheets[...]; it cannot edit existing files.

Next: Writing and Formatting Data covers the other file formats you can write.