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

Importing XML data files into Excel allows you to integrate structured data from various sources into your spreadsheets to take advantage of Excel’s research and visual features while dealing with XML-based data. The main advantage to importing XML data is to enable analyzing data from diverse sources in a user-friendly environment.

In this article, we will discuss how to import XML data files into Excel in various ways, including using the Data tab, a web URL, the Developer tab with the Source and Import features, and importing multiple XML data files.

XML data files will look similar to the image below after importing them into Excel.

Overview of how to import XML data file into Excel

Download Practice Workbook

What Is an XML Data File?

An XML (Extensible Markup Language) data file 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

Suppose we have a text file consisting of some Book Names, Authors, and Published Years saved as XML data.

To import this data into Excel, we need to convert the text document to an XML data file.


  • In the File Explorer, select the file to be imported.
  • Right click to open the Advanced context menu.
  • From the menu click Open.

Clicking the Open option to open the .txt file

The data is already stored in XML format.

Sample text file containing XML data

  • Click the File menu.
  • Select the Save As option.

Clicking Save As option from the File tab

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

Changing the file format to XML and hitting Save

As a result, we now have an XML data file. Let’s import it into our spreadsheet.

Sample data created by converting .txt documents into .xml

Read More: How to Open XML File in Excel

Method 1 – Using Data Tab


  • Open a workbook.
  • Go to the Data tab.
  • From the Get Data list, click the From File option and select From XML.

Clicking the From XML option from the Data tab

  • Select the XML file and click Import.

Selecting the XML file from the device storage

The Power Query window will open.

  • Click the book tab.
  • Click Load to to import the values to the desired location in the worksheet.

Clicking the book tab and hitting Load to option

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

Selecting a new location inside the worksheet to import data

We have successfully imported the XML data file into Excel.

Final result with importing XML data file into Excel

  • To edit the imported data, click the Transform Data option from the Navigator window.

Clicking the Transform Data feature from the Navigator window

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


Method 2 – Using Web URL

While exploring multiple sites, you won’t be able to download the XML file directly. But you can import XML data using the URL link. Here, we will import XML data from Forbes.


  • Click this link to access the data.
  • Select the full site link and copy it.

Selecting the URL and copying it

  • Open your workbook and go to the Data tab.
  • Open the Get Data list, and from the From Other Sources list choose From Web.

Selecting From Web option by visiting the Developer tab

From Web dialog box opens

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

Pasting the URL and hitting OK

  • An Access Web Content dialog box opens
  • Click the Connect button to continue.

Clicking Connect from the Access Web content window

  • 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

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

Method 3. Using Developer Tab

The Developer tab in Excel is primarily used for more advanced tasks related to Excel customization, automation, and macro development. We can use it to import XML data files using 2 different techniques.

3.1 – Using Source Option


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

Clicking the Source option from the Developer tab

  • From the right pane, click XML Maps.

Clicking the XML Maps from the right pane

  • Inside the XML Maps window, click the Add button.

Pressing the Add option from the XML Maps window

  • Choose your XML file and click Open.

Selecting the XML file and hitting Open

The file is added in the XML Maps.

  • Close the window by clicking 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.

  • To import the data, select any cell in the source table.
  •  Right click to open the Advanced options.
  • Choose Import from the XML option.

Selecting the Pivot table and inserting XML file by pressing Import

  • Select the XML file and click Open.

Selecting the XML file and hitting Open

The XML data file is imported into the spreadsheet.

Final output with importing XML file inside the spreadsheet

3.2 Using Import Feature

We can import data directly utilizing the Import feature. The XML file will need to be added in the XML Maps window first.


  • Go to 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

The XML file is imported into Excel.

Final result with importing the XML file

How to Import Multiple XML Files into Excel

Similarly, we can also import multiple XML files into Excel. Suppose we have 2 XML files containing a Book List in a folder. Let’s import both files into a workbook.

Sample dataset with 2 XML files inside a folder


  • Go to the Developer tab, and click XML then Import.

Clicking the Import option from the Developer tab

  • Select both files while pressing the SHIFT key.
  • With both files selected, click Import.

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

  • Both files will be imported into the spreadsheet.

Final result with importing multiple XML files into Excel

Things to Remember

  • While importing XML files ensure that the xml code is valid, 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, 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. 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.

Import XML to Excel: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!
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