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.
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.
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.
You’ll see a Downloading message as Excel gets the initial table data from the site.
Select where you want Excel to place your web data, and click Ok.
You’ll see a message in the spreadsheet that Excel is getting the data.
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.
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.
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.
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.
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.
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.
