Analyze Data Tables from the Web Using Microsoft Excel

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

Import Data from a Web Page

Excel is a spreadsheet application optimized for evaluating information in a two-dimensional grid. If you're going to import data from a web page into Excel, the best format is as a table. Excel imports every table on a web page, just specific tables, or even all the text on the page.

Import the Data (Excel for PC)

After you've identified the website that contains the information you require, you can import the data directly into Excel using the From Web tool with only a few clicks, customizing import options along the way.

Here's how to import a data table from the web on a PC:

1. Open Excel.

2. Select the Data tab and choose From Web in the Get & Transform Data group. The From Web dialog box will open.

3. Select Basic, type or paste the URL in the box, and select OK. If prompted, choose to Connect to the website.

4. In the Navigator box, select the tables to import. Excel isolates content blocks (text, tables, and graphics) if it knows how to parse them. To import more than one data asset, place a check mark next to Select multiple items.

5. After you select a table, a preview appears on the right side of the box. If it's the table you want, select Load. The table appears in a new worksheet.

6. The right side of the screen displays the Queries & Connections pane. If you've imported multiple tables, select a table from the Queries & Connections pane to view it.

Edit Data Before Importing It

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

In the Navigator box, select Transform Data instead of Load. Excel loads the table into the Query Editor instead of the spreadsheet. This tool opens the table in a specialized box that allows you to:

• Manage the query
• Choose or remove columns and rows in the table
• Sort data
• Split columns
• Group and replace values
• Combine the table with other data sources
• Adjust the parameters of the table

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

Import the Data (Excel for Mac)

You cannot import data from a website into Excel for Mac. You'll need to save the website to your computer using your web browser's Save As function. After you've saved the website, import the page's HTML data into an Excel spreadsheet with the following method:

1. Open Excel.

2. Select Data > From HTML.

3. Navigate to the location of your saved HTML web page, select it, and select Open.

4. Excel automatically imports the entire web page into a new workbook. From here, clean up the chart to only include the needed data.

While the From HTML method for Mac isn't as clean or controlled as the From Web option for PC, it still allows data from a web page to be imported into an Excel spreadsheet.