SEARCH

How-To Geek

Use Online Data in Excel 2010 Spreadsheets

Want to use live, updated data from the web in your spreadsheets?  Here’s how you can import data from the web into Excel 2010 to keep your spreadsheets up to date quickly and easily.

Make a Webified Spreadsheet

To add dynamic data from a website to a spreadsheet, click the From Web button under the Get External Data section of the Data tab in Excel.

sshot-2010-08-03-[21-27-51]

Enter a website address that you want to get data from, and click Go.  The page will load in the preview box, and you might have to scroll to find the data you want on the page.

image

You’ll see a small arrow beside any web tables you can import into Excel.  Click the arrow to select the data you want, and then click the Import button on the bottom of the dialog.

image

You’ll see a Downloading message as Excel gets the initial table data from the site.

image

Select where you want Excel to place your web data, and click Ok.

image

You’ll see a message in the spreadsheet that Excel is getting the data.

image

After a few moments, your web data will appear in Excel just like normal.  You may end up with a few extra cells and columns with unnecessary data, so feel free to remove any data you don’t want to use.

sshot-2010-08-02-[20]

Now you can manipulate the dynamic data just like you would any other Excel data.  You can use it in Graphs, Sparklines, and Formulas. Sparklines are a new feature in Excel 2010 and you might want to check out how to use them.  The great thing is, all of these will will automatically update whenever your web data is updated.

image

Refresh Your Data

If you’re concerned your data might be stale, click the Refresh All in the Data tab.  This will query the website for the latest data and update your spreadsheets.

sshot-2010-08-03-[21-42-47]

Or, if you’d like to make sure the data is automatically refreshed more often, select one of your dynamic cells in Excel and then click the click the Properties button under Connections in the Data tab.

sshot-2010-08-03-[21-30-32]

Check the Refresh every box, and enter the number of minutes you want.  By default, Excel will refresh the data every 60 minutes, but you can make it update much more often.  You can also select to have Excel update the data every time you open the file.  This way you’ll always have the latest data.

image

If you’re using static data from the web in Excel, such as the weights of minerals or the land area of states, you can even turn off the background refresh so Excel won’t be connecting to the internet unnecessarily.

Conclusion

The internet provides treasure-troves of data ready for you to manipulate and use as you want, and with this feature you can use Excel to help you use online data for your work.  From sports scores to melting points of metals to up-to-date exchange rates around the world, this is a great way to always have the data you need without having to enter it by hand or update it when something changes. 

If you’re using Excel 2007, here’s our tutorial on Copying Website Tables Into Excel 2007 Spreadsheets.

Matthew digs up tasty bytes about Windows, Virtualization, and the cloud, and serves them up for all to enjoy!

  • Published 08/4/10

Comments (6)

  1. Patti

    Do these files need to remain open to be refreshed? Or do they refresh even when Excel is closed? I am concerned about performance issues.

  2. jeri

    we are finding that using this method with Excel 2010, not all of the MySQL spreadsheet data is imported, it drops about a year’s worth of data. when refreshing it may add another five rows, or it may drop three. do you know of any refresh issues with Excel 2010 data connections?

  3. Swaps

    When i tryed to pull web data it’s going perfect, but it’s came with only header from that data, listing didn’t came that time or ever. any solution on it?

    Or Do i import data from web which is compatible with Firefox not with IE.

  4. Phillip Batz

    When importing data from the web into a spreadsheet, you cannot re-order the data according to the values in a single column without the next data refresh coming in all-messed-up. Is there a fix for this, or should I write my own spreadsheet program and take the lion’s share of the market overnight?

  5. Ofer Pillar

    While using Win7 and Excel 2010 this worked very well for several times and afterwards it caused the spreadsheet to turn un responsive. Refresh one query still works but not the option of represh all.
    for some reason the hyperlink can’t open the required destination and it fails to represh.
    If you can recommend how to fix this bug it would be great.
    Thanks

  6. Loki

    Hi, I also have a problem with Refresh All option. I had a Excel 2003 before and all web query worked fine. Now when I open it with Excel 2010 some of the Sheets I can Refresh but when I run Refresh All button it returns numerous mistakes (“Hyperlink cannot be followed to the destination”).
    Is there any solution to fix this?
    Thanks

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!