Import and convert Web text to columns in an Excel worksheet

Armed with a Web query and some conversion options, you can bring Web table data into a worksheet in a format you can use. This example walks through the process.

Excel makes it easy to extract table data from a Web page — but to be of use in a worksheet, you will need to convert the text tables into columns. For example, say you need to chart U.S. GDP data from 2002 through 2006. First, you would use a Web query to import the data from the Web. Then, you'd extract the data into two separate columns. Follow these steps:

  1. Open a blank worksheet.
  2. Go to Data | Import External Data and then click New Web Query. In Excel 2007, click the Data tab, click Get External Data, and then click From Web.
  3. Enter the Web page URL (Figure A).

Figure A


  1. Scroll to Gross Domestic Product.
  2. Click the arrow next to the Gross National Product Table. (If there are no arrows next to tables on the page, click Show Icons twice at the top of the dialog box to display them.)
  3. Click the Import button (Figure B).

Figure B


  1. Click Existing Worksheet, if necessary.
  2. Click the cell on the worksheet where you want the upper-left corner of the Web data to appear and click OK.
  3. Select the data cells containing the text you want to convert.
  4. Go to Data | Text To Columns. (In Excel 2007, click Text To Column in the Data Tools Group on the Data tab.)
  5. Select Delimited (Figure C).

Figure C


  1. Click Next.
  2. Click to select the Space check box under Delimiters (Figure D).

Figure D

finish conversion

  1. Click Next.
  2. Click Finish.

The data from the Web is now ready to chart (Figure E).

Figure E

chartable data

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks