Getting started with Python in Excel for Microsoft 365
Sep 02,2023 | AddOn Systems Pte Ltd
Hello and welcome to AddOn News & Events, the latest happenings in tech over the past few days. Haven’t been able to follow the news closely? Don’t sweat it... AddOn is here! Here we go!!
By Microsoft Excel Get started with Python in Excel
Python in Excel is gradually rolling out to Excel for Windows customers using the Beta Channel. At this time, the feature is not available on other platforms.
If you encounter any issues with Python in Excel, please report them by selecting Help > Feedback in Excel.
New to Python in Excel? Start with Annoucing Python in Excel.
Start using Python
To begin using Python in Excel, select a cell, go to Formulas in the ribbon, and then select Insert Python. This tells Excel that you want to write a Python formula in the selected cell.
Or use the function =PY in a cell to enable Python. After entering =PY in the cell, choose PY from the function AutoComplete menu with the Down arrow and Tab keys, or add an opening parenthesis to the function: =PY(. Now, you can enter Python code directly into the cell. The following screenshot shows the AutoComplete menu with the PY function selected.
Once Python is enabled in a cell, that cell displays a green PY icon. The formula bar displays the same PY icon when the Python cell is selected. See the following screenshot for an example.
Combine Python with Excel cells and ranges
To reference Excel objects in a Python cell, make sure the Python cell is in Edit mode, and then select the cell or range that you want to include in the Python formula. This automatically populates the Python cell with the address of the cell or range that you selected.
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.
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:
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 in 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 Using 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. This means that 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.