Python for Excel, 2nd Edition (for . .) (Felix Zumstein)（Z-Library）

Praise for Python for Excel Python and Excel are themselves daunting tools to learn. But Felix Zumstein brings them together with clarity— demonstrating how to go beyond basic automation to create powerful analytical tools in Excel. If you would like to take your Excel spreadsheets to the next level, you need to read this book. —Jordan Goldmeier, author, Microsoft MVP Excel and Python are two of the most important tools in the business analytics toolbox, and together they are far greater than the sum of their parts. In this book, Felix Zumstein lays out his unparalleled mastery of the many ways to connect Python and Excel using open source, cross-platform solutions. It will be an invaluable tool for business analysts and data scientists alike, and any Python user looking to harness the power of Excel in their code. —Daniel Guetta, professor of professional practice and director of the Business Analytics Initiative at Columbia Business School and coauthor of Python for MBAs Python for Excel is the clearest, most practical guide to the modern Excel-Python stack. It shows not just how the tools work, but why they meaningfully expand what Excel users can do. —George Mount, Microsoft Excel MVP, author of Modern Data Analytics in Excel

Felix explains the material incredibly well and simply, making it easy to learn. The level of thought and detail that he demonstrates is evident throughout the book. Concise examples will get you working quickly. He calls out the gotchas to avoid. A delightfully well-written book. —Cody Paustell, programmer/developer, Rubberduck-VBA contributor

Python for Excel SECOND EDITION A Modern Environment for Automation and Data Analysis Felix Zumstein

Python for Excel by Felix Zumstein Copyright © 2026 Zoomer Analytics LLC. All rights reserved. Published by O’Reilly Media, Inc., 141 Stony Circle, Suite 195, Santa Rosa, CA 95401. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional sales department: 800- 998-9938 or corporate@oreilly.com. Acquisitions Editor: Michelle Smith Development Editor: Melissa Potter Production Editor: Ashley Stussy Copyeditor: Piper Content Partners Proofreader: Rachel Rossi Indexer: nSight, Inc. Cover Designer: Susan Brown Cover Illustrator: Karen Montgomery Interior Designer: David Futato Interior Illustrator: Kate Dullea March 2021: First Edition

May 2026: Second Edition Revision History for the Second Edition 2026-05-22: First Release See http://oreilly.com/catalog/errata.csp? isbn=9798341640290 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Python for Excel, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the author, and do not represent the publisher’s views. While the publisher and the author have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the author disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights. 979-8-341-64029-0 [LSI]

Preface “Yeah, so I helped the Excel team with this. Excited that it’s out!” This was the message that Guido van Rossum— the creator of Python and, at the time, a Microsoft employee— posted on X on August 22, 2023. His post included a link to the official announcement of Python in Excel. So how did we get here? In the early days of Excel, Visual Basic for Applications (VBA) was the only choice to program and extend Microsoft Excel as an end user. VBA is a one-stop shop, as it allows you to: Write macros that run at the click of a button, e.g., to add a new worksheet with a report Write custom functions (also called user-defined functions) that you can call from worksheet cells Build your own add-ins to provide automation features and custom functions across workbooks With the growing importance of the internet and mobile platforms, Microsoft had to make Excel work across these new environments. Today, Excel runs on Windows, macOS, Android, iOS, and— most importantly— on the web. However, only Windows and macOS support VBA. Rather than extending VBA support to the other platforms, Microsoft created new tools: Power Query can import and transform data and uses the M language.

Power Pivot is an add-in for data modeling that uses Data Analysis Expressions (DAX) as its formula language. Office Scripts uses TypeScript and is the solution that comes closest to VBA. However, it doesn’t support custom functions and requires a Microsoft 365 subscription. Lambda functions use Excel’s own formula language to create custom functions. So what used to be a one-stop shop is now a fragmented ecosystem where each tool uses a different programming language. Python in Excel adds yet another option to this list: it gives analysts access to a more powerful analytics and visualization engine, but it doesn’t support Excel automation or custom functions. Python, however, is a general-purpose programming language, and using it only for analytics and visualization is leaving a lot of its potential untapped. That’s why this book doesn’t stop at Python in Excel. Instead, I will show you how to use Python as a modern VBA replacement by leveraging its entire ecosystem.

