Have you ever found a great table of data on the web and then gone through a 90 step process to copy, paste and clean up that data to use in your Excel worksheet? In many cases, you can set up a link to that data that not only keeps the data in the tabular arrangement, but that allows you to refresh your data when the web site updates. Here is how it works.
Let’s say we found a table with gas prices, and that we run a business that involves local deliveries. To make things easier, we calculate our fuel cost factor at intervals of 10, 25, 50 and 100 miles for purposes of estimation. We’ve visited the website and captured the URL or address.
1. From Excel, on the Data, in the Get External Data group, select the From Web button.
2. Paste the URL of the website into the Address field of the New Web Query dialog box.
3. You’ll notice yellow icons with black right-facing arrows. These represent the data you may import into your worksheet. Select the one you need and notice it turns green.
4. Click the Import button in the lower right corner of the dialog box.
You’ll see Excel actually “go get” the information and load it into your worksheet. Remember, you are not looking at data in cells, you are looking at the web site data in a different “browser:” Excel. Now that you have the data, you can use it in formulas to get the information you need.
To update your information, right click and choose Refresh, which will be at the bottom of the right click menu. Or, set up an automatic refresh by selecting Data Range Properties instead, and adjusting the Refresh Control settings.
Possible roadblocks: You may find websites that do not allow you to download the data or that your Network Administrators have blocked for one reason or another. The rules about connecting to websites will be the same as if you were browsing through your regular browser.
Join us 2/1/2011 for our Advanced Excel webinar and learn more great tools you already have at your fingertips.