How-To Use Online Data In Excel 2010 And 2007 Spreadsheets

Using data from a live feed on the web is the best way to keep your Excel 2010 spreadsheets automatically up to date.  This can come in handy if you are tracking the prices of something, or even your favorite sports teams’ score boards.

Below is an example spreadsheet made by MSN money.  Several cells pull live data of different stock indexes.  All of this data is coming from the internet, and in this guide you’ll learn How-to do just that.

How To Use Live Imported Internet Data In Your Excel 2010 Spreadsheet

This guide covers Excel 2010, although the steps in Excel 2007 are very similar.

Step 1

Click the Data ribbon and then Select From Web.*

*If your Excel window is small enough you will be required to Click the Get External Data submenu in order to access From Web.

Step 2

In the Address bar, Type in a website URL that you would like to pull data from.  The URL doesn’t have to be exact because this window also has some limited functionality as a web browser.

Once the page with the information you want loads, you will see small yellow arrows next to the data tables that Excel is able to import.  Click the small yellow arrow of the data you would like to use.  The arrow should turn green, and if you need to import multiple data tables just Select multiple arrows.   Once ready, Click Import.

Step 3

The Import Data dialog box will appear.  There are a couple things you can do here, but they are completely optional.

1. You can Click Properties to set up auto-refresh of your web data.  This can be done later too.
2. You can customize which cells the data table will be imported to, or even create a new spreadsheet for it.

Click OK when you are done customizing things.

Step 4

We’ll assume you Clicked Properties in the Import Data window from Step 3 to bring up the External Data Range Properties dialog.  There are a few additional settings in here you can change, but the focus are the Refresh every ## minutes and the Refresh data when opening the file options.

Since the whole point of using web data is to have updated data, why not turn on auto refresh?  Click OK to save your settings when finished.

Done!

Your web data should now be imported to your Excel sheet.  If at any time you want to do a manual refresh of the data, Right-Click any field in the table and from the context menu Select Refresh. From this same menu you can also get back into Data Range Properties to adjust the automatic refresh time.

If you only wanted a portion of the table and not the entire thing, you can always just Select the unwanted cells and Press Delete to get rid of them.  Groovy, eh?