Python in Excel DataFrames
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.
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? Begin by reading Introduction to Python in Excel and Get started with Python in Excel.
What is a DataFrame?
A DataFrame is a two-dimensional data structure in computer programming languages, similar to an Excel table. In Python, a DataFrame is an object in the pandas library. The pandas library is a core library used by Python in Excel, and DataFrame objects are a key structure for analyzing data with Python in Excel.
Note: Python in Excel uses a DataFrame as the default object for two-dimensional ranges.
Python in Excel can output a DataFrame in two ways: as a Python object or converted to Excel values. When a DataFrame is returned as a Python object, the cell displays the text “DataFrame”, preceded by a card icon. The following screenshot shows a DataFrame as a Python object in an Excel cell.
To view the information within a DataFrame, select the card icon in the cell or use Ctrl+Shift+F5. The following screenshot shows a DataFrame object with the card view open. This example DataFrame repeats the data in cells A1:B3 on the worksheet.
You can also import external data into Excel and then process that data with Python in Excel. To import external data, use Power Query. To learn more about this, see Use Power Query to import data for Python in Excel.
Change the Python output type
The data within a DataFrame can be returned as Excel values instead of as a Python object. Output a DataFrame as Excel values to incorporate other Excel-based analytics like charts, Excel formulas, and conditional formatting.
Use the Python output menu in the formula bar to control how Python calculations are returned. The following screenshot shows the Python output menu in the formula bar.
The following screenshot shows the preceding DataFrame data, but this time the data is returned as Excel values. Because this DataFrame repeats the data in cells A1:B3 on the worksheet, the Python result in cells C1:D3 matches cells A1:B3.
Note: If a cell in the output range, which in the preceding scenario is range C1:D3, already contains a value, then the Python cell returns the #SPILL! error instead of the Python output. To learn more, see Troubleshoot Python in Excel errors and How to correct a #SPILL! error.
Important: When the output type of a DataFrame is set to Excel values, the DataFrame only outputs the index column if one of the following conditions is met.
-
If values of the index column are not numeric (like the result of describe() or group_by()).
-
If the index column name has been set.
You can also use the right-click or Ctrl+click menu on any Python cell to change output types. Open the right-click menu and select Output As to change the output.
Related articles
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
