Import data from the web by using the web connector

Learn how to import data from any website and use table detection tools to quickly structure and model your data.

What is the web connector for Excel?

The web connector for Excel, also known as Get Data From Web, is a powerful tool that you can use to connect to websites and import data into your Excel workbook. The Web connector uses 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.

Advanced features in the web connector for Excel

The web connector for Excel has new features and enhancements:

  • Suggested Tables: Provides auto-detection of tables and suggests 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 you provide.

Note: These tools are also available in Power BI.

How to get started

The new Web connector in Excel is available as part of an Office 365 subscription. If you're an Office 365 subscriber, find out how to get the latest updates. You also need 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 select From Web
    (or select Get Data > From Other Sources > From Web).

  3. Enter the URL of the web page from which you want to extract data. In this article, we use the Microsoft Store web page and show how this connector works.
    If you want to follow along, use the Microsoft Store URL that we use in this article: https:⁠//www.microsoft.com/store/top-paid/games/xbox?category=classics
    Enter URL

  4. When you select OK, you go to the Navigator dialog box where you can see Suggested Tables, which are autodetected tables from the web page.
    In the following image, the connector finds five tables.

    Navigator dialog showing five suggested web tables

  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 extract the Name and Price for each of the games 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 by using smart data extraction algorithms.

    Add Table Using Examples

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

  7. When 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:

    Select custom table

  8. 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.

Move an older web query to Power Query

Note

The Web (Legacy) data import wizard is deprecated. Use the Power Query web connector for new web queries.

To move an older web query to Power Query, use the URL from the older query to create a new web query:

  1. Right-click the section of imported data that you want to migrate.
  2. From the shortcut menu, select Edit Query.
  3. Copy the URL from the address bar.
  4. On the Data tab, select From Web in the Get & Transform Data group.
  5. Paste the URL into the From Web dialog box, and then select OK. Follow the instructions in How to get started to select the data and load it into your workbook.

See Also