Share this content

Excel Tip: How To Import Data From a Web Page

Sep 29th 2017
Share this content

How to import data from the web in Excel: If you manually copy and paste data from a web page into Excel, you might find that all the data gets pasted into one cell.

That's annoying, and requires more copying and pasting. 

So, use the web query tool to save time, and make your worksheets more dynamic!

You can use web query to get refreshable (or static) data from your company's intranet or the internet, like (single or multiple) tables, or all the text on a webpage. Here's How

How to Import External data from the web

Just click File > Import External Data > Create New Web Query

Copy the URL of the web page you want to display and paste it into the Address box.

excel web query data

That should be it! Excel will import table data directly, and cleanly into your worksheet.  (Web query won't retrieve any photos or graphics)

Using Internet Explorer? 

If you use Internet Explorer, you can also import data to excel directly from your web browser.

Just right click on the webpage, and then select "Export to Microsoft Excel" on the shortcut menu.

This should display the New Web Query dialog box in Excel.

*Web browsers can change all the time, so personally, I prefer method one. 

101 Reasons to Import Data from a Website

Incorporating data from the web with this feature not only saves time, but it can make your worksheets more dynamic.

The next time you find a table of useful data online, try incorporating it into an Excel worksheet with the web query tool!

Replies (1)

Comments for this post are now closed.

By andymatt1962
Dec 5th 2017 15:46 EST

I have created spreadsheets to automatically list and sort daily updated data from the web, automatically refreshing whenever the workbook is opened.
Some suppliers display categories over a few pages (e.g. the address ending in "&page=2", "&page=3" etc.)
I formulated the workbook to collect data from a certain number of pages, but find that if any page has been removed, an error message "Unable to open [web address].
Internet site reports that the item you requested could not be found"
is displayed and the update will not continue until "OK" is clicked.

Is there a way to override this, so the workbook will just record "no data" for the missing page(s) and continue collecting the rest of the data from the other pages?

Thanks (0)