Learning very basic Python may be easy, but mastering it takes a long time and for many Excel users it’s not reasonable to expect them to invest the time to become a developer to the extent that they can fully replace Excel with Python. Even for competent developers, sometimes Excel is just faster and easier for some tasks. Developers often forget the years of training they’ve had, whether that be through self learning or university degrees, and assume that everyone should be able to just pick up a programming language (in addition to their already specialised skill set at whatever they do).

A better middle ground is to give these Excel users proper tools to stop their spreadsheets from becoming a liability in the first place. Higher level functions shouldn’t be encoded as entire sheets in Excel, or as VBA functions, but instead provided as worksheet functions or macros written in a modern language (like Python) and exposed to Excel.

Tools like PyXLL (https://www.pyxll.com) and Jinx (https://exceljava.com) are bridges between Excel and Python and Java (respectively). Using these, all the complexity (which is ultimately the problem with large spreadsheets) can be expressed in a language suitable to the problem. The end user still uses Excel as their interface, but in a much simpler way.

Trying to deal with large amounts of data being dumped into Excel sheets can raise issues. This can be entirely avoided, while still using Excel. In Python, data sets are passed around as pandas DataFrames. In Excel, using a PyXLL add-in, you can do exactly the same thing. DataFrames (or any Python object) can be returned directly to Excel from a worksheet function without expanding to an Excel range. Instead, a handle to the Python object can be returned which in turn can be passed to other Python functions (e.g. you might have a few functions for describing a DataFrame or getting the first n rows etc…). This link explains more about using Pandas in Excel https://www.pyxll.com/docs/userguide/pandas.html.

I recently wrote an article explaining my thoughts on this, but from a Java perspective — although exactly the same is true for Python https://medium.com/excel-java/replacing-vba-with-java-in-excel-e9f5e28d4e5c.

Written by

Professional software developer living and working in London. Creator of the Excel add-ins PyXLL and Jinx.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store