Python for Excel

Preface

Microsoft is running a feedback forum for Excel on UserVoice where everybody can submit a new idea for others to vote on. The top voted feature request is “Python as an Excel scripting language,” and it has roughly twice as many votes as the second most voted feature request. Though nothing really happened since the idea was added in 2015, Excel users were fueled with new hope at the end of 2020 when Guido van Rossum, the creator of Python, tweeted that his “retirement was boring” and he would join Microsoft. If his move has any influence on the integration of Excel and Python, I don’t know. I do know, however, what makes this combination so compelling and how you can start using Excel and Python together—today. And this is, in a nutshell, what this book is about.

The main driving force behind the Python for Excel story is the fact that we are living in a world of data. Nowadays, huge datasets are available to everybody and about everything. Often, these datasets are so big that they don’t fit into a spreadsheet anymore. A few years ago, this may have been referred to as big data, but nowadays, a dataset of a few million rows is really nothing special. Excel has evolved to cope with that trend: it introduced Power Query to load and clean datasets that don’t fit into a spreadsheet and Power Pivot, an add-in to perform data analysis on these datasets and present the results. Power Query is based on the Power Query M formula language (M), while Power Pivot defines formulas ...