- High level overview of the main differences
- Table of main feature differences
- What are people saying about Python in Excel?
- Performance
Microsoft recently announced that Python would become available in Excel. There are fundamental differences between PyXLL and Microsoft's own version of Python in Excel.
This is a new product for Microsoft, but our initial impression is that it is disappointing. It is our belief that the design decisions taken will, if used, result in less maintainable, error prone, and slower spreadsheets.
Microsoft have added a new "PY" function that will execute arbitrary Python code, remotely in an Azure container running their own Anaconda distribution. We all know the problems associated with VBA code being coupled with workbooks, and now with this PY function the situation is worse as a spreadsheet can contain any Python code in any cell, so code will end up copied and pasted everywhere in a way that will be impossible to maintain. You can't structure your code into packages, all of your code is written in the cells in the workbook.
This is virtually the complete opposite approach taken by PyXLL, which is to get code out of Excel to a place where it can be shared and re-used across workbooks. This gives Excel users a clean, manageable toolkit of Excel functions, written in Python. PyXLL functions run in your own Python environment and so you can import and use your own packages.
Even worse, these PY functions take no arguments. Inputs are hard coded into the Python code in the cell. This means that each time some functionality is to be re-used, not only does it have to be copy and pasted, the code itself has to be edited to change the inputs, adding yet more opportunity for bugs to be introduced.
As these PY functions take no arguments they are calculated outside of Excel's normal dependency graph. Instead of being run in the correct order as values they depend on change, they are run in strict left to right and top to bottom order, meaning you have to be very careful where you put them on your sheet to avoid errors due to out of order calculations.
PyXLL functions work in exactly the same way as normal Excel functions. They take arguments and work within Excel's dependency graph, meaning they are always calculated in the correct order.
High level overview of the main differences
Here are some high level differences specifically related to writing worksheet functions (UDFs):
- PyXLL runs your Python code in your Python environment, and you can import any packages and modules you like, including ones you've written yourself.
Microsoft's Python in Excel runs Python on a remote Azure container using a fixed set of packages that they control. You cannot import your own packages, all of the Python code needs to be written in your workbook. - Using PyXLL, your code and data is never shared with anyone. It is entirely in your control and resides on your local PC or network drives. We cannot ever use your code or data for any purposes, like training AI models for example. And, since we don't have it, we can't lose it.
Microsoft's Python in Excel runs in the Azure cloud, maintained by a third party Anaconda. If you use Microsoft's Python in Excel all of your code and data is uploaded to their cloud environment. You have to trust Microsoft and their partners to keep it safe. You should also carefully read their terms (and updates to those terms as they change) and trust they will not use your code and data in ways you do not agree with. - PyXLL functions look like normal Excel functions, because they are! Arguments are passed as arguments to your functions, and each function has its own name and help text. You can build your own toolkit of functions for yourself and for non-technical Excel users to use.
Microsoft's Python in Excel has only one function, "PY". All of your Python code is embedded in cells in the workbook using the same PY function, making your spreadsheet incredibly hard to understand. The PY function doesn't take any arguments, you have to reference hard-coded cells from your Python code, making traceability, maintainability, and code re-use challenging (to say the least). - Functions written with PyXLL use Excel's own dependency graph, so that functions run in the correct order when updates are made. For example, if a function depends on cell A1 then it will update when cell A1 is updated in exactly the same way you would expect as other Excel functions do.
Using Microsoft's Python in Excel, Python cells can be calculated in the wrong order, resulting in errors in your spreadsheet that are inconsistent and hard to find. Microsoft's Python cells are calculated in strict left to right and top to bottom order, regardless of the cell data they depend on. This is a compete change of behavior from what Excel and other spreadsheets have done for decades. - With PyXLL, your Python code is kept separate from your workbook. Found a bug in a function? No problem, fix it once and everywhere that function is used it gets fixed.
Using Microsoft's PY function, the Python code is embedded in the cell in your workbook. Want to re-use it? It's not easy as you have to copy and paste the code, and then change the input cells in the code. Made a mistake? To fix it, find every cell where that code has been copied and pasted and fix it. If you've already shared the workbook with others that bug might well live forever. - PyXLL functions can be easily debugged using your existing Python development tools such as VS Code. Step through your code to understand, find and fix issues.
With Microsoft's Python in Excel, debugging is not possible. - PyXLL runs locally for the best possible performance and allows your code access to your network and other resources you allow.
Microsoft's Python in Excel runs remotely on the Azure cloud. This results in poor performance due to your code and data having to be transferred to their servers before it can be run and additional latency waiting for the results to be transferred back. - Functions run using PyXLL can access any resources you've permissioned, including databases, file servers, or any other internal or external internet services. The code runs with your user permissions on your local PC and so there are no access problems.
Code called via Microsoft's Python in Excel solution is uploaded and run on the Azure cloud in a container with no network access. Code run here can't access any resources that are not available in your Excel workbook. - PyXLL's worksheet functions can be run concurrently across multiple threads or on an asyncio event loop. As PyXLL functions take arguments, Excel uses it's calculation dependency graph to determine the correct order in which to calculate the cells.
Microsoft's Python in Excel PY function runs in strict left to right, top to bottom order, meaning each cell has to complete before the next one can start. Coupled with the fact the code is running on a remote server, the end result is poor recalculation performance.
Table of main feature differences
PyXLL has many features beyond writing UDFs that allow you to create rich Excel interfaces to your Python code. Below is a table of just some of the feature differences:
| Microsoft's Python in Excel | PyXLL | ||
|---|---|---|---|
| Call your own Python functions as UDFs | ✘ | ✔ | Register as many different Python functions to be called from Excel as you need, using your Python environment and packages. |
| Pass arguments to functions | ✘ | ✔ | Call Excel functions normally, with arguments. No need to hard-code inputs. |
| Write Excel macros | ✘ | ✔ | Call the entire Excel Object Model from Python. |
| Jupyter Notebooks | ✘ | ✔ | Edit and run code in Jupyter Notebooks, in Excel. |
| Works offline | ✘ | ✔ | Doesn't require an internet connection to work. |
| Version Control | ✘ | ✔ | Use git or any other version control system, it's just Python code. |
| Debugging | ✘ | ✔ | Use VS Code or another Python IDE to debug your code. |
| Read Time Data (RTD) functions | ✘ | ✔ | Stream data from Python to Excel in real time. |
| Ribbon Customization | ✘ | ✔ | Extend the Excel user interface with your own toolbars. |
| Menu Functions | ✘ | ✔ | Quick and easy menu functions to call Python code from Excel. |
| Object Cache | ✔ | ✔ | Pass Python objects between Excel worksheet functions. |
| Cell Formatting | ✘ | ✔ | Automatically format cells when returning values from worksheet functions. |
| Custom Task Panes | ✘ | ✔ | Advanced UI integration with PySide2, PyQt5, wxWindows and Tkinter. |
| Multi-threading | ✘ | ✔ | Use Excel's multithreading calculation capabilities. |
| Asynchronous Functions | ✘ | ✔ | Take advantage of Excel's native support for async functions. |
| Keyboard Shortcuts | ✘ | ✔ | Assign keyboard shortcuts to commonly used Python macros. |
| Easy to access commercial support | ✘ | ✔ | Contact us anytime you need help. |
What are people saying about Python in Excel?
I was wondering where the python code was being executed.. what happens when one’s internet connection is down?!! Yikes.
And zero ability to make your own code base and library / package management. I doubt any of PyXLLs userbase will be able to do what they need to do using what is shown.
I think what PyXLL does is still way way better.
https://twitter.com/stat_py/status/1694153213744660562
https://twitter.com/khoitsma/status/1696943544315912634
https://techcrunch.com/2023/08/22/microsoft-is-bringing-python-to-excel/
So slow Microsoft had to add an option to exclude their "=PY" calls from automatic calculations!
https://www.techrepublic.com/article/microsoft-introduces-python-excel/
Performance
The PyXLL add-in was always designed to be incredibly fast. Microsoft's Python in Excel is not so performance focused (to put it mildly). If you care about performance, it's always best to try for yourself.
We care about the performance of PyXLL, so if anything is not working as fast as you would expect then contact us and let us know.
This video shows a few simple tests that demonstrate just how slow (and in the case of the first test, unusably slow) Microsoft's Python in Excel solution is.
If you have any questions about the features or capabilities of PyXLL, please contact us and we will be happy to assist.