Excel on the web: use Excel's built-in web browser to retrieve online data.

• Author: Lenning, Jeff

Did you know Excel has 3as 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 OMITTED]

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. If you want to play along, you can download the sample workbook referenced in the screenshots from www.elickconsulting.com/tbrg2012. 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.

[FIGURE 1 OMITTED]

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).

[FIGURE 2 OMITTED]

The data flows into Excel in a structured manner...