Python in Excel is currently in preview and is subject to change based on feedback. To use this feature, join the Microsoft 365 Insider Program and choose the Beta Channel Insider level.
Don’t have Insider access? Sign up with your Microsoft, work, or school account to be notified about future availability for Python in Excel.
Gradual Rollout of Python in Excel for Windows Beta Channel; Not Yet Available on Other Platforms.
Report any Python in Excel issues by choosing Help > Feedback in Excel if you encounter problems.
New to Python in Excel? Start with Introduction to Python in Excel.
Start using Python
To start using Python in Excel, choose a cell, go to the Formulas tab, and select Insert Python. This indicates to Excel that you intend to write a Python formula in the chosen cell.
Enable Python in a cell by using the function =PY. Once you’ve typed =PY, select PY from the function AutoComplete menu using the Down arrow and Tab keys or add an opening parenthesis: =PY(. You can now input Python code directly into the cell. Refer to the screenshot below, displaying the AutoComplete menu with the PY function selected.
After enabling Python in a cell, the cell will showcase a distinctive PY icon. Similarly, the formula bar will exhibit the PY icon when the Python cell is chosen. Refer to the example in the screenshot below for clarity.
Integrate Python with Excel Cells and Ranges
When referencing Excel objects in a Python cell, enter Edit mode in the Python cell and choose the desired cell or range. This action will automatically input the address of the selected cell or range into the Python formula.
Tip: Use the keyboard shortcut F2 to toggle between Enter mode and Edit mode in Python cells. Toggling to Edit mode allows you edit the Python formula, and toggling to Enter mode allows you to select additional cells or ranges with your keyboard. To learn more keyboard shortcuts, see Python in Excel keyboard shortcuts.
Python in Excel uses the custom Python function xl() to interface between Excel and Python. The xl() function accepts Excel objects like ranges, tables, queries, and names.
You can also directly type references into a Python cell with the xl() function. For example, to reference cell A1 use xl(“A1”) and for the range B1:C4 use xl(“B1:C4”). For a table with headers named MyTable, use xl(“MyTable[#All]”, headers=True). The [#All] specifier ensures that the entire table is analyzed in the Python formula, and headers=True ensures that the table headers are processed correctly. To learn more about specifiers like [#All], see Using structured references with Excel tables.
The following image shows a Python in Excel calculation adding the values of cell A1 and B1, with the Python result returned in cell C1.
Formula bar
Use the formula bar for code-like editing behavior, like using the Enter key to create new lines. Expand the formula bar using the down arrow icon to view multiple lines of code at once. You can also use the keyboard shortcut Ctrl+Shift+U to expand the formula bar. The following screenshots show a formula bar before and after expanding it to view multiple lines of Python code.
Before expanding the formula bar:
After expanding the formula bar:
Tip: To learn more keyboard shortcuts, see Python in Excel keyboard shortcuts.Output types
Use the Python output menu in the formula bar to control how Python calculations are returned. Return calculations as Python objects or convert calculations to Excel values and output them directly to a cell. The following screenshot shows the Python formula returned as an Excel value.
Tip: You can also use the right-click menu to change the Python output type. Open the right-click menu and go to Python Output, and then select the desired output type.
The following screenshot shows the same Python formula as the preceding screenshot, now returned as a Python object. When a formula is returned as a Python object, the cell displays a card icon.
Note: Formula results returned to Excel values are translated to their closest Excel equivalent. If you plan to reuse the result in a future Python calculation, it’s recommended to return the result as a Python object. Returning a result as Excel values allows you to run Excel analytics, such as Excel charts, formulas, and conditional formatting, on the value.
A Python object contains additional information within the cell. To view the additional information, open the card by selecting the card icon. The information displayed on the card is a preview of the object, which is useful when processing large objects.
Python in Excel can return many types of data as Python objects. A useful Python in Excel data type is a DataFrame object. To learn more about Python DataFrames, see Python in Excel DataFrames.
External data
To import external data, use the Get & Transform feature in Excel. Get & Transform uses Power Query to import external data. All the data you process with Python in Excel must come from your worksheet or through Power Query. For more information, see Use Power Query data with Python in Excel.
Important: To protect your security, common external data functions in Python, such as pandas.read_csv and pandas.read_excel, aren’t compatible with Python in Excel. To learn more, see Data security and Python in Excel.Calculation order
Traditional Python statements calculate from top to bottom. Within a Python in Excel cell, Python statements do the same thing—they calculate from top to bottom. But in a Python in Excel worksheet, Python cells calculate in row-major order. The cell calculations run across a row (from column A to column XFD), and then across each following row down the worksheet.
Python statements are ordered, so each Python statement has an implicit dependency on the Python statement that immediately precedes it in the calculation order.
The calculation order is important when defining and referencing variables in a worksheet, because you must define variables before you can reference them.
Important: The row-major calculation order also applies across worksheets within a workbook and is based on the order of the worksheets within the workbook. If you use multiple worksheets to analyze data with Python in Excel, make sure to include data and any variables storing data in cells and worksheets preceding the cells and worksheets that analyze that data. Recalculation
When a dependent value of a Python cell changes, all Python formulas are recalculated sequentially. To suspend Python recalculations and improve performance, use either Partial Calculation or Manual Calculation mode. These modes allow you to trigger a calculation when you’re ready. To change this setting, go to the ribbon and select Formulas, then open Calculation Options. Next, select the desired calculation mode. The Partial Calculation and Manual Calculation modes suspend automatic recalculation for both Python and Data Tables.
Disabling automatic recalculation in a workbook during Python development can improve performance and individual Python cell calculation speeds. However, you must manually recalculate the workbook to ensure accuracy in each Python cell. There are three ways to manually recalculate a workbook in Partial Calculation or Manual Calculation mode.
- Use the keyboard shortcut F9.
- Go to Formulas > Calculate Now in the ribbon.
- Go to a cell with a stale value, displayed with strikethrough formatting, and select the error symbol next to that cell. Then select Calculate Now from the menu.
Errors
Python in Excel calculations can return errors such as #PYTHON!, #BUSY!, and #CONNECT! to Python cells. To learn more, see Troubleshoot Python in Excel errors.
[nb]