Recently I put together a post showing how to import data from the web using Excel 2016. But since many businesses haven’t upgraded to the latest version of Office, I wanted to share how to do something similar in Excel 2010.
Excel 2010 Web Data Import
The steps I would have taken to import the same data in Excel 2010 are as follows:
- Navigate to the “Data” tab in the ribbon, and select “From Web” to open the New Web Query window
- Enter the URL of the page
- Scroll to find the table of interest and select the yellow box with a black arrow near the upper left corner of the table. (The yellow box turns green when selected)
- Click “Import”
Finally, select the cell you would like to import the data into, and click OK.
The data is imported as an unformatted table:
What can go wrong?
First, the very basic browser built into Excel 2010 (which is likely a carry-over from even earlier versions of Excel) has a hard time rendering today’s web pages. For instance, when I tried to do the simple example shown above, I encountered almost constant dialog boxes notifying me of a script error. I had to repeatedly close out these dialogs just to scroll down to the table I wanted. There is probably truly an error in the script on this page. However, other browsers such as Chrome and Firefox handle the errors and render the page without constantly notifying the user that something is wrong.
Second, in order to identify tables in web pages, Excel looks for the <table> tag in the HTML script of the page. However, with modern web design practices it is possible to create elements on a web page that look like tables without using the <table> tag. Therefore, Excel does not recognize it as a table and you won’t be able to import using this method. This is a problem regardless of which version of Excel you are using.
