Blog
Excel has more in it than most people ever go looking for, and Python in Excel is a good example. If you associate code in Excel with VBA, this is a very different thing: Python goes straight into your worksheets with =PY or Formulas > Insert Python, runs in the Microsoft cloud, and returns its result to the grid. There’s no local environment to set up, and NumPy, pandas, Matplotlib, seaborn, and statsmodels are already imported.
I work on the Python in Excel team, and I wanted to see it in the hands of someone using it under real pressure, so I asked Jasper van Merle.
Many people who compete in the Microsoft Excel World Championship come from finance, and the competition has long rewarded deep formula knowledge. Jasper took a different route: He arrived from a pure software background, competing in VBA until Python in Excel reached the Beta Channel, and then built his competition tooling around it. It works: He’s ranked as high as sixth in the world, in a field of formula specialists.
Here are six techniques he uses a lot. None are competition-specific: They cut down setup time and make awkward problems tractable, clock or no clock.
1. Keyboard shortcuts
The Python editor follows the same keyboard conventions as most modern code editors, so muscle memory from elsewhere transfers easily. All you have to do is open it from Formulas > Editor, and it will appear in a side pane.
In general, I’m quite happy with [the Python editor]. If it hadn’t existed, I would have built my own add-in to do essentially the same thing.
Three worth knowing:
- Ctrl + / to toggle comments on the selected lines
- Shift + Alt + right arrow to grow the selection by one syntax scope at a time, and Shift + Alt + left arrow to shrink it
- Ctrl + Shift + L to select every occurrence of whatever is selected, making renaming a variable a single action
Ctrl + / is the one Jasper reaches for most. Commenting out a block and re-running the cell narrows down what’s misbehaving, and with no breakpoints available it does much of the work a debugger otherwise would.
Just a shortcut in the editor, but it’s immensely useful to me. As a programmer, we all know what this does. But your average accountant does not.
2. Initialization settings
Python in Excel lets you edit the code that runs before every other Python cell in the workbook. Put your imports and helper functions there and every cell can use them. This information travels with the workbook.
Before this was editable, the workaround was a manual one. Jasper uses it to widen his set of default imports and adds a block like the below example, so what he reaches for under time pressure is always in scope:
import re
from collections import Counter, defaultdict, deque
from dataclasses import dataclass
from datetime import date, datetime, timedelta
from functools import cache
from math import *
from math import * brings in a long list of mathematical utilities such as sqrt() and pi.
The first thing I do when I open a new sheet is run a macro that inserts initialization code into A1.
To configure your Python initialization code:
- Open the settings, then click Formulas > Initialization.
- Add your imports and any helper functions you reuse, and then apply the changes.
- To confirm it worked, call one of your helpers from any Python cell in the workbook without importing anything first.
NOTE: The pane already contains statements Python in Excel needs to calculate correctly, including import excel and the two excel.set_xl_ conversion lines. Add your code alongside them rather than replacing what’s there.
TIP: Add type hints to your helpers. Autocomplete gets noticeably better, and they double as documentation when you return to a snippet months later.
3. Open-source libraries
It’s easy for Python in Excel work to start and end with pandas, and for good reason: It’s incredible for tabular data. However, plenty of problems aren’t tabular, and beyond the five libraries imported by default, many others are available.
NetworkX is a good example. Anything shaped like a graph (routing, dependencies, connections, shortest paths) becomes just a few lines of code.
Consider you have a table of European cities with From, To and Kilometers columns:
One can calculate the shortest route between two cities. The drawing methods render the same table as a network diagram:
Every time there’s a maze to solve, I just dump it into NetworkX and ask for the shortest path.
4. Stack traces
When Python code fails in Excel, you get the exception message and nothing else. Where you’d expect a stack trace, you get a single line: ZeroDivisionError: division by zero. That tells you what went wrong, not where. The workaround is to catch the exception yourself and re-raise it with the stack trace as its message.
You get the error message, and that’s it. Good luck. So I wrote a wrapper around that that just puts the stack trace into the message, and then you do get to see it.
Here’s how you can view the full stack trace:
- Make it available to every cell by modifying your initialization code. This is the wrapper Jasper uses:
import re from traceback import format_exc
from typing import Any, Callable
def run(func: Callable[[], Any]) -> Any:
try:
return func()
except Exception:
# Excel only shows the error message, not the stacktrace, so we make
# the stacktrace the exception’s message to have more to work with
trace = format_exc().strip().split(“:”, 1)[1]
trace = re.sub(r”File “[^”]+”, line d+, in (.*)”, r”In 1″, trace)
raise Exception(trace)
- To use it, put your work in a function and call that function through run:
return a / b
def solve_problem():
a = 100
b = 0
return divide(a, b)
run(solve_problem)
Without the wrapper, that cell reports only the bare exception:
With it, you get the path the failure actually took:
TIP: Writing your solutions as functions also makes it easy to return early and inspect intermediate values, a reasonable stand-in for breakpoints.
5. Execution order
In a spreadsheet, formulas recalculate based on dependencies, so where you put one rarely matters. Python in Excel doesn’t work that way. Python cells run in row-major order, across each row from column A rightward, then down to the next row, and across worksheets in tab order. Which cell needs which result makes no difference.
If you don’t know it exists and you think the formula way, then your computation will not work.
Take two Python cells. One defines a value and returns it:
rate
The other uses it:
Put the cell that uses the value above the one that defines it, and it runs first and fails:
Swap them. Nothing about the code changes, but now it works:
That trips people up most when they arrive with formula instincts. It’s also why initialization settings help: They run before everything else, no matter where your cells sit.
6. Calculation modes
Automatic calculation is the right default, and most of the time you’ll leave it there. Occasionally, you won’t. In a sheet full of expensive Python computations, iterating on one cell means everything around it recalculates, too.
This comes up often in competition, where the computations are heavy and the clock is running. Excel’s calculation modes let you decide when Python cells re-run.
I recently discovered the value of this feature, and greatly appreciate the ability to take control over which cells run and which cells do not.
To take control of when Python cells recalculate:
- To stop Python cells recalculating until you ask, click Formulas > Calculation Options, and select Partial. Ordinary worksheet formulas carry on as usual. To make those wait too, select Manual.
- To recalculate when you’re ready, press F9, or click Formulas > Calculate Now.
- To recalculate one cell on its own, select the error triangle beside it, and then click Calculate Now.
- To resume recalculating as you work, return to Calculation Options and select Automatic.
The same menu has Format Stale Values, which switches itself on when you move to Partial. It’s why a waiting cell is easy to spot: Its value shows with strikethrough, so it stays clear what’s current and what’s just the last answer.
Here the destination changed to Berlin, but the result still shows the earlier route to Luxembourg:
After recalculating, it catches up:
None of this is really about competition; the clock just makes it obvious sooner. The change is that problems you’d once have looked at, decided weren’t worth the formulas, and quietly solved somewhere else are now a few lines of code in the cell where the data already lives.
If you haven’t tried it yet, Get started with Python in Excel walks through writing your first Python cell.
About Jasper van Merle
Jasper van Merle builds bespoke software at IMC Trading in Amsterdam. Outside work, he’s an avid hiker, preferably away from the flatlands of the Netherlands, and occasionally competes in programming competitions. He has competed in Excel Esports since 2024 and has grown fond of the community around it. Find him on LinkedIn and GitHub.
Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!
Fuente: Microsoft 365 Insider
