Want to share your content on python-bloggers? click here.
Between Power Query and Power Pivot alone, “modern” Excel boasts so many features that it’s impossible to learn them all. Add to that Office Scripts, Power BI, LAMBDA() and more and it’s easy to get overwhelmed.
One “Excel-adjacent” tool that can be particularly intimidating for prospective learners is Python. Shouldn’t this be the last tool on the learning list, many Excel users think, given that it’s not even a Microsoft product and it literally requires learning a new language to use?
While Python may not be the best choice for every Excel user, it’s worth serious consideration for those looking to build complex automations, version-controlled projects and other advanced development products. Here’s the role of Python in modern analytics and modern Excel.
A growing stack requires glue
When I started as an analyst, my choices started and ended with Excel. All data, reporting, dashboards, everything was under that one green roof.
Fast forward a few years and there’s Power BI, Office Scripts, Jupyter Notebooks and more. This broadening of the analyst’s tech stack is right in line with wider trends in tech: a move away from one single, monolithic application to a loosely connected suite of specialized products.
To make this architecture work, a “conductor” or “glue” is needed. Whether it’s reading a dataset in from one source and visualizing it another, or deploying a machine learning model from the cloud to an end-user’s dashboard, Python is a great choice for this glue. It’s one of the rare languages that’s used for simple amateur-written scripts to enterprise-level applications alike, and can work smoothly with a variety of operating systems and other programming languages.
Microsoft has acknowledged and celebrated Python’s role as a “glue” language, and it’s already available to use for a variety of purposes in Azure, Power BI, SQL Server and more. And while Python isn’t yet officially supported for Excel, it still has advantages. Plus, think of all the other places you’ll be able to use Python… and who else is using it!
Network effects mean faster development time
“Everybody’s doing it” isn’t usually a good reason to engage in something… but, in the case of programming languages, it may be a good enough reason.
Have you ever heard of network effects? It’s the idea that the value of something grows as more people use it. Programming languages have network effects — the more programmers there are, the more code is shared, the more code there is to use and then build on… it’s a virtuous cycle.
Because Python is such a neutral “glue” language, it’s been adopted in professions as wide ranging as database management, web development, data analytics and more. This means it’s a good chance that no matter where your Excel project takes you, or what tools you need, someone you collaborate with also “speaks” Python.
For example, maybe you develop an inventory tracker or other application using Excel. The program gets too unwieldy for a workbook, or it becomes so popular that your organization wants it to become a standalone web program. The turnaround time for this project will be much faster if the existing code is already in Python.
I find it unfortunate that data analysts and other tech professionals often work with a completely different set of tools — it can make collaboration slow and tedious. By adopting a shared language, the network effects expand and development time drops.
Bring modern development to Excel
“Software developer” has become a coveted job title over recent years, almost as sexy as “data scientist.” Sadly, the methods and best practices developed by this profession have not caught on with BI or VBA developers. At an organization level, doesn’t it seem like trouble to have two tech professionals both called “developers” going about their work in totally different methodologies?
Python offers the modern Excel developer to implement these best practices. These include the following:
Unit testing
Most programming languages offer automated unit tests as a way to confirm that code is performing as expected. Unfortunately, base Excel and VBA do not. There are a few workaround tools here, but Python is a solid candidate for unit testing with the network effects to boot.
Automated unit testing makes applications more reliable and less likely to break — particularly useful for Excel workbooks that users with a variety of technical backgrounds might pick up.
Version control
Another development tool de jure that Excel lacks is a version control system. This tracks changes in a repository, allowing users to, among other things, see who contributed what, when and revert back to older versions.
If you’ve ever pulled your hair out over determining the difference between budget-model-final.xlsx and budget-model-FINAL-final.xlsx, then you understand why version control might be helpful.
Excel does provide some basic version control-esque features like the ability to view version history in OneDrive and the Spreadsheet Compare add-in, but lacks many of the features that could be found by moving all code production to Python.
Package development and distribution
The previous two features, while useful for a serious developer, may not be all that interesting for a typical Excel user. Perhaps you are looking for a more immediate reason to learn Python as part of your day-to-day analysis tasks… is it still worth learning?
And to that, all I have to say is one word: packages.
Don’t get me wrong, I love building things on the computer. But if someone’s already built something that does exactly what I want, I’m not reinventing the wheel. Python’s features for building and sharing packages, particularly through the Python Package Index, open a whole world of tools in a way that would be very difficult to replicate through Excel add-ins or VBA modules.
Not only that, but the pip manager and other tools provides methods for installing and managing these packages.
Whether you’re looking to collect data from the Census API, describe an image, or just have a function to generate descriptive statistics, access to Python packages is well worth the price of learning Python code. Some of these packages are even designed to work well with Excel.
Python and the future of Excel
In an AI-driven world, it can seem like learning to code is exactly the wrong choice to stay relevant. Ironically, however, as more data gets generated in more formats (including some AI-powered), the need to code may be more relevant than ever.
As an integral part of the modern analytics stack, the possibilities to integrate Python with Excel are only growing as Excel itself takes on a variety of AI-driven features.
Want to share your content on python-bloggers? click here.