How to Perform XML Mapping in Excel?

Get FREE Advanced Excel Exercises with Solutions!

In this Excel tutorial, you will learn how to
– Perform mapping in Excel
– Import XML data after mapping
– Remove the XML map

We have used Microsoft 365 to prepare this article. But you can use these methods in Excel versions from Excel 2003 onwards.

We use XML mapping to set up a connection between XML data and the cells within an Excel worksheet. It helps us to handle structured data, automated data as well as bulk data.

We need to have an XML schema file (.xsd) or an XML data file (.xml) to create an XML map in Excel. You can use XML schema or XML data files created from other applications to map in Excel. Otherwise, you can create one using Notepad or save the data as a text file.

XML Mapping in Excel

Download Practice Workbook

What Is XML Mapping in Excel?

XML mapping in Excel is the process that can transform an XML document into an Excel workbook. You may use the process if you are working with large or complex XML documents, or when you need to import data from an XML document into an Excel workbook.

An XML map represents the structure of an XML document. The XML map helps to link XML elements to cells in an Excel workbook. While importing an XML document into a workbook, the XML data is mapped to the cells that are associated with the XML elements.

How to Perform XML Mapping in Excel?

To perform XML mapping in Excel, you need to have XML data (.xml) or XML schema file (.xsd) and use the Developer tab. The XML data (.xml) file must contain the fields you want to map. You can follow the steps below to map XML in Excel easily.

Step-01: Prepare XML File

  • Initially, you need to open a Notepad and write the code of the following image in the notepad.

XML Code in XML File

Note: This XML data has 4 fields. They are: FirstNameAgeOccupation and Salary. You will find the XML data file in the ‘Download Practice Workbook‘ section.
  • After that, go to the File tab of the Notepad and click on Save.
  • We give the file’s name as “Create.xml” and save it.
  • If you already have an XML file, then you can use that.

Step-02: Map XML Data into Excel

  • Now, go to the Developer tab, and click on the Source ribbon. The XML Source task pane will open.
  • If you don’t find the Developer tab in the ribbon, you must enable it. You can follow this link to enable the Developer tab.

Open XML File in Excel

  • In the task pane, you will find the option “XML Maps”. Click on it.

Option to Open XML File

  • Right after that, the XML Maps dialog box will appear.
  • Click on the Add button. It will open the Select XML Source window.

Dialogue-Box to Choose XML File

  • Select the correct XML source file and click on Open.

Choose XML File Directory

  • After opening, you will get a warning message. Click OK to proceed.

Warning message while adding XML map

  • Now, select OK in the XML Maps window.

Adding XML map

  • As a result, the XML Source task pane will appear.

XML Source Task Pane

  • We will use the fields under the record section. We can call them map elements.
  • Now, select a field and drag them one by one in the worksheet, according to your needs. Click on the video below to see the process.
  • After dragging the fields or map elements, add new data to your worksheet.

Insert New Data After Mapping

Step-03: Export Excel File as XML

  • When you are done, select Export from the Developer tab.

Export XML file

  • Lastly, in the File name box, name your XML file and export it.

Name the Exported XML file

How to Import XML Data into Excel After Mapping?

To import XML data into Excel, you need to use the Developer tab in the ribbon. You may follow the steps below to import XML data to Excel.

  • First, go to the Developer tab and select Import.

Choose Options to Import XML Data

  • In the dialog box, select the XML source file.

XML File selection

  • Then, choose the position where you want to store your data. Here, we selected Cell B4.

Select Cell to Store Selection of Data

  • Just after you click “OK”, you will see the data in the worksheet.

Data Imported from XML File

How to Remove XML Mapping?

You can remove an XML map using the XML Maps window. When you are working with someone else’s workbook, you may need to remove the previous mapping. Follow the steps below to delete a map from your workbook.

  • First, click on the XML Maps command at the bottom of the XML Source dialog box.

Opening XML Maps Dialog Box

  • XML Maps dialog box will appear.
  • Now, click on Delete to remove XML mapping.

Selecting Option in the Dialog Box

  • A warning dialog box will appear that says “If you delete this XML map, you will no longer be able to import or export XML data using this XML map.”
  • Click OK to proceed.
  • Finally, check the XML Source pane. You will find that no XML mapping is available now.

XML Mapping Disappears

What Are Other Features of the XML Section in Excel?

There are some interesting features in the XML section that you can use in Excel. The first one is Map Properties.

1. XML Map Properties

The following image will give you an overall idea of the properties of an XML map.

Map Properties Dialog Box

While working with this box, you will have to be careful about the options under When refreshing or importing data. These options will determine how you are dealing with new data in your worksheet.

If your target is to alter the existing data with new data, you will choose the option “Overwrite existing data with new data”. However, if your goal is to add data to an existing XML table, you may use the option “Append new data to existing XML tables”.

2. Refresh XML Data

It is possible that you have brought some changes to the source data. But Excel does not update it automatically. Don’t be worried about this situation. You can solve this by using the Refresh Data option. Whenever you click on this option, Excel will automatically update the linked data.

Refresh Data Option in XML Group

Which Things Should You Keep in Mind?

  • Before working with XML mapping, ensure that you have a clear understanding of the XML structure.
  • Secondly, check the XML file you are working with is valid and well-formed.
  • Lastly, give special attention to the data types of the mapped XML elements. Excel interprets certain data types differently, such as dates, numbers, or text.

Frequently Asked Questions

1. What are the benefits of using XML mapping in Excel?

Answer: There are a lot of benefits if you use XML mapping. Some of them are easy importing and exporting of data; data synchronization; data analysis; and automation of the full process.

2. Can I edit the XML data directly in Excel after importing it using XML mapping?

Answer: Yes, you can edit the XML data directly in Excel after importing it using XML mapping. Excel treats the XML data as a structured table, and you can modify the content in the mapped cells. However, note that any changes made to the XML data in Excel will not be reflected in the original XML file unless you export the data back to XML.

3. Can I use XML mapping in Excel without the Developer tab?

Answer: No, XML mapping in Excel requires the Developer tab to be enabled. The Developer tab provides access to the XML mapping tools and features. If the Developer tab is not visible in Excel, you need to enable it by going to the File tab, selecting Options, and customizing the ribbon to show the Developer tab.


Hey! You have reached the end of the article on XML mapping in Excel. You need to use the Developer tab to perform XML mapping in Excel. There are some properties of an XML map that can be handy when you work with large data in Excel. After mapping, you can export the XML file for other uses. If you find this article helpful, please share it with your friends. Moreover, do let us know if you have any further queries.

XML Mapping in Excel: Knowledge Hub

<< Go Back to Export Excel to XML | Export Data from 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.

Md Junaed-Ar-Rahman, a Biomedical Engineering graduate from Bangladesh University of Engineering and Technology, has contributed to the ExcelDemy project for one year. As a technical content developer, he has authored 15+ unique articles and actively addressed user problems. He participated in 2 specialized training programs on VBA and Chart & Dashboard design in Excel. His passion lies in solving problems uniquely and exploring new functions and formulas. Eager for future exploration, he aims to gain proficiency in applications... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF