How to Convert XML to Excel Table (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes you may need to convert XML to an Excel table. In this article, I will explain how to convert XML to an Excel table using Microsoft 365 version.


What Is an XML File?

An XML file can store data in a readable format for different applications and systems. Basically, XML stands for Extensible Markup Language. Actually, users can’t read the XML file easily. That is why it needs to be extracted into another format. Furthermore, the XML file is mainly used for storing and transporting data. It is similar to the HTML format, but the tags are not predefined.

However, you can easily open the XML file with a text editor if it contains text data and make the necessary changes. On the other hand, if it contains both text and numerical data, then you can extract the XML file to Excel and read it easily.


Here, I will describe the 3 suitable methods for how to convert XML to an Excel table. Also, for your better understanding, I’m going to use two XML files. One is given below.

XML file to Convert it to Excel Table


1. Using Data Tab to Convert XML to Excel Table

Here, you can use the Data tab to convert XML to an Excel table. Furthermore, you can use 3 different ways under this Data tab. You may use this method when your XML file is saved on your device. Now, let’s talk about the steps.

Steps:

  • Firstly, from the Data tab >> go to the Get Data menu.
  • Secondly, from the From File feature >> select From XML option.

Use of Data Tab to Convert XML to Excel Table

At this time, you will get a new window named Import Data.

  • Now, select your XML file.
  • Then, click on Import.

As a result, you will see the window named Navigator.

  • Here, select the record file.
  • Then, click on Load.

Subsequently, you will see the following result. If you notice, then you will see the Dates are in General format.

Result of the converted Excel table

  • Now, select the cells C2:C8.
  • Then, choose Short Date from Number formatting which is in the Home tab.

So, your dates are now in Date format.

Here, if you want you can format your Excel table too.

Formatted result of how to Convert XML to Excel Table

Read More: How to Convert Large XML to Excel


2. Employing Web URL to Convert XML to Excel Table

Now, I will employ the web URL to convert XML to an Excel table. You may use this method when you have the online XML file. Now, let’s talk about the steps.

Steps:

  • Firstly, from the Data tab >> go to the Get Data menu.
  • Secondly, from the From Other Sources feature >> select From Web option.

Employing Web URL to Convert XML to Excel Table

At this time, a new dialog box named From Web will appear.

  • Now, choose Basic.
  • Then, paste your file link to the URL box.
  • Here, I used this XML File link.
  • Subsequently, press OK.

As a result, you will see the window named Navigator.

  • Here, select the url file.
  • Then, click on Load.

Lastly, you will get the converted Excel table.

Read More: How to Convert XML to Columns In Excel


3. Using Import Feature from Developer Tab in Excel

However, you can use the Developer tab to convert the XML file to an Excel table. Let’s see the steps.

Steps:

  • First, from the Developer tab >> choose the Import option.

Using Import Feature from Developer Tab in Excel

At this time, a new window named Import XML will appear.

  • Firstly, choose your XML file in the File name box.
  • Secondly, click on Import.

After that, you may see the following dialog box named Error In XML.

  • If you see this, then press OK.

Subsequently, another dialog box named Import Data will appear.

  • Here, I chose XML table in existing worksheet: including the position.
  •  Then, press OK.

Finally, you will see the following data table. Here, the Dates are in General format. So, I’ll change this formatting to Date.

The following is the final format of my converted Excel table.

Formatted result of the converted xml file to Excel table

Read More: How to Convert XML to XLSX Without Opening File


Download Practice Files

You can download the practice workbook from here:


Conclusion

I hope you found this article helpful. Here, I have explained 3 suitable methods to convert XML to an Excel Table. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

<< Go Back to Import XML to Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo