Excel on the Web

Did you know Excel has a built-in web browser with a special feature other browsers don’t? The Excel browser visually identifies and allows you to select web tables, and then import them into Excel in a structured way. The best thing is that once you have imported a table, you can refresh the data range at any time from within Excel by clicking the refresh button. So, job tasks that begin with online data may be enhanced with this handy Excel feature. Ready to uncover the details?

Illustration

A client recently asked me to help with an Excel project that required a foreign currency translation between U.S. dollars and Euros. I configured the workbook to automatically retrieve the most current exchange rate from the internet. I’ll demonstrate Excel’s External Data feature by using this recent project as an illustration.

Note: the screenshots and navigation assumes Excel 2007 or Excel 2010.

Walkthrough

To launch Excel’s web browser, click the Data > From Web icon in the Ribbon. Once the browser is open, navigate to the website that has the data you want to retrieve. In this case it’s www.google.com/finance. You’ll notice that Excel’s browser identifies the importable data regions by placing a yellow arrow into the webpage near the upper left corner of each data table. When you hover over a yellow arrow, the browser places a border around the table, making it easy to see which content is included in the table. Once you decide which table you want to import into Excel, click the yellow arrow.

In the case of the foreign currency translation workbook, I needed to import the current exchange rate, so I scrolled down the Google Finance webpage and selected the Currencies web table (Figure 1). You’ll notice that Excel’s browser indicates the selected table by shading it and changing the yellow arrow to a green check.

Once you have selected the data table you want to pull into Excel, click the Import button in the web browser. The Import Data dialog will be shown, allowing you to specify where in Excel you’d like the data. You can specify the sheet and cell reference.

Once you click “OK” on the Import Data dialog box, Excel pulls the data from the webpage right into your Excel worksheet as shown (Figure 2).

The data flows into Excel in a structured manner, which means the data is imported into individual cells and split properly into rows and columns. This makes it easy to use the data in formulas.

Once the table has been imported, it is easy to ask Excel to go back to the webpage and retrieve the current data. Just click the Refresh button (Data > Refresh or via right click on the table).

Finishing out our illustration, we can apply the imported exchange rate to the U.S. dollar prices to generate accurate and current pricing in Euros (Figure 3).

There is an ever-growing pool of data online that we can use in our Excel workbooks that is easy and quick to retrieve with this External Data feature of Excel.

Want to explore some additional details of this feature? Me, too!

Taking Refresh to the Next Level

In addition to manually refreshing the data by clicking on the Refresh button, you can ask Excel to automatically retrieve the current web data each time the workbook is opened.

To do so, open the external data range properties dialog box (Figure 4) by right clicking anywhere within the imported data region and selecting “Data Range Properties” from the shortcut menu. Check “Refresh data when opening the file,” then “OK.” Now, each time this workbook is opened, Excel will automatically refresh the data range with the current web page data.

Internet Explorer

In addition to the method above to implement the External Data range feature to pull web data, Internet Explorer users can right-click a web table, and if the web table is importable, “Export to Microsoft Excel” can be selected from the shortcut menu.

Importable Data

Let’s clarify what types of data this feature can retrieve. This feature doesn’t pull “files” into Excel, it only retrieves data that appears in the content of a webpage. For example, your bank website may offer the ability to download a PDF or CSV file of your bank statement or banking activity. These are both “files” and thus can’t be imported using the External Data range feature of Excel.

Additionally, this feature only retrieves data presented in a table format. The choice to include data in a table or not is controlled by the web developer, and thus, outside of our control. If a webpage does not present the needed data in an importable table format, hopefully another webpage will.

Compatible Websites

The External Data feature of Excel can access data from many, but not all, websites. For purposes of this conversation, let’s categorize all webpages into two types: public and secure. A public webpage can be accessed by anyone. A secure webpage can only be accessed once a user has logged in with a username and password.

This Excel feature works well with public webpages, but its success with secure webpages depends on how the web developer set up the security. Generally speaking, you can login to the site from within the Excel browser and probably be successful pulling downthe data the first time. However, the sites that have data in a secure area may or may not be refreshable, depending on the security settings. For example, I was easily able to navigate to wellsfargo.com, login and download my bank account summary data. I was able to click refresh within Excel and successfully update the retrieved data. However, after a few minutes (when my session expired) the refresh button no longer worked. So, some experimenting may be necessary to determine if the webpages you want to import work well with this feature.

Structure Changes

If the webpage you are pulling from changes its format or structure, you may need to update your Excel workbook by re-importing the data.

Conclusion

I constantly learn new things about Excel and it seems that there are many manual tasks we perform that can be delegated to this remarkable program. Hopefully, if you work with web data, this feature will allow you to get your work done just a little faster.

And remember, Excel rules!