How to Convert Excel to XML File (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Similar to HTML, XML is also a markup language. But unlike HTML, it is designed to store and transport data. Excel users often need to store their data in XML format. This allows them to store their data on the web and make it accessible to other users. In this article, we will discuss how to convert an Excel dataset to XML file.


Download the Practice Workbook


Step-by-Step Procedures to Convert Excel to XML File

In this discussion, we will learn how to convert an Excel dataset to XML in detail. We will start by creating an Excel dataset and finally export the dataset as XML. We will use the sample dataset below to illustrate the process.

Sample Data


Step 1: Creating Dataset

  • To begin with, we will create a dataset in an Excel spreadsheet.
  • Then, we will convert this sheet to an XML file.

Step-by-Step Procedures to Convert Excel to XML File


Step 2: Saving XML Schema

  • Secondly, we will write our XML schema in a text editor.
  • In this case, we will write it in Notepad.

Step-by-Step Procedures to Convert Excel to XML File

  • Then, go to the File tab of Notepad.
  • Select the Save As option.

Step-by-Step Procedures to Convert Excel to XML File

  • Once that is done, write the file name with an .xml extension.
  • Then, select Save.

Step-by-Step Procedures to Convert Excel to XML File


Step 3: Opening XML Source Panel

  • Thirdly, in the Excel worksheet, we need to open the XML Source panel.
  • To do that, first go to the Developer tab.
  • After that, select the Source command from the XML option.

Step-by-Step Procedures to Convert Excel to XML File

  • As a result, the right side of the Excel worksheet will now display the XML Source panel.

Step-by-Step Procedures to Convert Excel to XML File


Step 4: Adding XML Map

  • Then, from the XML Source panel, click on the XML Maps tab.

Step-by-Step Procedures to Convert Excel to XML File

  • Consequently, the XML Maps dialogue box will appear on the screen.
  • From the dialogue box select Add.

Step-by-Step Procedures to Convert Excel to XML File

  • Afterward, we will add the XML file that we have created previously.
  • Finally, click OK.

Step-by-Step Procedures to Convert Excel to XML File

  • Consequently, we will find that the elements of our dataset will appear in the XML Source Panel.

Step-by-Step Procedures to Convert Excel to XML File


Step 5: Incorporating XML Elements to Dataset

  • Finally, we will incorporate the XML elements into our dataset.
  • To do that, first select the first row header in your dataset.
  • In this case, that is “Name”.
  • Then, select the element having the same name from the XML Source panel.

Step-by-Step Procedures to Convert Excel to XML File

  • As a result, you will see that the selected row header and the dataset under it have turned into a table.

Step-by-Step Procedures to Convert Excel to XML File

  • Then, do the same for the rest of the row headers.
  • In our case, we have another row header named “Sales”.

Step-by-Step Procedures to Convert Excel to XML File

  • Finally, you will find that all the entries from your dataset will be turned into a table.

Sample Data


Step 6: Exporting XML Data

  • Finally, we will export the Excel dataset as an XML file.
  • To do that, first go to the Developer tab.
  • Then, from there select the Export option.

Sample Data

  • You will notice that the file already has an .xml extension.
  • Finally, click on Export.

Sample Data

  • Then, open the XML file.
  • You will see that the existing data set will be turned into XML.

Sample Data


Conclusion

XML allows Excel users to store data on the web, thus exposing the data to the customers on the web to access. After going through this article, the readers will have a clear understanding of how to convert Excel to XML. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo