Using Web Pages with Excel

Set up Data in Web Pages for Importing into Excel Spreadsheets

One interesting feature of Excel is the ability to import Web pages. This means that if you have data on a Web site, it's very easy to convert it to an Excel spreadsheet, if your Web page is properly set up. This gives you the analysis tools in Excel available to your Web pages. It also allows you to import data from other Web pages into your spreadsheets.

How to Set Up Your Site

As Excel is a spreadsheet, it is made up of boxes or tables.

Thus, if you're going to import data from a Web page into Excel, the best format is as a table. You can tell your spreadsheet to import every table on a Web page, just specific tables, or even all the text on the page.

Name Your Tables

Excel allows you to import tables by number (where you would simply count the number of tables on the page) or by name. However, to import a table by name, you need to name your tables. But you don't use the "name" attribute, instead, you use the "id" attribute. Make sure that every table has a unique id.
<table id="table_name">

How to Import the Data

Once you have the Web page set up with your tables identified, you need to import the data into Excel.

  1. Open Excel.
  2. Click in the cell where you'd like your data to start.
  3. Click on the Data menu and choose "Get External Data".
  4. Choose "New Web Query".
  5. In the address box, type the URL of the Web page where your data is found.
  1. Click in the "One or more specific tables on the page".
  2. Type in the id of the table you want to import. If you're importing from a page where the tables aren't labled, you'll need to count the number on the page.
    Be careful of formatting. If a page uses tables for layout, this can impact the numbering of the tables - and Excel might import more than you expect.
  1. Choose the amount of formatting you would like:
    • None - the data will be imported with no formatting
    • Rich Text Formatting - the data will be imported with formatting like bold, italics, and background colors
    • Full HTML Formatting - the data will be imported as close to the existing Web page as Excel can render
  2. Click OK.

Practice with This Page
Note that there are three tables on the page, two are named, and one is used for formatting. The named ones are:

  • basic
  • table_two

Learning how to create Excel friendly pages and how to import them into Excel will give you the analyzing power you need to create more complex and useful Web sites for your clients and customers.