How This Book Is Organized This book is organized into six parts. The first two parts teach you the fundamentals of Python and pandas, so you’ll need a bit of patience until we start using Python and Excel together in Part III. This is intentional: if you want the fast track, there are plenty of resources that jump straight into Python in Excel or pandas. However, as soon as you need to adapt or debug the code from these tutorials, it’s easy to get stuck. By building a solid foundation in Parts I and II, you’ll understand the concepts and tools you’ll rely on in later chapters. This approach takes a bit longer upfront, but it will save you time and frustration in the long run. Part I, “Introduction to Python” Chapter 1 looks into the reasons why Python is such a good companion for Excel. Chapter 2 introduces the tools we’ll be using in this book: the uv package manager, the Terminal, Visual Studio Code, and Jupyter notebooks. Chapter 3 will teach you enough Python to be able to follow the rest of this book. Part II, “Introduction to pandas” Chapter 4 gives you a very short introduction to NumPy, which is the backbone of many scientific packages, including pandas. pandas, the topic of Chapter 5, is one of the most popular Python libraries for data analysis. Its core data structure, the DataFrame, is similar to a spreadsheet and can serve as a programmatic alternative to an Excel workbook. Part III, “Python in Excel” Chapter 6 covers the basics of Python in Excel, the official Microsoft solution. Python in Excel is centered around the

concept of a two-dimensional Jupyter notebook that unlocks better analytics and visualization by giving you access to the broader pandas ecosystem. Chapter 7 teaches you how to use pandas with time series. For this chapter, you can choose to follow the code samples with either Python in Excel or with a classic Jupyter notebook. Part IV, “xlwings” In Chapter 8, I’ll introduce the xlwings package to automate the Excel application from Python. For example, you will learn how to open and edit Excel workbooks programmatically. Chapter 9 is about building interactive Excel tools, which allow you to click a button to run Python code. Chapter 10 brings everything you’ve learned so far together to build a typical database-backed business application. In Chapter 11, you’ll write custom functions that you can call from Excel cells just like built-in functions such as SUM. Part V, “xlwings Lite” Chapter 12 introduces xlwings Lite that gives you access to the xlwings package without the need of a local Python installation. Instead, you install xlwings Lite from Excel’s add-in store. Chapter 13 dives into writing custom functions with xlwings Lite. In this part, you already know the basics of Python, pandas, and xlwings. This allows us to explore a few interesting projects: we’ll analyze 3.5 million taxi rides, build an alternative to Microsoft’s COPILOT function, and run a pretrained machine learning model for sentiment analysis. Part VI, “Reading and Writing Excel Files Without Excel” This part is different from the rest of the book, as it doesn’t require an installation of Excel. You’ll learn how to read and

write Excel workbooks directly on disk. Chapter 14 introduces you to the reading and writing features that pandas offers. Under the hood, pandas depends on other packages, so called engines, that do the actual work. How you can use these engines directly is the topic of Chapter 15, where we’ll look at packages like OpenPyXL and XlsxWriter, among others. 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: Chapter 6 introduces Python in Excel. Chapter 12 introduces xlwings Lite and its automation capabilities. Chapter 13 shows how custom functions work with xlwings Lite. Appendix B introduces Copilot in Excel. Other than that, I changed the order of chapters slightly: Chapter 7, “Time Series Analysis with pandas” 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.

Part VI, “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.

Who This Book Is For If you are an advanced Excel user who wants to push beyond the limits of Excel with a modern programming language, this book is for you. Most typically, this means that you spend hours every month downloading, cleaning, and copy/pasting big amounts of data into mission-critical spreadsheets. While there are different ways to overcome Excel’s limits, this book will focus on how to use Python for this task. You should have a basic understanding of programming: it helps if you have already written a function or a for loop (no matter in which programming language) and have an idea about what an integer or a string is. However, you might still be able to follow this book even if you are just used to writing complex cell formulas or have experience with tweaking recorded VBA macros or Office Scripts. You are not expected to have any Python-specific experience, though, as I introduce all the tools that we will use, including an introduction to Python itself. If you are a seasoned VBA developer, you will find regular comparisons between Python and VBA that will help you avoid common gotchas. This book can also be helpful if you are a Python developer and need to learn about the different ways that Python can deal with the Excel application and Excel files to be able to pick the right package given the requirements of your business users.

