Importing Useful Information From The Web To Excel

Spreadsheets are not the only sources of large amounts of useful data. Very often, such data is also on websites, as they are sometimes constructed in a database friendly way (like tables of information). If you've spotted some data online that you think would be very useful to you or your business, then it's time to learn how to import it into Excel.

Why would you want to do this? If you're still paying for net access by bandwidth usage (not so common for home, landline based internet packages anymore, but very common if you're accessing the web from a mobile device), it will enable you to play with the data "offline", not running up your bandwidth or download costs. You can also manipulate the data easily, whereas it would be static when it's on the webpage. What's more, you can import more than one set of data from more than one website, making your own super spreadsheet.

Let's say that you're a shipping company (or even a private Ebay seller) and you know that your favourite postal services all have different rates to different countries. You think it would be a great idea to import the table of services/prices from each site, so you can put it in a spreadsheet and easily compare costs per country when you know you want to ship something there. Very useful. Here's how you'd do it.

Excel is already essentially made up of tables/cells. If your intended website has a table on it, it's time to get importing. You can "tell" Excel to import all the tables on a site, or just one. You need to give the tables names or numbers to do this, using the "id" attribute. Give every table a unique name or number (id).

Then, select the cell in excel where you would like the start of the table data to be pasted, and then "get external data", followed by "new web query". Then enter the web address of where the data is located, followed by the names of tables (with id) that you want to import.

Voila - you now have all the data from the web imported onto your workbook where you can enhance it with pivot tables, analyse it, and add functions so you can feel your way around it more easily. If it's a large amount of information, you might even want to consider exporting the data from your Excel spreadsheet to an Access database, as Office products tend to work seamlessly in this way.

Once you have the data, you can reformat it to fit the rest of your spreadsheet, and use it at your leisure. Obviously, it goes without saying that you shouldn't republish the data as your own (it does, after all, belong to someone else). If it's personal data, you have to be even more careful about how you use information downloaded from the web. The norm is that anything sensitive won't be easily accessible to you anyway, but as with taking data sources from any part of the web, it's what you do with it that counts!