What is the Web connector for Excel?

 

The web connector for Excel (AKA Get Data From Web) is a powerful tool that allows you to connect to websites and import data into your Excel workbook. The Web connector leverages the strength of Power Query in Excel to import data. You can also refresh your data connection to get the latest updates from the selected websites.

 

Note: The new Web connector will be available in Excel for Windows. 
We have plans to support Excel for Web and Excel for Mac in the future.

 

What's new in the improved connector for Excel?

 

We are excited to announce that we have released an improved web connector for Excel that has new features and enhancements:

• Suggested Tables: Provides auto-detection of suggested tables, so you can easily structure your data.
• Add Table Using Examples: Allows you to provide examples of what you want your data to look like, and select from AI-generated table structures based on the examples provided.

This new release has been long-awaited since its previous release to Power BI.

 

How to get started

 

The new Web connector in Excel is available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get the latest updates. You also need to have an internet connection to access the web data sources.

The following sections describe how to connect to a website, select data, and bring that data into Excel – using the new features.

 

To use the Web connector for Excel, follow these steps:
     1. Open Excel and create a new workbook or open an existing one.
     2. Go to the Data tab and click on From Web
     3. Enter the Web page URL from which you'd like to extract data.

 

In this article, we'll use the Microsoft Store Web page, and show how this connector works. If you want to follow along, you can use the Microsoft Store URL that we use in this article:
https://www.microsoft.com/store/top-paid/games/xbox?category=classics


     4. When you select OK, you're taken to the Navigator dialog box where you can see Suggested Tables, which are autodetected tables from the Web page.
In the case shown in the image below, 5 tables were found.



      5. Alternatively, you can use the new Add Table Using Examples feature, which presents an interactive window, where you can preview the content of the Web page. Enter sample values of the data you want to extract.

     6. In this example, you'll extract the Name and Price for each game on the page. You can do that by specifying a couple of examples from the page for each column. As you enter examples, Power Query extracts data that fits the pattern of example entries using smart data extraction algorithms.

 

Note: For more information, see Get webpage data by providing examples.

     7. Once you're happy with the data extracted from the Web page, select OK to add the data to the list, and select the custom table that was created:



     8. You can use Transform Data to shape your data with the Power Query Editor or Load to load the data to the Excel grid. For more information about Power Query in Excel, see Power Query for Excel Help.

We hope you like this new addition to Excel and we’d love to hear what you think about it! Let us know by using the Feedback button in the top right corner of Excel or clicking File > Feedback - add #PowerQuery in your feedback so that we can find it easily.