Use Online Data in Excel 2010 Spreadsheets

When integrating web analytics, usability testing data and other dynamic data, I need to keep my numbers fresh and up to date.  It is important that the numbers are updated regularly. Excel can import the data every time you open the file or even every minute.

Here is an article on how to set up your spreadsheets to include online data automatically.

Originally posted on  How-To Geek

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.

15 thoughts on “Use Online Data in Excel 2010 Spreadsheets

  1. Congratulations on having 1 of the most sophisticated blogs Ive come throughout in some time! Its just incredible how very much you can take away from one thing simply because of how visually beautiful it’s. Youve put together a fantastic weblog space –great graphics, videos, layout. This is absolutely a must-see blog!

  2. Nice to be going to your weblog again, it continues to be months for me. Well this write-up that i’ve been waited for so long. I require this write-up to complete my assignment inside the school, and it has exact same subject with your post. Thanks, fantastic share.

  3. Nice to become going to your blog again, it has been months for me. Properly this post that i’ve been waited for so long. I want this write-up to total my assignment inside the university, and it has exact same topic with your post. Thanks, terrific share.

  4. Great to be browsing your blog once more, it continues to be months for me. Well this post that i’ve been waited for so long. I want this write-up to total my assignment within the college, and it has exact same subject together with your write-up. Thanks, good share.

  5. I’m happy I found this blog, I couldnt learn any info on this topic matter prior to. I also run a site and if you want to ever serious in a little bit of guest writing for me if possible really feel free to let me know, i’m always look for people to test out my site. Please stop by and leave a comment sometime!

  6. This was a genuinely pretty beneficial submit. In theory I’d wish to write like this also – getting time and actual effort to make a wonderful piece of writing… but what can I say… I procrastinate alot and by no means appear to obtain a little something done.

  7. I was very delighted to find this web page.I wished to say thank you to you with regard to this excellent examine!!! I certainly enjoyed each tiny little bit of it and I have you bookmarked to examine out new items you post.

  8. Dude, please tell me that youre going to create a lot more. I notice you havent written an additional blog for a while (Im just catching up myself). Your weblog is just as well important to be missed. Youve obtained so a lot to say, these knowledge about this subject it would be a shame to see this blog disappear. The internet needs you, man!

  9. I think youve made some truly interesting points. Not too many people would actually think about this the way you just did. Im actually impressed that theres so much about this topic thats been uncovered and you did it so nicely, with so a lot class. Good one you, man! Seriously wonderful stuff right here.

  10. I admire the beneficial info you provide inside your articles. I’ll bookmark your blog and have my children test up right here frequently. I’m very positive they will discover a lot of new stuff right here than anyone else!

  11. I must say, as much as I enjoyed reading what you had to say, I couldnt help but lose interest after a while. Its as if you had a excellent grasp around the topic matter, but you forgot to include your readers. Perhaps you should think about this from more than 1 angle. Or maybe you shouldnt generalise so much. Its better if you think about what others may have to say instead of just going for a gut reaction to the topic. Think about adjusting your very own believed process and giving others who may read this the benefit of the doubt.

  12. I think youve created some truly interesting points. Not too many people would in fact think about this the way you just did. Im genuinely impressed that theres so considerably about this subject thats been uncovered and you did it so properly, with so very much class. Superior 1 you, man! Seriously fantastic stuff here.

Leave a Reply

Your email address will not be published. Required fields are marked *