In this article, we will learn to import HTML into Excel. Excel is an excellent tool for managing data, and data comes in various formats. One of them is HTML format.
You can extract and work with web data directly in Excel by importing HTML data. This facilitates data analysis and enhances decision-making. By learning the process of importing HTML into Excel, you can gain new insights and improve productivity. This article will show 3 different ways to import HTML into Excel.
Download Practice Workbook
You can download the practice workbook here.
Import HTML into Excel: 3 Easy Ways
This article will demonstrate three easy ways of importing HTML into Excel from direct web pages and existing connections. So, Let us dig into the methods.
1. Use Data Tab to Import HTML from Existing Connection
In this method, I will show you how to import HTML from existing connections in Excel. You can follow this method if you already have an HTML file saved on your computer from which you want to import data in Excel.
- First, open an Excel file, go to the Data tab, and select Existing Connections from the Get & Transform Data group.
- Next, the Existing Connections box will appear.
- Select Browse for More from the box.
- After that, the window for selecting files will open.
- From there, search the HTML file, select it, and press the Open button.
- Here, I selected an HTML file named List of lakes by area which is saved from Wikipedia.
- Then, the New Web Query window will open, showing the contents of the HTML file.
- You will see a red arrow sign on a yellow box at the top left side of each table.
- Scroll the content, search for the table you want to import, and click on the arrow sign at the top left side.
- It will change into a tick mark, and then press the Import button.
- Next, the Import Data message box will appear.
- Select New worksheet if you need the table to import in a new sheet.
- Otherwise, press OK, inserting a cell reference in the Existing worksheet section.
- I inserted cell reference A1 in the existing worksheet.
- Finally, you can see the table imported in your Excel file.
- If you want to present it more clearly, add table styles or edit it according to your choice.
2. Import HTML from Web Directly Using Data Tab in Excel
Instead of saving an HTML file and using it for importing data, you can also import data from the web directly. This method is more convenient as the table style is added by default, and you do not need to save the HTML file before working. I will use the data in the image below to demonstrate the steps.
- First, select the link you want to import data and copy it by pressing Ctrl+C.
- Next, open an Excel file, select the Get Data from the Data tab, click From Other Sources, and select From Web.
- Or, you can directly select From Web from the Get & Transform Data group in the Data tab.
- After that, the From Web named box will appear.
- Paste the copied link by pressing Ctrl+V in the URL section and press OK.
- Next, a Navigator named window will appear where you can see all the available tables in HTML displayed on the right side.
- Select multiple items at once by checking the check box Select multiple items.
- So, choose a table or multiple tables and press Transform Table to open the table in Power Query Editor.
- You can also press the Load button to import the table immediately without editing it.
- The table opened in a Power Query Editor since I pressed the Transform Data button in the previous step.
- Press Use First Row as Headers if you want the first row as headers, and also, you can make changes,i.e., add or remove columns, etc., and press Close & Load to import the table in the worksheet.
- Finally, the table will be imported into an Excel file.
3. Apply Import Option to Import HTML into Excel (Only for Mac)
This method is only applicable for Excel versions Microsoft 365, Excel 2021, Excel 2019, and Excel 2016 for Mac. If you do not have these Excel versions for Mac, kindly follow the other two methods described above.
- To apply this method, select the Import option in the File tab.
- A dialog box named Import will appear. Choose the type of file you want to import and press the Import button.
- Next, the Choose a File named dialog box will appear.
- Select the HTML file you want to import and click Get Data.
- After that, specify the specifics to divide the text into columns and other formatting options in Text Import Wizard.
- Next, you can set query definition, data layout options, and refresh control by clicking the Properties option in the Import Data box and pressing OK after finishing.
- Then, you will be returned to the Import Data dialog box.
- You can choose the option of importing data to an Existing or New worksheet and press OK.
Frequently Asked Questions
1. How to open an HTML file in Microsoft Excel?
Open an Excel file, go to the File tab, select Open, click the Browse option, select the HTML file you want to open, and press Open.
2. How do I import a web page into Excel?
Select the From Web option from the Data tab, insert the URL, and click Connect to select and import web data.
3. How do I convert HTML to readable text?
Copy web page text by selecting and pressing Ctrl+C and paste it by pressing Ctrl+V into a text document.
I demonstrated three different methods of how to import HTML into Excel in this article. You can import HTML files from existing connections and the web directly using these methods. The third method is only applicable to Excel for Mac. All these methods will help you to incorporate online data into data analysis. Decision-making and productivity both benefit from this. However, data structure variations must be considered to ensure data integrity during import. Lastly, this skill is helpful for any data-driven task, including financial analysis and market research.