How to Import Table Data Into Excel Easily

Image: Screen capture of Excel 2007 interface by Jason Bean

Have you ever looked a website and thought, man I wish I could grab all that data in that table real quick and play around with it in Excel?

What? Tables? On a website? Used in HTML? That’s just wrong!

Hold on a sec! I’m not talking about using a table for layout control. I’m talking about a table actually being used to present tabular data in rows and columns. Yes. There is a real reason to do that.

The question then remains. How do you easily get that data?

I came across a tip today that I knew could be done, but I wasn’t aware of how you could do it in this way and format, which makes it pretty easy.

I usually would just select the text in the table using my mouse and copy and paste it into my Excel spreadsheet. But then I’d have to go in and reformat columns and disable hyperlinks and delete columns and other general formatting issues.

With this tool, I just open the page that holds the table of data and select what table it is I’m wanting to import into my spreadsheet.

Here are the steps you need:

1. Browse to the webpage that has the data you need to grab
2. Copy the URL of the page to your clipboard
3. Open a new workbook in Excel 2007 and navigate to the Data tab
4. Look for the Get External Data section (should be the first section on the left)
5. Select "From Web" in the toolbar

A window opens and lets you paste the URL of your webpage into the address bar and loads the web page into the window.

Note: It looks like the tool tries to open whatever page is set as your homepage initially. It was throwing script errors to me but I just clicked through the errors to keep moving forward.

Image: Screen capture by Jason Bean

You should see something similar to the graphic above. Each table on the page is highlighted with a yellow box and black arrow.

Select the table (the arrow changes to a checkmark) holding the data you want to import and click on "Import".

You’re then prompted to select the location you want to import the data into on your Excel spreadsheet.

Click OK and you’re ready to go.

Now you can manipulate and modify the data as much as you’d like in Excel.

Pretty cool eh?

Source: Microsoft Tips & Tricks Special Delivery Email