How to Import XML Data File into Excel (3 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

Importing XML data files into Excel allows you to integrate structured data from various sources into your spreadsheets. Utilizing this strategy, you may use Excel’s research and visual features to benefit yourself while dealing with XML-based data. The main advantage of importing XML data file is you can analyze data from diverse sources in a  user-friendly environment.

Today in this article, I will guide you on how to import XML data file into Excel including using data tab, web URL, developer tab with source and import feature, and importing multiple XML data file.

In the following, you will find an overview of how to import XML data file into Excel.

Overview of how to import XML data file into Excel

Download Practice Workbook

What Is an XML Data File?

An XML data file meaning Extensible Markup Language serves as a structured means of storing and organizing data. It’s a plain-text file format that employs a tag-based system, making it both human-readable and machine-readable. XML data files act as a universal language for data exchange that can communicate between different software systems and programming languages.

How to Import XML Data File into Excel: 3 Quick Methods

Below I am sharing 3 simple tricks to import data file into Excel. Suppose we have a text file consisting of some Book Names, Authors, and Published Years saved as XML data. First, we need to convert the text documents to XML data file so that we can import it into Excel.

  • Selecting the file right click the mouse button to open the Advanced option. From the Advanced options click Open.

Clicking the Open option to open the .txt file

  • As you can see, we have the data stored in XML format.

Sample text file containing XML data

  • Now, visiting the File option click the Save As option.

Clicking Save As option from the File tab

  • After the title of the file add “.xml” and click Save to store the file as an XML file.

Changing the file format to XML and hitting Save

  • As a result, we have the XML data file in our hands. Now let’s import the XML data file into our spreadsheet.

Sample data created by converting .txt documents into .xml

Read More: How to Open XML File in Excel

1. Using Data Tab

With proper utilization of the Data feature, you can sort and filter pivot tables and data validation for accurate values. Here we will import XML data inside a worksheet.

  • To do so, open a workbook, visit the Data tab, from the Get Datalist click the From File option and select From XML.

Clicking the From XML option from the Data tab

  • Next, choose the XML file and hit Import.

Selecting the XML file from the device storage

  • Immediately, the power query window will open from which we will click the book tab and hit the Load to option to navigate the values in a desired location of your worksheet.

Clicking the book tab and hitting Load to option

  • From the new Import Data tab, select Existing worksheet and choose a cell (B4) where the data will be inserted. If you want you can also import data as PivotTable Report, PivotChart, or Only Create Connection from the tab.

Selecting a new location inside the worksheet to import data

  • Finally, we have successfully imported XML data file into Excel. It’s that simple.

Final result with importing XML data file into Excel

  • If you want to edit the imported data then you need to click the Transform Data option from the Navigator window.

Clicking the Transform Data feature from the Navigator window

  • Immediately, the Power Query Editor will open where you can edit the data file to make changes to the data table.

Opening the Power Query Editor to edit the imported data


2. Using Web URL

While exploring multiple sites, you won’t be able to download the XML file directly. Well, you don’t need to be afraid of that. If you want you can import XML data using the URL link. Without moving here and there let’s get into it right now.

  • Here, we have imported XML data from Forbes. You can click this link to access the data.
  • Visiting the link select the full site link and copy it.

Selecting the URL and copying it

  • Next, open your workbook, visit the Data tab, open the Get Data list, and From Other Sources list choose From Web.

Selecting From Web option by visiting the Developer tab

  • In the URL section, paste the link and hit OK.

Pasting the URL and hitting OK

  • From the newly popped window, hit the Connect option to continue.

Clicking Connect from the Access Web content window

  • Hence, from the power query tool choose the tab from the left section and hit Load to import the data into a new worksheet.

Selecting the data from the left pane and hitting the Load option

  • In conclusion, we have successfully imported XML data into our spreadsheet.

Final result with importing XML data from URL

Read More: How to Extract Data from XML File to Excel

3. Using Developer Tab

The Developer tab in Excel is primarily used for more advanced tasks related to Excel customization, automation, and macro development. Here, we will use this developer feature to import XML data file. Lying inside the Developer tab, there are 2 techniques for importing data quickly.

3.1 Using Source Option

In this part, we will utilize the Source tab to import XML data stored in the device storage.

  • First, select the Developer tab, open the XML list, and press the Source option.

Clicking the Source option from the Developer tab

  • Now, from the right pane, click XML Maps.

Clicking the XML Maps from the right pane

  • Inside the XML Maps window, press the Add option.

Pressing the Add option from the XML Maps window

  • Thereafter, choose your XML file and click Open.

Selecting the XML file and hitting Open

  • Now, you will see the file is added in the XML Maps Close the window by hitting OK.

Clicking OK after adding the XML file

  • Coming back to the workbook, drag the file inside the worksheet.

Dragging the XML file inside the spreadsheet

  • As a result, the table consisting of data will be inserted inside the worksheet.
  • It’s time to import data. For this choose any cell from the source table open the Advanced options, and choose Import from the XML tab.

Selecting the Pivot table and inserting XML file by pressing Import

  • Hence, select the XML file and hit Open.

Selecting the XML file and hitting Open

  • Within a blink of an eye, the XML data file is imported into the spreadsheet.

Final output with importing XML file inside the spreadsheet

3.2 Using Import Feature

If you want, you can import data directly utilizing the Import feature. But you must keep in mind that the XML file needs to be added in the XML Maps window.

  • Simply, visit the Developer tab and click the Import option.

Selecting the Import feature from the Developer tab

  • Select the desired file and click Import.

Choosing the XML file and hitting Import

  • In conclusion, the XML file is imported into Excel. Simple isn’t it?

Final result with importing the XML file

How to Import Multiple XML Files into Excel

Similar to the previous methods, you can also import multiple files into Excel. Suppose we have a folder containing 2 XML files with Book List. Now, we will import both files into a workbook.

Sample dataset with 2 XML files inside a folder

  • Start by, visiting the Developer tab, and clicking the Import option lying inside the XML

Clicking the Import option from the Developer tab

  • Now, choose both files by pressing the SHIFT key.
  • After both the files are selected click Import.

Holding SHIFT key and selecting both the XML file and hitting Import

  • As a result, both files will be imported into the spreadsheet. It’s that simple.

Final result with importing multiple XML files into Excel

Things to Remember

  • While importing XML files you must check if it’s valid or not. Otherwise, errors in the XML structure can cause import issues. You may need to work with the data provider or validate the XML file before importing it.

Frequently Asked Questions

1. Can I customize the import process for XML data?

Yes, Excel provides options to customize the XML import process. You can map XML elements to specific Excel columns, set data types, and configure refresh settings to keep the data up-to-date.

2. What should I do if the XML file has a complex structure or namespaces?

Complex XML structures or namespaces may require more advanced handling. You can use Excel’s tools to manage namespaces and map elements to columns manually during the import process.

3. What if there are errors during the XML import process?

If errors occur during import, Excel will provide error messages that describe the issue. You may need to review the XML file’s structure, mapping, or data quality to address these errors.


In summary, the ability to import XML files into Excel is a useful tool that will enable you to easily include structured data from many sources into their spreadsheets. This method simplifies data management, improves analytical capabilities, and makes it possible to automate data updates. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.

Import XML to Excel: Knowledge Hub

<< Go Back to 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.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF