Import web table into excel

Table of Contents Show

Step 1: Open File or URL in Query Editor
Step 2: Cleanup in Query Editor
Post navigation
How to Import External data from the web
101 Reasons to Import Data from a Website
How do I import a table from a website into a spreadsheet?
How do I copy a table from HTML to Excel?
Can you link website data to Excel?

In this article, I will show you how to import an HTML table into Excel. This will work for any data from a local HTML file or online URL that is stored in a table. I will import the HTML table in a way that the data can be updated if it changes, which is particularly valuable for online data. This will be done using a Get & Transform Query. As such, this method is only compatible with the Windows version of Microsoft Excel.

Step 1: Open File or URL in Query Editor

Table to import in Excel

In this article, I will be using the NYC Boroughs table on the following Wikipedia page: https://en.wikipedia.org/wiki/New_York_City. You could also use a local file.

To import data from the web, on the Data tab click Get Data > From Other Sources, From Web. If you have a local file, choose Get Data > From file > From XML instead.

Find the table you need in the Navigator

Once you open the file or URL, the Navigator will open. This shows all of the tables in the file. You may need to browse through the tables to find the one you need. Once you do, if everything looks exactly as you want it to appear in Excel, you can click Load. Otherwise, click Transform Data to clean up the table.

Step 2: Cleanup in Query Editor

Finished table in Query Editor

Once in the query editor, the steps will be unique for every table you desire to import. In this case, I needed to remove the left-most column, as it was filled with the name of a header. I also removed the first tow rows. Use the ribbon interface at the top to make changes. If you make a mistake, remove the step in the Applied Steps section.

Once the table is ready for Excel, click Close & Load.

Just like that, the table is imported! If you used a URL, you may wish to enable auto-update of the data. Click a cell in the table, go to the Query tab, click Properties. You will find options in the Properties dialog for background refresh and auto-update.

Thank you for reading. If you have any questions, please comment below.

How to import data from the web in Excel: If you manually copy and paste data from a web page into Excel, you might find that all the data gets pasted into one cell.

That's annoying, and requires more copying and pasting.

So, use the web query tool to save time, and make your worksheets more dynamic!

You can use web query to get refreshable (or static) data from your company's intranet or the internet, like (single or multiple) tables, or all the text on a webpage. Here's How

How to Import External data from the web

Just click File > Import External Data > Create New Web Query

Copy the URL of the web page you want to display and paste it into the Address box.

That should be it! Excel will import table data directly, and cleanly into your worksheet. (Web query won't retrieve any photos or graphics)

Using Internet Explorer?

If you use Internet Explorer, you can also import data to excel directly from your web browser.

Just right click on the webpage, and then select "Export to Microsoft Excel" on the shortcut menu.

This should display the New Web Query dialog box in Excel.

*Web browsers can change all the time, so personally, I prefer method one.

101 Reasons to Import Data from a Website

Incorporating data from the web with this feature not only saves time, but it can make your worksheets more dynamic.

The next time you find a table of useful data online, try incorporating it into an Excel worksheet with the web query tool!

How do I import a table from a website into a spreadsheet?

How to Import an HTML Table into Excel or Google Sheets.

Open the Excel file that you wish to import the data into..

In the Ribbon, select Data > Get & Transform Data > From Web..

Type in the URL from which you wish to fetch the data and click OK..

How do I copy a table from HTML to Excel?

Each table on your web page will be indicated by an arrow, as shown below. Click the arrow next to the table you want to import, which will turn it into a check mark. Click the Import button, and the Import Data box pops up. Indicate where you'd like to import your HTML table, then click OK.

Can you link website data to Excel?

Create a link to a web page On a worksheet, select the cell where you want to create a link. On the Insert tab, select Hyperlink. You can also right-click the cell and then select Hyperlink... on the shortcut menu, or you can press Ctrl+K. Under Display Text:, type the text that you want to use to represent the link.