If you’re like most people, you use spreadsheets to manage your data. But what if you want to use online data? Excel 2010 has a lot of features for working with online data. In this article, we’ll show you how to use Excel 2010’s online features to work with data from websites. First, open Excel 2010 and create a new spreadsheet. In the new spreadsheet, click the Data tab and then click Online Data. This will open the Online Data dialog box. In the Online Data dialog box, you’ll first need to select a source of online data. You can select from a variety of sources including Google Sheets, Microsoft Excel files, and OpenOffice Calc files. Once you’ve selected your source of online data, click the Add button and then enter your website’s URL into the Source text box. Next, in the Columns text box, enter the column names that contain your website’s data. For example, if your website contains information about products, enter Product in Column 1 and Price in Column 2. If your website contains information about customers’ addresses, enter Customer_Address in Column 1 and City in Column 2. Finally, in the Rows text box, enter the row numbers that contain your website’s data. For example, if your website contains information about products that are available in different colors (colors 1-6), enter Product_Color in Row 1 and Price_Color in Row 2. If your website contains information about customers’ addresses that are available in different cities (cities 1-100), enter Customer_Address_City in Row 1 and Price_Color_City in Row 2. ..

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.