Import an HTML Table into 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

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.

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

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.