How to Enter Web Data into Microsoft Excel

If the numbers you need to crunch come from the Web, then you’ll need an efficient way to get it into Microsoft Excel. There are several ways you can do this, and one method even allows for automatic updating, which means that the Excel worksheet will reflect the data changes and keep your small business database up-to-date.

In this column, I’ll show you these three ways to move data from the Web into an Excel spreadsheet without typing it yourself.

Drag and Drop Web Data to Excel

As small business software goes, Excel is a great productivity tool. It lets you analyze data very efficiently. Of course, the first step is to get the data into Excel, and the simplest way to move data from the Web to Excel is to copy and paste it.

Select the Web data in your browser, copy it from the browser, and paste it into Excel. Alternately you can try dragging and dropping the table from the browser into the worksheet. To drag-and-drop the data, you need to see both the browser window and the Excel sheet.

In some cases, selecting a small amount of additional data above and below the table of data you want actually works better than trying to select the exact data in the Web page. Once you have the data in Excel, simply delete the unwanted extra cells or rows of data. Sometimes one method will work and the other will not — it’s worth trying both in these circumstances.

The benefit of copying and pasting data is that typically the data will appear inside Excel as it did on the Web. You may need to format it, but the data will be intact and useable. The downside is that the Excel worksheet data is static, and it will not update if the Web data changes; you will need to open the Web page again and copy the new, changed data.

Excel; small business software

Figure 1: You can copy data from the Web to an Excel worksheet or, in some cases, drag-and-drop it from a browser window into the worksheet.

Get External Data

The second method of grabbing data from the Web involves Excel’s Get External Data feature. This method is less reliable because not all websites support it, but it will let you import live data, which means the data can be automatically updated on a regular basis. This ensures that you always work with the most recent data.

From inside Microsoft Excel, choose Data > From Web, and then wait until the New Web Query dialog opens. This dialog contains a mini-browser, so click in the Address box and type the URL of the website you want to get data from. For example, to get the current U.S. stock price, type http://www.money.cnn.com/data/markets.

When the website opens in the dialog box, you will see a number of small yellow and black arrow markers scattered around the page. These yellow markers indicate selectable tables that you can add to Excel. To add a table, hold your mouse over the marker to see the outline of the table, and click to select it. A selected table will have a green marker in place of a yellow one.

Excel; small business software

Figure 2: Select the tables you want to import into Excel from the website in the dialog box.

When you have selected the tables that you want to import into Excel, click Import. When the Import Data dialog appears, click Properties. You can now name this query and set some of its properties such as how often you want to refresh the data. To determine the refresh rate, change the value in the Refresh Every box.

You can also specify what should happen if the number of rows in the data range changes upon refreshing the data. When you have configured your selections, click OK to close the External Data Range Properties dialog.

Next, click to set the top-left cell for the imported data. Note: make sure you have empty cells below and to the right of the top-left cell for the imported data. Click OK and wait as Excel imports the data from the website.

Excel; small business software

Figure 3: Set the options for your query. Include a name and a time frame for updating its data.

The data will be updated on a regular basis according to the frequency that you selected for the update.

Predefined Queries

There is one final way to move Web data into Excel, and that is using a preset Web query. Here’s how it works. Click to view a new sheet and choose Data > Existing Connections, and then click Browse for More. This will take you to the …Office14Queries folder where you will find some existing .iqy query files for various MSN Money Central sites relating to Investor Currency Rates, Investor Major Indices and Stock Quotes. Select the Stock Quotes query and click Open.

Select the top left cell for the imported data and click OK. You will be prompted to enter the symbols of the stock to view, so do this and separate multiple symbols with commas. For example, type the following to view data for AOL, Google and News Corp:

Aol, goog, nws

Before you click OK, decide whether you always want to check these stocks or if you want the flexibility to enter any stock symbol. If you always want to check just the named stock, enable the Use this value/reference for future refreshes checkbox, and you won’t have to enter the stock symbols each time. Click OK to continue. The requested data will be imported and displayed in the worksheet.

You set the properties for this query by right-clicking on the imported data area and choosing Data Range Properties. Here you can set an update frequency and other options.

The .iqy files are preset query files that you use to extract data from websites. These simple text files contain three or more lines of instructions. Some are shipped with Excel, and knowledgeable Web programmers can write their own.

Excel; small business software

Figure 4: An .iqy Web query file is a simple text file containing details of the query. The one pictured here fetches stock data from MSN Money Central.

Depending on your needs and the configuration of each website, one of these methods should let you grab Web data to use in Excel without having to type it in yourself.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!

Must Read

Get the Free Newsletter!

Subscribe to Daily Tech Insider for top news, trends, and analysis.