How to get the most of Python in Excel with the Python Editor from Excel Labs

The Python in Excel environment is notably user-friendly, especially for those accustomed to Excel. It resonates with users familiar with writing formulas and functions in Excel cells, as this is precisely the approach Python in Excel adopts.

However, Python workflows differ from those in Excel. Rather than relying on interdependent cells, Python typically involves more extensive blocks of code. Standard Python development environments often feature syntax highlighting, autocomplete capabilities, and other advanced tools, which the basic Python in Excel cell editor lacks.

Despite appearing somewhat rudimentary, there’s good news! Microsoft has thoughtfully developed a complimentary solution to enrich Python development in Excel with these sought-after features. Explore these enhancements with the accompanying exercise file:

Getting the add-in

The Python Editor from Excel Labs is available as an add-in. To activate it in your Excel application, simply navigate to the ‘File’ tab on the home ribbon. From there, select Get Add-ins and search for Excel Labs:

Once you click Add, you’ll notice the Excel Labs icon conveniently placed towards the right side of your Home tab. Additionally, an Excel Labs popup will appear to the right of your workbook, marking the successful integration of the add-in.

Interestingly, the Excel Labs add-in isn’t just a single feature; it’s a comprehensive suite of three distinct add-ins, each with its unique capabilities. While all of them are intriguing and merit exploration, our primary focus here is the Python Editor. Simply click ‘Open’ to dive into the Python Editor and begin your journey into enhanced Python scripting within Excel.

The Python Editor uses some of the same components that power Visual Studio Code and supports many of the features that Visual Studio Code offers for Python development, such as IntelliSense, formatting, code completion, and syntax highlighting.

Viewing the Python code and preview

Navigate to the sales worksheet in the exercise file. The Excel Labs will display all Python cells found within this worksheet. In this instance, there is only one. Additionally, it provides a preview of the Python output for each code block:

This feature is quite useful for a single code block, but it becomes extremely helpful for a worksheet with multiple cells, like the data_profiling worksheet in the same workbook. Here, you can scroll down to quickly gain an overview of all the Python activity within this block:

Feel free to delve into this and the other worksheets to understand how the Python editor functions in more intricate scenarios. This exploration will provide insights into some of the rapid advantages achievable with Python in Excel.

For simplicity, we’ll return to the sales worksheet to explore additional features of this tool.

Tips for using the Python Editor

Jumping to cells of interest

Excel worksheets can often be cluttered and challenging to navigate, making it difficult to immediately locate the Python blocks within. To simplify this with Excel Labs, simply click on the cell location above the Python script you’re interested in (for example, cell M2). This action will direct you to that specific area in the worksheet:

Editing Python code

Now, let’s focus on the main feature: using the Python Editor as a fully-fledged code editor. For instance, you can expand your workspace significantly by toggling the rightmost icon. Although we are only adding one line currently — to obtain the descriptive statistics of the sales DataFrame using the describe() method — I encourage you to experiment on your own. You’ll find that it includes features like autocomplete, syntax highlighting, line numbering, and many other conveniences not available in the basic formula bar.

Moreover, there’s a Save icon that allows you to explicitly save your work when finished, or you can choose to keep it in a draft state. This flexibility greatly enhances the ease of building, testing, and editing your Python work in Excel:

Changing between Python object and Excel values

While not a feature new to the Python Editor, another useful capability is the ability to toggle between viewing the Python object in the Excel worksheet and seeing the actual Excel values. As with any other modifications made through this menu, it’s important to formally save your work to ensure that the changes are finalized:

The next generation of Python in Excel is here

While utilizing basic single block Python analyses in Excel is already impressive, embracing this more advanced approach marks the next wave of innovation. Here, you’re merging the user-friendliness and accessibility of Excel with the robust syntax and development environment capabilities of Python, particularly those of the Jupyter notebook. This combination fosters an auditable, reproducible workflow and narrative with your code.

What questions do you have about using the Python Editor from Excel Labs? Let me know in the comments.

The post How to get the most of Python in Excel with the Python Editor from Excel Labs first appeared on Stringfest Analytics.