Why I Wrote This Book Through my work on xlwings, the Excel automation package that we will meet in Part IV and Part V of this book, I am in close contact with many users who use Python for Excel— whether that’s via the issue tracker on GitHub, a question on Stack Overflow, or at a physical event like a meetup or a conference. On a regular basis, I am asked to recommend resources to get started with Python. While there is certainly no shortage of Python introductions, they are often either too general (nothing about data analysis) or too specific (full scientific introductions). However, Excel users tend to be somewhere in the middle: they certainly work with data, but a full scientific introduction may be too technical. They also often have specific requirements and questions that aren’t answered in any of the existing material. Some of these questions are: Which Python-Excel package do I need for which task? How do I move my Power Query database connection over to Python? What’s the equivalent of an Excel PivotTable in Python? I wrote this book to get you from zero Python knowledge to automating your Excel-centric tasks and leveraging Python’s scientific ecosystem in Excel without any detours.

Python and Excel Versions This book has been tested to work with Python 3.14 and expects you to use a modern version of Excel. Ideally, you would use the version that comes with Microsoft 365, or if you use Excel with a permanent license, you should use Excel 2021 or later. If a chapter requires a specific version of Excel, I will point this out at the beginning of the chapter. Conventions Used in This Book The following typographical conventions are used in this book: Italic Indicates new terms, URLs, email addresses, filenames, and file extensions. Constant width Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords. Constant width bold Shows commands or other text that should be typed literally by the user. Constant width italic Shows text that should be replaced with user-supplied values or by values determined by context.

TIP This element signifies a tip or suggestion. NOTE This element signifies a general note. WARNING This element indicates a warning or caution. Using Code Examples I am maintaining a web page with additional information to help you with this book. Make sure to check it out, especially if you run into an issue. Supplemental material (code examples, Excel workbooks, etc.) is available for download on https://oreil.ly/github- pfe2e. I will provide detailed instructions on how to download this companion repository in Chapter 2. If you have a technical question or a problem using the code examples, please send an email to bookquestions@oreilly.com. This book is here to help you get your job done. In general, if example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing

examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission. We appreciate, but generally do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Python for Excel, Second Edition by Felix Zumstein (O’Reilly). Copyright 2026 Zoomer Analytics LLC, 979-8-341-64029-0.” If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com. O’Reilly Online Learning NOTE For more than 40 years, O’Reilly Media has provided technology and business training, knowledge, and insight to help companies succeed. Our unique network of experts and innovators share their knowledge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in- depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit http://oreilly.com.

How to Contact Us Please address comments and questions concerning this book to the publisher: O’Reilly Media, Inc. 141 Stony Circle, Suite 195 Santa Rosa, CA 95401 800-889-8969 (in the United States or Canada) 707-827-7019 (international or local) 707-829-0104 (fax) support@oreilly.com https://oreilly.com/about/contact.html We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at https://oreil.ly/py4excel-2e. For more information about our books, courses, conferences, and news, see our website at http://www.oreilly.com. Follow us on LinkedIn: https://linkedin.com/company/oreilly. Watch us on YouTube: http://youtube.com/oreillymedia.

Acknowledgments I am incredibly grateful to the many people who helped me write this book. At O’Reilly, I would like to thank my editor, Melissa Potter, who helped bring this book into a readable form. I’d also like to thank Michelle Smith, Ashley Stussy, Jonathon Owen, and Daniel Elfanbaum. I was very lucky that the book was reviewed by highly qualified tech reviewers, and I really appreciate the hard work they put in under a lot of time pressure. Thanks for all your help, Jordan Goldmeier, George Mount, Cody Paustell, Andreas Clenow, Werner Brönnimann, Björn Stiel, and Eric Moreira! A big thank you goes to all my colleagues, friends, and early release readers who read the earliest forms of my drafts: Adam Rodriguez, Mano Beeslar, Simon Schiegg, Rui Da Costa, Jürg Nager, Christophe de Montrichard, Felipe Maion, Ray Doue, Kolyu Minevski, Scott Drummond, Volker Roth, and David Ruggles.

Part I. Introduction to Python