Getting Started
Availability
The integration of Python in Excel is currently in its testing phase and may undergo changes based on user feedback. This feature is exclusively available to participants of the Microsoft 365 Insider Program on the Beta Channel.
If you're not an Insider yet, you can sign up using a Microsoft, work, or school account to receive updates about Python’s availability in Excel.
To become Microsoft Insider, click here. It's super easy.
Activating Python in Excel
To use Python in Excel, select a cell and go to the Formulas tab. Then, click on the Insert Python button. This action sets up the selected cell for Python scripting.
You will know when yougot it right when you get the =PY function. Enter =PY in a cell, and select the PY function from the AutoComplete menu using the Down arrow and Tab keys, or start with =PY( to directly input Python code.
When Python is activated in a cell, a green PY icon appears in that cell. The formula bar also shows this icon when a Python-enabled cell is selected.
Combining Python with Excel Elements
To link Excel elements like cells or ranges in a Python cell, ensure you're in Edit mode. Selecting a cell or range during this mode auto-fills the Python cell with its address.
Switch between Enter and Edit modes in Python cells using F2. Edit mode lets you modify the Python script, while Enter mode lets you select cells or ranges.
Xl() function for interfacing between Excel and Python. It can take Excel elements such as ranges, tables, queries, and names as arguments for the python script.
Directly input references in a Python cell using xl(). For instance, xl("A1") for cell A1, xl("B1:C4") for the range B1 to C4, or xl("MyTable[#All]", headers=True) for a table named MyTable.
Using the Formula Bar
The formula bar allows for coding-like editing. Expand it to see multiple lines of code at once, using the down arrow icon or Ctrl+Shift+U.
Python Output Types
The Python output menu in the formula bar lets you control how Python calculations are returned: as Python objects or converted to Excel values in a cell.
Right-click in a cell and navigate to Python Output to change the output type.
Python formulas returned as objects display a card icon. These can be viewed in detail by selecting the card icon, especially useful for large objects.
Python in Excel can process various data types as Python objects, including DataFrame objects.
Importing External Data
Import external data using Excel's Get & Transform feature, which relies on Power Query. Note that Python in Excel must source its data from your worksheet or Power Query.
Caution: For security reasons, common external data functions like pandas.read_csv and pandas.read_excel are not compatible with Python in Excel.
Understanding Calculation Order
In Python cells, calculations proceed from top to bottom. However, in an Excel worksheet, the calculation follows a row-major order, moving across and then down rows.
Python statements in a worksheet are ordered and dependent on the preceding statement.
Note: This row-major order also applies across multiple worksheets, so ensure that data and variables are properly sequenced.
Managing Recalculations
To control performance, use Partial Calculation or Manual Calculation modes, accessible under Formulas in the Calculation Options.
In these modes, use F9, Formulas > Calculate Now, or resolve stale values in cells to trigger manual recalculation.
Handling Errors
#BLOCKED!
Python in Excel must be enabled in a workbook for Python formulas to calculate. If you see the #BLOCKED! error, ensure that you have access to Microsoft 365 connected services.
#BUSY!
The #BUSY! error indicates that Python calculations are running in the Microsoft Cloud. If the #BUSY! error displays for longer than 60 seconds, try resetting the Python runtime. From the Formulas tab, in the Python group select Reset runtime. Or use the keyboard shortcut Ctrl+Alt+Shift+F9.
#CALC!
Python in Excel calculations can process up to 100 MB of data at a time. Attempting to run a calculation with more than 100 MB of data returns the #CALC! error. Try using a smaller dataset.
#CONNECT!
Try refreshing the connection with the Microsoft Cloud by resetting the Python runtime. From the Formulas tab, in the Python group select Reset runtime. Or use the keyboard shortcut Ctrl+Alt+Shift+F9.
#PYTHON!
The #PYTHON! error likely indicates a Python syntax error. The diagnostics task pane automatically opens if the #PYTHON! error is returned. Check the diagnostics task pane to see details about the error.
#SPILL!
The #SPILL! error likely indicates that a cell in the Python output range already contains data. This could be the result of returning a Python object as Excel values. The Excel values may spill across multiple cells.
#UNKNOWN!
The #UNKNOWN! error in a Python cell likely indicates that your version of Excel doesn’t support Python in Excel. To learn more, see the Feature requirements section earlier in this article.
Libraries
Open-source Python libraries
With a secure distribution from Anaconda, Python in Excel includes a standard set of Python libraries. You can use these libraries to analyze your data more easily, discover patterns and insights that are not obvious, and create visualizations with plots.
Core Python in Excel libraries
Python in Excel comes with several open-source libraries by default. You can use them by importing them with the statements shown below.
Python in Excel comes with some open-source libraries by default. You can use them by importing them with the statements shown below.
- Matplotlib: A low-level library that provides a wide range of plotting functions and customization options. It is the foundation of many other libraries and frameworks. To use Matplotlib, we need to import it as plt.
- NumPy: A high-performance library that offers fast and efficient operations on multidimensional arrays and matrices. It is the core library for scientific computing in Python. To use NumPy, we need to import it as np.
- pandas: A high-level library that provides easy-to-use data structures and tools for data manipulation and analysis. It supports various data formats, such as CSV, Excel, SQL, JSON, etc. To use pandas, we need to import it as pd.
- seaborn: A high-level library that builds on Matplotlib and provides a more attractive and intuitive interface for creating statistical graphics. It also integrates well with pandas and NumPy. To use seaborn, we need to import it as sns.
- statsmodels: A high-level library that provides a comprehensive set of statistical models and tests for various types of data analysis, such as regression, ANOVA, time series, etc. To use statsmodels, we need to import it as sm.
Note: You can find these core libraries in the Python in Excel initialization task pane as well. To access the initialization task pane, go to Formulas > Initialization in the Excel ribbon. This task pane shows the initialization settings for your Python in Excel runtime, but you cannot modify them.
How to import libraries
You can use Anaconda to access more libraries besides the core ones. To import a Python library into Excel, write a Python import statement in a Python in Excel cell. For example, import numpy as np. This statement brings in the NumPy library and gives it the name np. You can use np to refer to NumPy in any Python formulas in that workbook, after you enter this import statement in a Python cell.
A good practice is to import your libraries and set any configurations on the first worksheet of your workbook, before running your Python formulas. This way, you can avoid any errors or delays. You can also dedicate the first worksheet only for this purpose, if you prefer.
Here are some other libraries that can be used in excel and are particularly useful for finance:
Library Name | Purpose in Finance | Link |
|---|---|---|
| NumPy | Used for numerical computing, essential for financial modeling and analysis | NumPy |
| pandas | Provides data structures and tools for effective data analysis in finance | pandas |
| Matplotlib | Essential for creating charts and visualizations of financial data | Matplotlib |
| seaborn | Enhances matplotlib for more advanced statistical data visualization | seaborn |
| SciPy | Used for scientific and technical computing in finance | SciPy |
| statsmodels | Useful for statistical computations and modeling in finance | statsmodels |
| scikit-learn | Offers machine learning tools, valuable for predictive analytics in finance | scikit-learn |
Now, we have enough of the theory, let's play!