The combination of Python and Excel has become one of the most interesting topics for those who work daily with spreadsheets and need to get the most out of their data. For years, the only way to unite these two worlds involved external libraries, APIs, and a bit of patience. Now Microsoft has made a move and is integrating Python directly into Excel, completely changing the game.
In this guide, you'll learn how to use Python in Excel , what Microsoft's official integration offers, how to enable it, what you can do with it, and its current limitations. We'll also review the main classic alternatives based on libraries like openpyxl , pandas, and xlwings. And we'll even cover AI-powered tools that allow you to automate Excel without writing a single line of code.
What exactly is Python in Excel?
When we talk about Python in Excel, we're referring to the official functionality that Microsoft has incorporated into Excel, allowing you to write Python code directly into cells, just as if you were entering a formula. This code is sent to the Microsoft cloud , executed there with a standard version of Python, and the results are returned to your workbook as values, tables, charts, or Python objects.
For this new environment, Microsoft is relying on Anaconda , which provides a set of Python libraries widely used in data science and statistical analysis. The goal is to perform everything from complex data cleaning to advanced statistical modeling without leaving Excel.
The most striking feature is that you don't need to install Python locally or manage virtual environments on your machine. The engine runs in the cloud, with a standard and secure configuration, fully managed by Microsoft. This greatly simplifies things for business users who don't want to deal with installations and dependencies.
This approach allows you to combine the classic Excel ecosystem (tables, formulas, pivot tables, charts) with the full power of the Python ecosystem (DataFrames, advanced visualizations, statistical models, automation, etc.). All within the same workbook.
Availability and platforms where you can use it
The integration of Python into Excel is being rolled out gradually. As of today, it is not available on all platforms or to all users. Microsoft has made it clear that the goal is to integrate it into Microsoft 365 subscriptions , but the rollout is being done progressively.
Currently, this feature is available in Excel for Windows , Excel on the web , and Excel for Mac , provided your subscription and update channel are compatible. However, on some platforms, even if you can open workbooks containing Python cells, they cannot be recalculated. You will encounter errors if you attempt to do so.
Python in Excel is not available in Excel for iPad, Excel for iPhone, or Excel for Android . On these devices, workbooks containing Python code in cells can be viewed, but if a recalculation is initiated, the Python cells may return errors. This is because the Python execution engine is not supported.
Regarding the product's status, Python in Excel began as a public preview version , accessible to users of the Microsoft 365 Insider Program through the Beta channel. During this phase, the functionality has been evolving and expanding based on community feedback, with the goal of becoming a standard part of the Microsoft 365 offering in the near future.
How to enable and start using Python in Excel
If you have a valid Microsoft 365 subscription and the correct version of Excel, enabling Python in Excel is fairly straightforward, though not always obvious the first time you try. Ideally, you should start with a blank workbook to familiarize yourself with the workflow before working with critical files.
The first path goes through the ribbon:
- Go to the “Formulas” tab and click the “Insert Python” button.With that gesture you tell Excel that you want to convert the selected cell into a Python cell, in which you can literally write code in the language instead of a traditional formula.
- use the function =PY. Just write =PY In a cell, let it appear in the autocomplete menu and select it with the arrow keys and Tab, or type directly =PY(From that parenthesis onwards, the cell accepts Python code that will be sent to the cloud to perform the calculations.
Once a cell is converted to a Python cell, you'll see a "PY" icon inside the cell itself, and that same icon will also appear in the formula bar when the cell is selected. This is a quick way to identify which cells contain Python code versus those that only have Excel formulas.
Combining Python with Excel cells and ranges
One of the key aspects of this integration is that Python can work directly with the data already in the spreadsheet, without needing to export or duplicate it. To achieve this, Python in Excel uses a custom function called `xl()` , which acts as a bridge between Excel objects and Python code.
To reference a range from a Python cell, you can enter edit mode in that cell and select the cell or range you want to use with your mouse. Excel will automatically insert a call to `xl()` into your code with the corresponding address of the selected range or table. This saves you from having to type it manually.
If you prefer to write the reference directly, you can do that too. For example, to work with cell A1 from Python, you would simply use xl("A1") , while for a range like B1:C4 you would write xl("B1:C4") . In the case of named tables, the syntax becomes a bit more interesting.
Imagine you have a table with headers called MyTable . If you want Python to receive the entire table, including headers, you can use xl("MyTable", headers=True) . The specifier indicates that the entire table should be parsed, and the argument headers=True ensures that the headers are processed correctly in the generated DataFrame or structure.
How to use the formula bar with Python code
When you start writing longer code, Excel's formula bar becomes your best friend. It's not just a line for a quick formula; it's a small editor that becomes even more important when working with Python in Excel.
You can expand the formula bar by clicking the down arrow to its right, or by using the keyboard shortcut Ctrl+Shift+U . Doing so will display multiple lines at once, which is very useful when your Python code doesn't fit on a single line and you want to keep it readable.
Within the formula bar, you can use the Enter key to create new lines, just as you would in a code editor, instead of accepting the cell's contents. This allows you to write much more structured blocks of code, with line breaks, comments, and slightly more complex logic.
This behavior makes the experience more like working in a lightweight IDE , without leaving Excel. While you won't have all the options of a professional editor, you will have a sufficiently comfortable environment for analysis scripts, visualizations, and small models.
Python cell output types
Python in Excel isn't limited to simply dumping numbers into cells. You have considerable control over how your scripts' results are returned . From the formula bar itself, you can choose the output type, which affects both what you see in the cell and how you can reuse that result within the workbook.
In many cases, you'll want to convert the result directly into Excel values , for example, when the script produces a table that you want to use as a source for pivot tables or classic Excel charts. In that case, the Python cell content is translated into standard cells with native Excel data.
Another option is to return the result as a Python object . When you choose this method, the cell displays a card icon instead of a simple value. Clicking on this icon opens a card where you can preview the object. This could be a DataFrame, a list, a Matplotlib figure, etc.
This card is especially useful when you're working with large or complex objects . It's simply not practical to fit everything into a single cell. Furthermore, it allows you to retain additional information associated with the object—information that would be lost if you converted it directly to plain Excel data.
Among the most interesting data types you can return as a Python object are DataFrames . These objects integrate very well with the analysis workflow, as they allow you to manipulate, filter, and transform data with a level of detail that is sometimes difficult to achieve using only Excel formulas.
Import external data to analyze it with Python in Excel
For Python in Excel to work its magic, the data must first arrive in the spreadsheet or Power Query. Unlike a traditional Python script that can read directly from files, APIs, or other sources, here the flow relies on Excel acting as the gateway to the data.
The recommended way to bring information from external sources is to use the "Get & Transform" feature , also known as Power Query . From there, you can connect to CSV files, databases, online services, and much more. Then, you can export the transformed results to a spreadsheet.
Once the data is in the worksheet or a Power Query query , Python can access it using xl() and work with the resulting tables. This way, you combine Power Query's initial cleaning and transformation capabilities with Python's advanced processing.
Microsoft provides detailed documentation on how to use Power Query to import data for Python into Excel. This is because this combination has become a cornerstone of the new way of working with data within Excel, especially in enterprise environments with diverse data sources.
Python cell calculation order
In a traditional Python script, instructions are executed from top to bottom , following the order in which you wrote them. In a spreadsheet, however, the calculation engine works by cells and ranges, which requires establishing specific rules for Python cells.
Within a single Python cell, code is evaluated sequentially , as you would expect in any .py file: line 1 is executed before line 2, line 2 before line 3, and so on. Each line has an implicit dependency on the previous one. This affects the definition and use of variables within that cell.
However, if we look at the spreadsheet as a whole, Excel evaluates Python cells following a primary row order : it traverses each row from left to right (from column A to XFD) and then moves to the next row. This means that a Python cell located in row 5 will not be calculated before one located in row 4, even if it is further to the left.
This order is very important when defining variables in cells and reusing them in others , because you need the cell that declares them to be calculated before the one that references them. Otherwise, you'll encounter errors or unexpected results, just as with dependencies between Excel formulas.
In practice, this means structuring your spreadsheet logically. This way, cells that establish base values or previous calculations should be placed before (at the top or further to the left) the cells that rely on them. This maintains the consistency of the calculation flow.
Recalculation and calculation modes with Python in Excel
Every time you change a value that one or more Python cells depend on, Excel recalculates all the Python formulas sequentially , respecting the calculation order you just saw. This can be very convenient, but it can also impact performance if your workbook is large.
For greater control, Excel offers several calculation modes , including partial calculation mode and manual calculation mode . These modes suspend automatic recalculation for both data tables and Python cells, so recalculation only occurs when you choose to perform it.
If you're in the middle of developing your model, disabling automatic recalculation can significantly improve performance, especially if your workbook contains many Python scripts or resource-intensive queries. However, you'll need to remember to run the recalculation again to ensure the results reflect the latest changes.
To change the calculation mode, simply go to the ribbon, open the "Formulas" tab , and access "Calculation Options ." There you can select whether you want automatic, partial, or manual calculation. This applies to both standard formulas and Python cells.
When you are in partial or manual mode, you can force recalculation in three ways :
- Using the key F9.
- Going to Formulas > Calculate now on the tape.
- By clicking on the error symbol that appears next to a cell with an obsolete value (displayed with a strikethrough) and choosing the option “Calculate now” in the drop-down menu.
Python in Excel add-on license and premium capabilities
In addition to the basic functionality integrated into certain Microsoft 365 subscriptions, there is a Python-specific license add-on for Excel that provides access to additional features, primarily geared towards environments where performance and computing power are critical.
This add-on license includes, among other benefits, premium calculations. This translates to faster execution times for Python formulas, as well as improved responsiveness in workbooks that combine many Python cells or large volumes of data.
Another important point is that it expands the calculation mode options . This provides even finer control over when and how often Python formulas are recalculated. For advanced users and data teams, this can make all the difference when working with complex models.
If you want to know exactly what's included in your subscription and what this add-in adds, Microsoft maintains specific documentation on what's included in Microsoft 365 and the Python in Excel add-in license. It includes a detailed breakdown for businesses and education.
Cloud execution, security, and terms of use
All Python calculations in Excel are executed in the Microsoft Cloud , using a standard version of the Python language and a set of libraries provided by Anaconda. This cloud architecture has direct implications for security, performance, and maintenance.
From a security standpoint, Microsoft applies its usual data protection and regulatory compliance controls, including aspects related to the GDPR and the processing of personal data.
If you have a Microsoft 365 Family or Personal subscription , the use of this feature is governed by the Microsoft Services Agreement . In business and education environments, however, the Product Terms apply , specifying in more detail what is permitted and under what conditions.
A key detail is that you don't need to have Python installed on your computer to use Python in Excel. And even if you have a highly customized local installation, these configurations won't carry over to the cloud computing environment, which uses its own standard, isolated distribution.
Copilot in Excel with Python: AI at the service of analysis
The integration of Python into Excel doesn't come alone: it's complemented by Copilot in Excel , Microsoft's AI assistant that can now also work with Python code. This combination is designed precisely for users who, without being programming experts, want to take their analyses to the next level.
With Copilot you can formulate requests in natural language and let the system take care of generating the necessary Python code, performing the calculations and displaying the result in the spreadsheet.
This approach helps lower the barrier to entry for both Python and advanced Excel functions, because it transforms tasks that previously required considerable technical knowledge into something much more conversational and guided.
For users who are already proficient in Python, Copilot can also serve as support for testing analysis variants , exploring patterns, or quickly building model prototypes without spending so much time manually writing code.
Why using Python in Excel improves your daily life
The arrival of Python in Excel represents a significant leap forward for anyone working with data. The ability to manipulate and explore data with pandas, create charts with Matplotlib or Seaborn, or build statistical models with Statsmodels directly in the spreadsheet changes the landscape considerably.
With this integration, you can leverage all those libraries without installing anything extra on your computer, seamlessly integrating the results of your scripts with classic Excel formulas, pivot tables, slicers, and native charts. All within the same environment your colleagues already know.
The ability to create automations directly within the workbook is also very interesting. This ranges from periodic data cleaning and report generation to building dashboards that combine interactive Excel charts with visualizations produced by Python.
Data security, which has always been a concern in business environments, remains under the Microsoft 365 umbrella , making it easy to share workbooks, scripts, and results through Teams, Outlook, or SharePoint without leaving the corporate ecosystem.
Automating Excel: Code vs. AI-powered tools
Alongside Microsoft's native approach, another way of working has been gaining traction: AI-powered automation tools that allow you to manipulate spreadsheets using natural language. These solutions fall somewhere between classic Python scripts and assistants like Copilot.
The most traditional approach remains the programmatic route , using libraries like openpyxl to automate reading, writing, formatting, and charting. This method is ideal for developers who want to integrate Excel into complex data pipelines or larger applications, controlling every detail of the cells and the workflow.
On the other hand, AI tools like "Excel agent" (such as Excelmatic in some examples) are becoming popular, where you simply upload a file, describe in simple language what you want (clean data, create a chart, add formulas) and receive the modified workbook.
This "no-code" approach is very attractive to marketing, sales, and operations teams —generally, to anyone who needs to quickly automate reports and analyses without investing time in learning libraries or debugging scripts. AI handles translating business instructions into concrete technical operations.
The interesting thing is that you can now choose between three main paths to automate Excel:
- Code with classic libraries.
- Native Python integration within Excel.
- External AI agents.
The best option depends on the technical level of the team, the volume of data, and how much fine control you need over the process.
Given this landscape, the combination of Python and Excel is solidifying as a powerful solution for both technical professionals and business users. From Microsoft's native cloud integration with Copilot, through classic libraries like openpyxl and pandas, to new AI-powered tools that automate tasks for you, you have a vast array of options to take your spreadsheets to the next level without sacrificing the comfort of the environment you use every day.
