How to Convert XML to Excel? – 2 Easy Methods
If you regularly deal with databases in your day-to-day work, you may have already come across the XML file format. It is a widely used popular database format on the internet.
However, it cannot be directly accessed or used by humans like other formats such as PDFs, Excel sheets, and Word Docs, since it is mainly stored in a machine-readable format.
XML stands for “extensible markup language”, which is similar to HTML. But unlike HTML, it makes use of custom tags to store any information in a structured database. This gives it the powerful ability to easily store, search and retrieve data at the hit of a button.
But don’t worry. In this guide, we will see how to convert XML to Excel spreadsheets, the easy way.
You’ll learn:
- Method 1 – How to Convert XML to Excel?
- Method 2 – How to Import an XML File From the Internet into Excel?
Related:
The 7 Golden Rules of Excel Spreadsheet Design
How to Fix the #Div/0 Error in Excel? 2 Easy Methods
How to Fix the Excel Spill Error? — 5 Easy Fixes
Method 1 – How to Convert XML to Excel?
If you already have an XML file saved locally on your computer, you can follow these steps:
- Open any blank Excel sheet where you want to import the XML
- Go to the DATA tab and select the ‘Get Data’ button in the Get & Transform group.
- From the drop-down list click on From File > From XML.
- Now, locate the XML file locally saved on your computer and click on the Import button.
- Doing this will open the Navigator dialog box. Here once again click on the XML file you want to import and click on the Load button. Alternatively, you can click on the Transform button if you want to modify any columns in the XML file. This is completely optional.
That’s all folks. Excel will immediately import all the data from the XML file into a new sheet in the currently open workbook.
Please note that this import is dynamic in nature. That is, any changes made in the original XML file will reflect in the copied Excel file. All you need to do is, just right-click on any cell and click on the Refresh button from the right-click menu.
Also Read:
How to Apply the Accounting Number Format in Excel? (3 Best methods)
How to Autofit Excel Cells? 3 Best Methods
The FORMULATEXT Excel Function – 2 Best Examples
Method 2 – How to Import an XML File From the Internet into Excel?
In case you don’t have an XML file stored on your computer, but instead, have its URL from the internet, you can easily import it into Excel by following these steps:
- Open any blank Excel workbook and click on the Get Data button in the Get & Transform group of the Data tab. (Basically, repeat the steps 1 & 2 from the previous method)
- Under the Drop-down menu, click on From Other Sources > From Web
- Now, paste the URL of the XML file in the From Web dialog box and click OK.
- If prompted, press Connect. Doing so will take you to the Navigator window. Now, select the XML file and click Load to insert the XML file into the workbook.
Here also, any changes in the source XML file will reflect in the Excel file.
Please note that all imported data will be stored in the Table format.
Suggested Reads:
How to Group Worksheets in Excel? (In 3 Simple Steps)
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)
Closing Thoughts
In this guide, we saw how to convert XML to Excel using two easy methods. Many people are simply ignorant of these methods and end up using spammy online XML to Excel converters.
Just follow this guide, and enjoy a hassle-free XML to Excel conversion. Please do let us know if you have any questions as we are always happy to help.
Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!