Excel 2024: Using Python in Excel

In 2023, Microsoft released a preview of Python in Excel to people using Microsoft 365 who are signed up for the Beta channel of Microsoft Insiders. It is free to join the Microsoft Insiders program. As of January 2024, the preview is still free and still only in beta. Microsoft has not decided if Python will continue in Excel past the preview.

This section of the book will talk about Python from an Exceller's perspective. I am definitely a Python rookie. But I found useful things that I could do with Python that I could not do in Excel.

Python is a data science language. Ten years ago, I bought my first Python book. Flipping through the chapters, I found a technique called k-Means clustering that seemed interesting. So then I went back to the beginning of the book and attempted to get Python installed. That put an end to my Python journey. Installing Python requires several steps in an Command Prompt run as an Administrator. I could not figure it out. Five years ago, I donated the dusty book to the thrift store.

In 2023, Python comes to Excel as a preview. All of the setup disappeared. You can start writing Python on Day 1 without ever going to a command prompt.

Funny but true: I re-bought the same Python book that I had purchased ten years ago. Not for a dollar at the thrift store, but at full price.

Also funny and also true. After I ran into a few limitations in the Excel version of Python, I decided I wanted to install Python on my computer. My friend sent me a video with the 45 steps detailed in all their glory. I still haven't gotten through it. I am happy enough with the Python in Excel.

Here are some basics:

Python can be written in any cell. You can put a bunch of lines of Python code in one cell. The code in cell 2 can re-use the variables set up in cell 1. The Python statements are processed in Row Major Order, which means A1 first, then B1, then C1, out to XFD1, followed by A2, B2, ... XFD1048576.

To type Python in a cell, press Ctrl+Alt+Shift+P or use Formulas, Insert Python, Python in Excel.

When you press Ctrl+Alt+Shift+P, the formula bar shows a green PY on the left side. You will also see a new drop-down menu to the left of the Formula Bar where you can decide to have this Python cell return values to the grid or simply return a Python object that can be used by later Python cells.

What you and I would call a Range in Excel is called a Data Frame in Python. Every example that you encounter on the web will use the variable df to refer to a Data Frame. You can use any variable name that you want. PetHelpful.com suggests great names for a pet snake include Buttercup, Raven, Yoshi or Zuke. But rather than Zuke = xl("A5:C151", headers=True) I am going to be boring and use df = xl("A5:C151", headers=True) just like every other Python tutorial. Just remember that "df" stands for Data Frame, which is a name for a range.

That is just enough to get you started. Follow these steps:

1. Grab any Excel data. It should have headers. And some numeric columns. Paste to cell A1 of new workbook.

2. Go to a cell to the right of your data.

3. Press Ctrl+Alt+Shift+P to turn your cell into a Python cell. You will see a green PY to the left of the Formula Bar.

4. You want to load your Excel data into a Data Frame with the name df. Type df = and then, using your mouse, drag to select your data. Excel will fill in the rest of the line of code: df = xl("A1:C16", headers=True) Congratulations - you have now loaded your Excel data into Python.

5. A python cell usually contains multiple lines of code. Press Ctrl+F2 to move your editing from in-cell to the formula bar.

6. Press Ctrl+Shift+U to make the Formula Bar taller.

7. To go to a new line in the Python code, press Enter.

8. Type the next line of code: df.describe()

9. Look to the left of the Formula bar. There are two possible icons. The "stack of squares laying on their side" stands for "Return a Python Object". The "123 with curved arrow" stands for return values to Excel. In this example, you want to return values to Excel. If you are currently seeing the Stack of Squares icon, press Ctrl+Alt+Shift+M to toggle to return values.

10. Press Ctrl+Enter to commit the code. In 10-15 seconds, you will see statistics about your data. Congratulations, you are now a Python coder!

Bonus Tip: Excel Pre-Loads Many Python Libraries

As you begin your Python journey and look for code online or from Chat-GPT, you will see that the first several lines of Python is often loading the various libraries needed for a particular analysis. Excel pre-loads several libraries for you.

Go to Formulas, Python, Initialize for a current list of which libraries are loaded. Any of the lines of code that you find on the Internet that are already in the following screenshot can be skipped.