Using Web Pages With Excel

Use data from online tables inside of Microsoft Excel

Set up Data in Web Pages for Importing Into Excel Spreadsheets
ra-photos Getty

One little-known feature of Excel is its ability to import Web pages. This means that if you can access data on a website, it's easy to convert it to an Excel spreadsheet if the Web page is properly set up. This import capability helps you analyze Web data using Excel's familiar formulas and interfaces. 

Scraping Data

Excel is a spreadsheet application optimized for evaluating information in a two-dimensional grid. Thus, if you're going to import data from a webpage into Excel, the best format is as a table. Excel will import every table on a Web page, just specific tables, or even all the text on the page—although the less structured the data, the more that the resulting import will require restructuring before you can work with it.

Import the Data

After you have identified the website that contains the information you require, import the data into Excel.

  1. Open Excel.
  2. Click the Data tab and choose From Web in the Get & Transform Data group.
  3. In the dialog box, select Basic and type or paste the URL in the box. Click OK.
  4. In the Navigator box, select the tables you wish to import. Excel tries to isolate content blocks (text, tables, graphics) if it knows how to parse them. To import more than one data asset, ensure the box is checked for Select multiple items.
  5. Click a table to import from the Navigator box. A preview appears on the right side of the box. If it meets expectations, press the Load button.
  6. Excel loads the table into a new tab in the workbook.

Editing Data Before Import

If the dataset you want is very large or not formatted to your expectations, modify it in the Query Editor before you load the data from the website into Excel.

In the Navigator box, select Edit instead of Load. Excel will load the table into the Query Editor instead of the spreadsheet. This tool opens the table in a specialized box that lets you manage the query, choose or remove columns in the table, keep or remove rows from the table, sort, split columns, group and replace values, combine the table with other data sources and adjust the parameters of the table itself.

The Query Editor offers advanced functionality that's more akin to a database environment (like Microsoft Access) than the familiar spreadsheet tools of Excel. 

Working with Imported Data

After your Web data loads into Excel, you'll have access to the Query Tools ribbon. This new set of commands supports data-source editing (through the Query Editor), refreshing from the original data source, merging and appending with other queries in the workbook and sharing the scraped data with other Excel users.

Considerations

Excel supports scraping of text from websites, not just tables. This capability is useful when you need to import information that's usefully analyzed in spreadsheet form but isn't structured like tabular data—for example, address lists. Excel will do its best to import the Web data as-is, but the less structured the Web data, the more likely it is that you'll have to do a lot of formatting within Excel to prepare the data for analysis.