Python for Excel–The Book

A Modern Environment for Automation and Data Analysis

Responsive image

Where to Buy

You can buy the book at your favorite book store. If you don’t have one, here are a few suggestions:

What’s New in the Second Edition

It’s been five years since the first edition. In that time, Microsoft came out with Python in Excel, and xlwings Lite hit Excel’s add-in store. Besides that, artificial intelligence (AI) has become mainstream with the release of ChatGPT, Microsoft Copilot, and numerous other AI tools. Accordingly, this edition includes three new chapters and one new appendix:

  • Python in Excel introduces Python in Excel.
  • xlwings Lite Scripts introduces xlwings Lite and its automation capabilities.
  • xlwings Lite Custom Functions shows how custom functions work with xlwings Lite.
  • Copilot in Excel (appendix) introduces Copilot in Excel.

Other than that, I changed the order of chapters slightly:

  • The Time Series chapter was previously in the pandas part but has been moved to the Python in Excel part so that you can directly compare a classic Jupyter notebook with the Python in Excel implementation.
  • The part on reading and writing Excel files without Excel has been moved to the very end. This has the advantage that the parts related to the focus of the book, interacting with Python from the Excel application, are kept together while the fundamentally different concept of directly manipulating files comes last.

This edition also adds a few new projects that make use of modern technologies, including DuckDB, Parquet files, pretrained machine learning models from Hugging Face, and OpenAI API. Besides that, the Python version has been updated from 3.8 to 3.14, and pandas has been updated from version 1.1.3 to 3.0.0. And finally, while the first edition used the Anaconda Python distribution, this edition uses the uv package manager instead to install Python and its packages.

Table of Contents

Part I. Introduction to Python
1 Why Python for Excel?
2 Development Environment
3 Getting Started with Python

Part II. Introduction to pandas
4 NumPy Foundations
5 Data Analysis with pandas

Part III. Python in Excel
6 Getting Started with Python in Excel
7 Time Series Analysis with pandas

Part IV. xlwings
8 Excel Automation
9 Python-Powered Excel Tools
10 The Python Package Tracker
11 Custom Functions

Part V. xlwings Lite
12 Scripts with xlwings Lite
13 Custom Functions with xlwings Lite

Part VI. Reading and Writing Excel Files Without Excel
14 Excel File Manipulation with pandas
15 Excel File Manipulation with Reader and Writer Packages

Appendices
A The uv Package Manager
B Copilot in Excel
C Advanced Python Concepts

Companion Repository

https://github.com/fzumstein/python-for-excel

Where to Buy

You can buy the book at your favorite book store. If you don’t have one, here are just a few suggestions:

Video Summary

Reviews

Felix Zumstein’s book is an engaging read—and concisely articulates the difference between doing *data analysis* with Python vs. traditional software engineering.\ —Paige Bailey a.k.a. @DynamicWebPaige via Twitter

This book is pitched at a nice level for someone competent in Excel, but new to Python. Unlike most intro to Python texts it allows an advanced Excel user to supplement their current skillset, by leveraging Python. Overall would recommend to a colleague.\ —jpoynter via O’Reilly Learning Platform

It is the best technical book I have read in months. That means something because I’m actively working my way through approximately 2 top-notch books a month.\ —solveigroth via O’Reilly Learning Platform

It covers all popular Python libraries related to Excel. It also provides good description on pandas and xlwings which are not found in the standard documentation. I would recommend the book for people who want to use Python with Excel.\ —hormengyoong via O’Reilly Learning Platform

I have followed your book to develop a Python tool using Excel. It was incredibly helpful.\ —kanavasait via GitHub

Done reading your book, and I learned so much!\ —Mark Sanel Calonia via YouTube

Table of Contents

The book has 316 pages and is split into 4 parts and 12 chapters:

I. Introduction to Python (71 pages)
1 Why Python for Excel?
2 Development Environment
3 Getting Started with Python

II. Introduction to pandas (66 pages)
4 NumPy Foundations
5 Data Analysis with pandas
6 Time Series Analysis with pandas

III. Reading and Writing Excel Files without Excel (40 pages)
7 Excel File Manipulation with pandas
8 Excel File Manipulation with Reader and Writer Packages

IV. Programming the Excel Application with xlwings (96 pages)
9 Excel Automation
10 Python-Powered Excel Tools
11 The Python Package Tracker
12 User-Defined Functions (UDFs)

For a more detailed table of contents, please refer to the O’Reilly Learning Platform.

Companion Repository

You’ll find the Jupyter notebooks, Python scripts, and other example files here: https://github.com/fzumstein/python-for-excel/tree/1st-edition.

Updates/Errata

The official errata page is: https://www.oreilly.com/catalog/errata.csp?isbn=0636920386926. Feel free to submit your corrections there. However, you can also contact me directly, and I will take care of it. I will also keep a list of changes here as it is much easier to format code samples etc.

  • ch02 (p. 37): Windows-specific instructions: VS Code changed the Command Palette entry from Terminal: Select Default Shell to Terminal: Select Default Profile, accordingly, when you search for default shell, you won’t find the entry. Instead, type default profile.

  • ch09 (p. 201): Code sample 63 uses a . for the decimal separator. Depending on your regional settings, this may have to be replaced with a , instead. I fail to mention this explicitly in the Language and Regional Settings box on p. 187.

Python/Anaconda Version

The book was originally written for Anaconda 2020.11 and Python 3.8. Meanwhile, Anaconda 2021.11 is out with Python 3.9 and the good news is that all code samples are still working without any changes. While Anaconda doesn’t come with a version that supports Python 3.10 yet, you can still create a Conda environment based on Python 3.10 by following these steps: install the latest version of Anaconda, then, on an Anaconda Prompt, run the following commands (the first line expects you to have the companion repository downloaded as explained in the book’s preface):

(base)> cd C:\Users\username\python-for-excel\conda
(base)> conda env create -f xl310.yml
(base)> conda activate xl310
(xl310)>

If you would like to use the xl310 environment rather than the base environment to work through the examples in this book, make sure to always have your xl310 environment activated by running:

(base)> conda activate xl310
(xl310)>

That is, wherever I show the Anaconda Prompt as (base)>, you will want it to show (xl310)> instead.

Translations

The following translations are available for order:

translations

FAQ

The snake on the cover is not a Python, can you please fix this?
You are right, it’s a false coral snake. As the cover animal is selected by the publisher, I can’t change it though.

Does the book use any commercial software like xlwings PRO?
No, the book uses exclusively software that is open source and free (except for Excel, of course). Also, the Anaconda distribution isn’t open source, but the Individual Edition, which is used in the book, is free, see the EULA.

Which Python-Excel packages are presented in the book?
The book introduces pandas, OpenPyXL, XlsxWriter, pyxlsb, xlrd, xlwt, xlutils and xlwings.