How To Import Data From Web Page Url Into Excel

In this article, I will show you how to retrieve data from a web page and import the data into an excel worksheet. With this function, you can prepare excel data very efficiently.

1. Prerequisite.

The data you want to import from a web page must be included in an Html table. Otherwise excel is not easy to retrieve those data.

2. Import Web Page Table Data Into Excel WorkSheet Steps.

There are two menus to import web page data into an excel worksheet. We will introduce them one by one.

2.1 Import Web Page Data Into Excel By Data -> From Web Menu.

1. Click the excel Data —> From Web ( in Get External Data group) icon on the top menu bar.
2. Then it will pop up a New Web Query window. Input the web page url in the Address text box and click the Go button after it, it will load the web page in the web browser below it and it will also add a yellow arrow at beginning of each Html table on the web page.
3. If you want to load an Html table data, you just need to click the yellow arrow to select it, then click the Import button to import the data. During the process, it will prompt the excel cell range confirm dialog to you, and you can change the table data target excel cell range.
4. If you find the above web browser can not parse out Html table data in the web page, and this makes you can not get the table data that you need, you can use the below method.

2.2 Import Web Page Data Into Excel By Data -> New Query – > From Web Menu.

1. This method is better than method one. Because it parses the Html page in the background, it does not use a web browser to parse the web page, so it can parse out more Html tables.
2. Click excel Data —> New Query ( in Get & Transform group ) —> From Other Sources —> From Web in the excel top toolbar.
3. Input the web page url in the popup From Web dialog URL text box. If you need to add headers or parameters to the url you can select the Advanced radio button to add more parts to the basic url then click the OK button.
4. Now it will popup Navigator dialog. In this dialog you can see a list of Html tables that excel parsed out on the left side, select the table which you want and it will display the table data on the right side. There are two tabs on the right side, one is Table View the other is Web View, you can click each tab to see different data views.
5. Now you can click the Load dropdown arrow ( at the dialog bottom right corner ) —> Load To menu item to load the selected Html table data into an excel worksheet.
6. In the next popup Load To dialog, you can select how you want to view this data in the worksheet ( select either Table or Only Create Connection radio button), and where the data should be loaded into ( select either New worksheet or Existing worksheet radio button ).
7. Click the Load button, then you can see the Html table data has been loaded into your excel worksheet.
8. There will also show a Workbook Queries panel on the excel right side,  it lists all the workbook queries.
9. You can right-click one workbook query and then click the Edit menu item to edit it, you can also click the Refresh menu item to refresh the data when the web page data is changed.