Copy Website Tables into Excel 2007 Spreadsheets
If you find interesting or important data in a table on a website it might be helpful to import it into Excel. Today we will look at how to import data from a table on a website into Excel which will allow you to organize and add to reports.
Open Excel 2007 and under the Data tab select Get External Data and From Web.
This will open up the New Query window where you enter in the URL of the site you want to get data from. There will be yellow arrow icons next to the table data that can be imported, click the areas of the data you want to import which turns the box to a green checkmark. After you have what you want selected click on the Import button.
Next choose the cell where you want the data to appear in the spreadsheet and click OK.
The data will begin to be retrieved from the site, the amount of time it takes will depend on the amount of data you selected. Now you can organize the data how you like for presentation.
This tip comes in very handy when doing research or for easily adding website table data to reports.
Daily Email Updates
You can get our how-to articles in your inbox each day for free. Just enter your name and email below:
Feel like a puzzle? How would you use this technique to pull data from a table that spans multiple pages? For example, when you do this technique for "/www.fangraphs.com/leaders.aspx?pos=all&stats=bat&lg=all&qual=70&type=0&season=2009&month=3″, you get "Displaying page 1 of 7, items 1 to 35 of 230″. How would you pull data on pages 2-7 without going separately to each page?
Regards,
Jason B
Great hint! That's a real timesaver if/when you need it!
BTW, you can do this also in Excel 2003, by making this change…
Excel 2007: Go to “Data / Get External Data / From Web”
Excel 2003: Go to “Data / Import External Data / New Web Query”
Sometimes you can also highlight a table on a web site and then copy it and paste it right into Excel.
@Jason
I don't think multiple page data import is possible with out complicated macros… The best solution is to use firefox with autopager, use the site wizard and load all pages. Then just copy and paste everything into excel.
Its not a perfect solution because you cant refresh the data but its quicker than setting up data import for every page…
Hope that helps!
I am having problems pulling data from the web into Excel 2007. The data in
question can be found at the following site:
http://www2.fdic.gov/closedsales/LoanSales.asp
As a point of reference, I ran a search of data from 8/1/09 through 8/31/09
at that location and the website produced a data table that I attempted to
bring into excel 2007 by going to Data/Get External Data/From Web. When I
pull it into excel, it pulls the following prompt (which can be found at the
bottom of the initial website noted above:
"For sales with offices other than Dallas - Field Office Branch (FOB) please
refer questions to Dallas-FOB. See "Loan Sales FAQ" for contacts. "
Any idea what I am doing wrong?
Does anyone know how to do this one a mac computer?