XML in Excel

  • Post author:
  • Post category:Uncategorized

XML (eXtensible Markup Language) is a markup language designed to store and transport data.  This article will give us an overview on how to import XML in Excel and how to convert XLS to XML.  

Figure 1. Final result:  Import XML in Excel

Import XML in Excel

We can import XML data or open an XML file by following these steps:

  • Activate the Developer tab by clicking File Excel Options > Popular tab
  • Select the check box for Show Developer tab in the Ribbon

Figure 2. Show Developer tab option

  • Now that the Developer tab is displayed in the ribbon, click the Import button

Figure 3. Import button in Developer tab

  • The Import XML dialog box will appear.  Browse for the XML file we want to import and click Import.  

Figure 4. Import XML dialog box

  • We will be asked where to put the data

Figure 5. Import Data dialog box

  • Click OK.  The XML data will then be imported into the cells.

Figure 6. Output:  Import XML in Excel

Export Excel to XML

After importing and editing the XML data, we might want to convert XLS to XML.  Here are the steps:

  • On the Developer tab in the ribbon, click Source
  • The XML Source dialog box will appear at the side

Figure 7. XML Source dialog box

  • Click XML Maps and choose the map we want to use

Figure 8. XML Maps dialog box

  • In the Developer tab, click Export.

Figure 9. Export option in Developer tab

We have now successfully exported XLS to XML.  

Save Excel as XML

In order to save an Excel file as XML, we follow these steps:

  • Click File Save As or press F12
  • In the Save As dialog box, enter the file name in the text box and click the arrow for Save as type
  • Choose XML data (*.xml)  

Figure 10. Save As diaog box

  • Click Save.  

The Excel file will be saved as an XML document as shown below.

Figure 11. Output:  Save Excel as XML