Related topics
Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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 Current Channel (Preview) Insider level.

This feature is gradually rolling out to Enterprise, Business, Education, and Family and Personal users running Current Channel (Preview) on Windows. It's rolling out to Excel for Windows first, starting with Version 2406 (Build 17726.20016), and then to other platforms at a later date. For more information about availability, see Python in Excel availability.

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 Introduction to Python in Excel and Get started with Python in Excel

What is Power Query?  

Power Query is a data transformation and preparation tool designed to help you shape your data, and it’s available in Excel and other Microsoft products.

Use Power Query with Python in Excel to import external data into Excel and then analyze that data with Python. 

Important: Power Query is the only way to import external data for use with Python in Excel.

To learn more about Power Query, see Power Query for Excel Help

How to use Power Query to import data for Python in Excel 

Take the following steps to learn how to import a sample external data set and to use with Python in Excel.

Tip: If you’re already familiar with Power Query and want to work with existing queries in your workbook, skip to Use Power Query data with Python in Excel later in this article.

  1. To see the Get & Transform Data and Queries & Connections groups, select the Data tab. Choose Get Data to select your data source. You can import data from multiple sources, including comma-separated values (CSV) files. Every import creates a query.

    This example imports data from the Northwind OData service, a test data source. To do this, select Get Data From Other Sources From OData Feed.

  2. Next, enter the following link to the Northwind OData service and select OK.

  3. In the Power Query preview dialog, select a data table to load into Excel. For this example, select the Categories table, and then select the arrow next to the Load button and choose Load To.... The following screenshot shows the Power Query preview dialog with the Categories table selected and the Load To... option displayed.

    Set up a Power Query connection for Python in Excel. Choose the Categories table from OData.


    • You can also select Transform Data from the Power Query preview dialog. This allows you to edit the data in the Power Query Editor before importing it into Excel.

    • If you want to view the data on the Excel grid, select the Load button. This will load the selected table directly to the Excel grid.

  4. Select Only Create Connection to establish the data connection from the source. Power Query automatically creates a query for this connection.

    Choose Only Create Connection from the Import Data dialog.

  5. The Queries & Connections pane opens automatically, listing the query created by Power Query for the selected data source—in this case, the Categories table from the Northwind OData sample dataset. To manually open the Queries & Connections pane, on the Data tab select Queries & Connections.

    Tip: Hover over the queries in the task pane to get a preview of the data.

The next section describes how to analyze the Categories data with Python in Excel.

Use Power Query data with Python in Excel

The following procedure assumes that you’ve created a Power Query connection with the Categories data from the Northwind OData service described in the preceding article section. These steps show how to analyze the Categories data with Python in Excel.

  1. To work with external data with Python in Excel, enable Python in a cell by entering the =PY function. Next, use the Python in Excel xl() formula to reference Excel elements, like a Power Query query. For this example, enter xl("Categories") into the Python in Excel cell.

  2. The Python cell now displays the Categories data within a DataFrame, as returned by the xl("Categories") formula. Select the card icon next to the DataFrame to view a preview of the data in the DataFrame. The following screenshot shows the DataFrame preview.

    A preview of the data within the DataFrame object.

    Tip: Resize the DataFrame dialog using the icon in the bottom right corner.

  3. Convert the data in the DataFrame to Excel values, which returns the data to the Excel grid. To convert the DataFrame to Excel values, select the cell containing the DataFrame, and then select the Insert Data icon. The following screenshot shows an example of this icon next to the DataFrame.

    Choose the Insert Data option for the DataFrame object.

    Selecting the Insert Data icon opens a menu with a list of values relevant to your data.

    View the menu after selecting Insert Data for the DataFrame object.

  4. Select the first value in the menu, arrayPreview. This value displays the Python data from the DataFrame in the Excel grid. See the following screenshot for an example.

    See the arrayPreview result from the DataFrame in the Excel grid.

  5. Now that you’ve used Power Query to import external data and processed that data with Python in Excel, you’re ready to start analyzing data with Python in Excel. To learn how to create Python plots and charts with your data, see Create Python in Excel plots and charts.

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.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!