Python in Excel runs Python code inside a worksheet cell, with the calculation executing in the Microsoft Cloud rather than on your machine. It suits analysis that formulas handle badly, such as profiling messy data, flagging exceptions and grouping large sets. It does not automate Excel and it cannot reach your files or the internet. It is not always the right answer either. Here is what it does and where the line sits.
Table of Contents
What is Python in Excel and how does it work?
Python in Excel is a Python formula that lives in a worksheet cell. You type =PY into the cell and write Python below it. The code runs in the Microsoft Cloud on a library set curated by Anaconda, then returns results to the grid. Nothing is installed locally and the code never touches your machine.
Microsoft’s introduction to Python in Excel states that “Python in Excel calculations run in the Microsoft Cloud with a standard version of the Python language”, with no local install and results returned straight to the worksheet.
Two functions matter. PY holds the code, inserted from the Formulas tab or by typing =PY. xl reads the workbook, so xl("A1") pulls a single cell and xl("MyTable[#All]", headers=True) pulls a table with its headers. Both are set out in Microsoft’s getting started guidance.
Most people who ask us about Python in Excel are describing something else. They mean a Python script that opens a workbook, does the work and writes the results back. That runs on a machine you control with any library you like, and it can reach files and systems. Python in Excel is the opposite. The code lives in the workbook and runs in a sandbox that cannot reach anything outside it. Confusing the two is the biggest source of disappointment we see on this topic.
For where this sits alongside Power Query, pivot tables and the rest, our guide to Excel for business maps the full toolkit.
Is Python in Excel free or do you need the paid add-on?
Python in Excel is included with paid Microsoft 365 subscriptions that come with the desktop apps, on standard compute with automatic calculation only. A separate add-on licence buys premium compute for faster calculation and adds manual and partial calculation modes. Most business analysis sits comfortably inside the included tier.
Microsoft’s add-on licensing FAQ sets out a two-tier model. The base capability comes with the Microsoft 365 subscription. The paid add-on delivers, in Microsoft’s words, “faster Python formula calculation times”, plus the ability to “switch between manual, partial, or automatic calculation modes”.
The practical difference is less about features and more about how the workbook behaves while you work in it.
| What you get | Included with Microsoft 365 | Python in Excel add-on |
|---|---|---|
| Compute | Standard | Premium, faster calculation |
| Calculation modes | Automatic only | Manual, partial and automatic |
| Suits | Analysis packs, validation, exploratory work | Large datasets and heavy recalculation |
Eligibility is not universal. Microsoft’s availability guidance states that Python in Excel requires a paid Microsoft 365 subscription that includes the desktop apps, and that device-based licences and shared computer activation are excluded. The licensing FAQ also excludes Office 365 Education and Office 365 Government GCC High and DoD.
Our position is to leave the add-on alone until the workbook proves it needs it. Automatic-only recalculation sounds limiting on paper and on a normal analysis pack it is not. The tier question only becomes real once every edit triggers a wait, and that is usually the same point at which the analysis should be leaving Excel anyway. Paying to make a slow workbook faster is often a way of postponing a decision you should be making.
How do you turn Python in Excel on and why is Insert Python missing?
Open a blank workbook, go to the Formulas tab and look for Insert Python. If the button is not there, the cause is almost always the platform, the subscription or the Office update channel rather than a setting you can switch on yourself. Excel for iPad, iPhone and Android do not support it at all.
Platform support is the check people skip, and it is the one that most often ends the conversation, so confirm it before anything else.
| Platform | Python in Excel | What to expect |
|---|---|---|
| Excel for Windows | Yes | Enterprise and Business on Current or Monthly Enterprise Channel from Version 2408, Semi-Annual Enterprise Channel from Version 2502 |
| Excel for the web | Yes | Available to Enterprise and Business users |
| Excel for Mac | Yes | Enterprise and Business on Current Channel from Version 16.96 |
| Excel for iPad, iPhone and Android | No | The workbook opens, and Python cells show an error when recalculated |
Work through the causes in this order, cheapest check first.
- Confirm the platform, since Microsoft’s availability guidance states Python in Excel “isn’t available” on Excel for iPad, iPhone or Android.
- Confirm you are signed into Excel with the work account that holds the licence, not a personal account.
- Check the Office update channel against the versions in the table above.
- Check the licence type, since device-based licences and shared computer activation are excluded.
- If all four pass and the button is still missing, it is a tenant or policy question for your Microsoft 365 admin.
That last table row matters more than it looks. Microsoft’s guidance is that on unsupported platforms “workbooks containing Python can be viewed but Python cells display an error when recalculated”. A workbook that works for you can look broken to a colleague on a phone, so a mobile audience is a design constraint rather than a footnote.
What can you actually do with Python in Excel?
Python in Excel earns its place on analysis that formulas make unreadable, such as profiling a messy export, flagging exceptions before sign-off and grouping or ranking large sets. It is not for lookups, totals or business rules. Those stay in formulas, where the whole team can read and change them without help.
We run Python in Excel in production for a finance team in Sydney. Before it, their loop was an export out of Excel into a separate tool, the work done there, then the results pasted back. Every step in that loop let the numbers drift out of sync with the source.
Three patterns carry that workbook.
Profiling and data quality checks. Before anyone trusts a figure, the Python summarises what actually arrived, covering row counts, missing values, ranges and duplicates. It answers in one cell what people were previously answering by scrolling.
Exception flagging. Values outside expected ranges come back as a list to review rather than a colour on a cell. This is the pattern that changed the outcome. Errors that were slipping through are now caught before sign-off, because the check runs every time instead of when someone remembers.
Segmentation and aggregation. Grouping, bucketing and ranking that would have sprawled into nested formulas across helper columns now sits in a few readable lines of code.
We have deliberately not put a number on the errors now being caught, because nobody was counting them before. That is the point. There is no honest baseline to compare against, and inventing one would be worse than leaving it out.
Worth naming what is not on that list. No lookups, no totals and no business rules like status or margin. Those stay in formulas, because the team has to change them without calling us. For the analysis techniques that need no code at all, our Excel data analysis guide covers the ground.
Python in Excel vs Power Query vs VBA, which one for what?
Power Query shapes and combines data on a repeatable refresh. Formulas hold the business rules the team has to read. VBA automates the application itself, saving files, formatting sheets and sending email. Python in Excel does analysis the others handle badly. They are not competing tools and most real workbooks use more than one.
The clearest line drawn on Python versus VBA comes from Excel author Leila Gharani, whose guide was updated in April 2026. She writes that “Python in Excel is for data analysis: cleaning data, building charts, running models. VBA is for automation: saving files, formatting sheets, sending emails, triggering actions”. Her list of what Python in Excel cannot do includes renaming, saving or moving sheets, exporting data, automating email and managing form controls.
That matches what we find. Teams expecting Python to replace their macros leave disappointed. Teams expecting it to replace unreadable analysis formulas are usually happy.
Rather than ranking the tools, match each job to the thing built for it, because the wrong choice normally shows up as maintenance pain months later rather than as an immediate failure.
| Job | Use | Why |
|---|---|---|
| Clean, reshape and combine data every week | Power Query | Repeatable named steps that refresh on demand |
| Business rules like status, margin or eligibility | Formulas | The team has to read and change them without help |
| Profiling, outliers, segmentation and statistical work | Python in Excel | Stays readable where nested formulas do not |
| Saving, formatting, exporting, emailing and buttons | VBA or Office Scripts | Python in Excel cannot touch the application |
| Scheduled, unattended production runs | A script or pipeline outside Excel | Nothing in the workbook is built to run without a person |
What are the real limitations of Python in Excel?
The limits are structural rather than temporary. Python in Excel code cannot reach the internet, your files, your network or the rest of the workbook’s objects. It needs a connection to run at all and it does not work on mobile. Results come back as a Python object until you convert them to Excel values.
Microsoft’s data security documentation is unusually direct. It states that “The Python code doesn’t have network access” and that “The Python code doesn’t have access to your computer, devices, or account”. It also states the code “doesn’t have access to other properties in the workbook, such as formulas, charts, PivotTables, macros, or VBA code”.
Read that as a design decision rather than a gap. The sandbox is what makes the feature safe inside a compliance boundary, and the same sandbox makes it useless for the jobs people hope it will do.
The limits that actually bite in client work:
- It needs internet access. Microsoft’s introduction states plainly, “To use Python in Excel, you need internet access.” No connection means no calculation, so offline work is out.
- Results are Python objects by default. A cell returns an object with a card icon until you switch it to Excel values through the Python output menu in the formula bar. Charts and conditional formatting only work on converted values. This catches nearly everyone once.
- Row-major calculation order. Cells calculate across each row from column A rightwards then down the sheet, so a variable has to be defined before it is referenced.
- The library set is fixed. You get what the curated Anaconda distribution provides. Installing your own package is not an option.
- It cannot automate anything. Python in Excel does analysis only, so saving, formatting, exporting and email stay with VBA or Office Scripts. This is the limitation that surprises people most.
The one we design around hardest is the mobile gap. A review workbook that errors on an iPad is a workbook people quietly stop trusting. Where output has to travel, we convert the result to static values or push it into a Power BI page rather than leaving a live Python cell for someone else to recalculate. Our post on building spreadsheets people actually want to use covers the same principle more broadly.
Is Python in Excel safe for client data?
Python in Excel runs inside your organisation’s compliance boundary in hypervisor-isolated containers that are destroyed after use, with no data persisted in the Microsoft Cloud. The code cannot reach your network or your machine. In practice this is a far easier security conversation than most teams expect it to be.
Microsoft’s data security documentation states that “Python code runs within the compliance boundary of your organization on hypervisor isolated containers”, that “once a container is used, it’s destroyed. No data persists between sessions or users” and that “Python in Excel doesn’t handle or store any data at rest”.
On the Sydney engagement the security question never came up. That is worth saying out loud, because the assumption that “the code runs in Microsoft’s cloud” will trigger a review stops more teams than any actual policy does. The accurate framing for IT is that Python in Excel is a Microsoft 365 connected experience inside the same boundary as the rest of the tenant, not a new vendor and not a new data flow.
The governance work that matters is ordinary. Keep the input as a single Excel Table so the shape stays predictable. Separate inputs, outputs and checks so nobody types over a result. Add a visible validation strip showing row counts, totals and date ranges. Write the assumptions down, because whoever inherits the workbook cannot ask you.
When should the analysis leave the workbook entirely?
Move the analysis out when the logic outgrows what Excel’s engine can do, when the output stops being read in Excel or when recalculation time makes the workbook unusable. Any one of those three is enough on its own. Python in a cell is a good tool right up to the point where the workbook stops being the destination.
This is where most Python in Excel advice stops.
We build the other side of this line too. For a retail analytics business in Regional NSW, every retailer review meant building a large PowerPoint deck by hand over days, pulling numbers out of Power BI into templates. We replaced that with a Python tool that builds the whole deck from the client’s existing Power BI data. The live review deck automation case study records the outcome as roughly 660 slides, built in under five minutes, from one click, replacing days of manual work.
That job is not Python in Excel, and it could not have been. The Python applies data manipulation Excel’s engine cannot do, it writes a PowerPoint file and it runs without a workbook being open. Every one of those puts it outside the sandbox. Calling it Python in Excel would be wrong, and that distinction is the point of this section.
Three triggers, any one of which moves the work out:
- The logic outgrows the engine. If the manipulation genuinely cannot be expressed inside Excel, no amount of Python in a cell fixes it, because the constraint is the container rather than the language.
- The output stops being read in Excel. The moment the answer’s destination is a deck, a portal, another system or a scheduled email, the workbook has become a middleman.
- Recalculation makes it unusable. When every edit triggers a wait long enough that people avoid opening the file, the workbook has outgrown itself. The add-on licence buys time here, it does not solve it.
The mistake we see is treating the move out as a failure of Excel. It is not. Excel did its job right up to the boundary, and recognising it early is cheaper than a year of workarounds. That handover, from workbook to pipeline, is what our reporting and analytics practice does most often.
Frequently asked questions
Is Python in Excel free?
Python in Excel is included with paid Microsoft 365 subscriptions that come with the desktop apps, running on standard compute with automatic calculation only. A separate paid add-on licence provides premium compute for faster calculation and adds manual and partial calculation modes. Device-based licences and shared computer activation are excluded, as are Office 365 Education and Office 365 Government GCC High and DoD. For most business analysis, the included tier is enough.
Do you need to know Python to use Python in Excel?
You need enough Python to read and adapt code rather than enough to write it from scratch. In practice the useful patterns are short, usually a few lines of pandas to group, filter or summarise a table. The bigger skill is knowing which analysis belongs in code and which belongs in a formula. If nobody on the team can read the code, the workbook will not survive handover.
Which Python libraries are available in Python in Excel?
Python in Excel runs on a curated distribution provided by Anaconda, which includes the common analysis and visualisation libraries such as pandas, Matplotlib, Seaborn, statsmodels and scikit-learn. The set is fixed, so you cannot install your own packages into the environment. If your analysis depends on a library outside that distribution, the work belongs in Python running outside Excel rather than in a worksheet cell.
Can Python in Excel replace VBA macros?
No. They do different jobs. Python in Excel does analysis, and it cannot rename, save or move sheets, export data, send email or manage form controls, because the code has no access to the application or to anything outside the workbook. VBA and Office Scripts do that automation work. If your macros are automating Excel rather than analysing data, Python in Excel will not replace them.
Does Python in Excel work offline?
No. Microsoft states that internet access is required, because the calculation runs in the Microsoft Cloud rather than on your machine. Without a connection the Python cells will not calculate. This also affects colleagues, since a workbook containing Python opens on Excel for iPad, iPhone and Android but the Python cells return an error when recalculated on those platforms.
Can Python in Excel read a file from my computer?
No. The code runs in an isolated container that Microsoft states has no network access and no access to your computer, devices or account. It can only reach data already in the workbook, through the xl function, either from cell references or through a Power Query connection name. If you need to read a local file or call an API, that is a job for Python running outside Excel.
Why can I not see Insert Python in Excel?
Check four things in order. Confirm the platform, since Excel for iPad, iPhone and Android do not support it. Confirm you are signed in with the work account holding the licence. Check the Office update channel and version against Microsoft’s availability guidance. Check the licence type, because device-based licences and shared computer activation are excluded. If all four pass, it is a tenant or policy question for your Microsoft 365 admin.
Final take
Python in Excel is worth using when the analysis stays inside the workbook and the audience reads the answer in Excel. It profiles messy data, catches exceptions before sign-off and handles grouping and ranking that formulas make unreadable. It does not automate anything, it cannot leave its sandbox and it stops being the right tool the moment the answer belongs somewhere else.
If your team is carrying analysis in spreadsheets that has outgrown formulas, that is the work our spreadsheet solutions team does. Talk to us about which side of the line your workbook sits on.
