How To Create Xml File From Excel

Excel spreadsheets are great for storing large amounts of data that would be unmanageable elsewhere. But what happens when you need to transfer that data somewhere else? What if you have hundreds or thousands of cells crammed with information? That would be a lot of data to manually re-type, especially if you need it in another format.

Extensible Markup Language, or XML, is an easy-to-use, read, and format document markup language. It can be used to store data in Excel files and help transfer them to other applications. It is a widely used format on the internet and in other applications.

In this guide, you'll learn a quick and easy way to convert Excel to XML. All you need is an Excel spreadsheet, an XML schema, and a rudimentary understanding of Excel, which you can learn in this Excel training course for beginners.

Step 1: Create your Excel spreadsheet

The first thing you need is an Excel spreadsheet. Below is an example spreadsheet with six columns and seven rows of data. The columns separate the last names, first names, ages, majors (subject), GPAs (Grade Point Average), and school year of each student listed in the spreadsheet.

Excel spreadsheet example, six columns, seven rows last names, first names, ages, majors (subject), GPAs (Grade Point Average), and school year of each student

Step 2: Create your XML schema

After you have your Excel document set up, you need an XML schema. An XML schema is the document that defines an XML file's structure. You can create an XML schema in Notepad, as it is a text file format. Here is a basic structure:

          <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <student-data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">     <record>        <TAGNAME>data</TAGNAME>        <TAGNAME2>data</TAGNAME2>        <TAGNAME3>data</TAGNAME3>     </record> </student-data>

The first line is a standard line. It identifies that it is an XML file, using the encoding UTF-8 and the XML version 1.0.

The second line identifies that the data is about "student-data". If you simplify this line and the last line, you have:

          <student-data> </student-data>

The first line above opens the element, and the last line, which includes a "/", ends this element. It is important that you have a closing tag for every opening tag, and that they are in the right order. Everything in between these lines is part of this element. In other words, it is "student-data". Because this is the first element, it is also called the "root element".

The "student-data" above includes a "record" element, which has three data elements, TAGNAME, TAGNAME2, and TAGNAME3.

You can change the name of the <TAGNAME> tags into the names of your columns. They do not have to be the exact name of the columns, as long as they're in the correct order. You only need to fill out a few examples manually, but we can get Excel to do the rest for us later.

Here's an example XML schema for our Excel spreadsheet:

          <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <student-data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">     <record>        <LastName>Takahashi</LastName>        <FirstName>Jesse</FirstName>        <Age>19</Age>        <Major>Literature</Major>        <GPA>3.8</GPA>        <Year>Freshman</Year>     </record>     <record>        <LastName>Nguyen</LastName>        <FirstName>May</FirstName>        <Age>23</Age>        <Major>Chemistry</Major>        <GPA>3.5</GPA>        <Year>Senior</Year>     </record> </student-data>

As you can see, data elements have become <LastName> and <FirstName>, in line with the columns in our data set. You do not need to have the lines indented, but that aids readability.

Also take note of the <student-data> tags that hold the contents of each <record> tag. It should be renamed according to the name of your Excel spreadsheet. In this example, our Excel workbook is called student-data.xlsx, and so our root element tag is called <student-data>.

Don't forget to name your XML schema with a .xml at the end. In this example, our schema is simply called schema.xml, if you'd like to follow along.

If you want to know more about XML documents, how they work, and what they're useful for, check out this introduction to XML course. You can also get started with this beginners XML tutorial class.

Step 3: Enable the Developer tab

If the Developer tab is not available, you need to enable it. Click on File – Options, and then the "Customize Ribbon" page. In the column on the right is a list of tabs called Main Tabs with check marks.

Make sure that the check box next to the Developer tab is checked, then click OK. The Developer tab should now be visible in the main Excel window.

If you're having trouble navigating Excel and its various options, it would be beneficial to take a basic Microsoft Office course for beginners and learn your way around the application.

If you want more advanced skills, then why not take advanced Excel training courses if you need experience with the more complicated elements of Excel.

Step 4: Open the XML Source pane

Now that you've brought up the Developer tab and clicked on it, you should see a number of sections called Code, Add-Ins, Controls, and XML.

In the XML section, click on the large button that reads Source. This will bring up the XML Source task pane to the right of the screen, where you'll have the option to manage XML maps. That's what we want to do next.

Step 5: Add an XML Map

In the XML Source task pane, click the XML Maps option at the very bottom right. A dialog box will appear. Click Add to add a new XML schema, find your schema.xml file in the browser, and click OK.

Now that your XML file is added, click OK again.

The computer may give you a warning – that's fine.

Click OK and return to the XML Source task pane.

Step 6: Drag and drop the XML elements into your spreadsheet

Each of your columns should appear as a new element in the record tree created by the XML Source task pane. Drag each element individually into its appropriate column, starting at Row 1.

Excel spreadsheet, xml source pane on the right

Converted columns become part of a table and should turn blue. Do this until all of your columns have been mapped to the XML elements.

Step 7: Export your XML data

Once you've finished converting each column into an XML element, you can either click on Developer – Export, or go to File – Save As, and change the File Type to "XML Data (*.xml)."

Save your XML file, and Excel will export it to XML. It may warn you that it will result in the loss of non-XML features, such as formatting, pictures, and objects. Again, that's fine.

Excel warning message, result in loss of data, continue

You can then open it in Notepad to see your results. Go to File – Open, and change the file type from "Text Files (*.txt)" to "All Files":

          <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <student-data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 	<record> 		<LastName>Takahashi</LastName> 		<FirstName>Jesse</FirstName> 		<Age>19</Age> 		<Major>Literature</Major> 		<GPA>3.8</GPA> 		<Year>Freshman</Year> 	</record> 	<record> 		<LastName>Nguyen</LastName> 		<FirstName>May</FirstName> 		<Age>23</Age> 		<Major>Chemistry</Major> 		<GPA>3.5</GPA> 		<Year>Senior</Year> 	</record> 	<record> 		<LastName>Johnson</LastName> 		<FirstName>Ori</FirstName> 		<Age>20</Age> 		<Major>Business</Major> 		<GPA>3.2</GPA> 		<Year>Junior</Year> 	</record> 	<record> 		<LastName>Kang</LastName> 		<FirstName>Han</FirstName> 		<Age>18</Age> 		<Major>Biology</Major> 		<GPA>3.9</GPA> 		<Year>Freshman</Year> 	</record> 	<record> 		<LastName>Jones</LastName> 		<FirstName>Raymond</FirstName> 		<Age>19</Age> 		<Major>Engineering</Major> 		<GPA>3.3</GPA> 		<Year>Sophomore</Year> 	</record> 	<record> 		<LastName>Akhtar</LastName> 		<FirstName>Ali</FirstName> 		<Age>19</Age> 		<Major>Literature</Major> 		<GPA>4.0</GPA> 		<Year>Freshman</Year> 	</record> </student-data>

Excel was able to format all of this information automatically, so you didn't have to.

This trick can come in handy with large data sets of over thousands of cells, so you can create multiple XML data files quickly. However, you can still save time and export XML more reliably with any amount of data.

This was a very simple example, though, and in the future, you might find yourself facing some tougher scenarios. If this happens, it will help if you have advanced Excel skills. If you want to learn how to handle these scenarios, take a look at this course about Pivot Tables, Power Query, Power Pivot, and DAX.

I hope that you have enjoyed this article. You've now successfully converted Excel to XML. Hopefully when you need to do it in your work, you will find it easy to do.

If you're interested in learning more advanced Excel skills, let's move on to learning how to use SQL in Excel.

Frequently Asked Questions

What is Excel XML format?

Excel can export data into XML (or Extensible Markup Language). It is based on an XML schema, which defines the structure of the file. You can create it in NotePad. To convert the data, go to the Developer tab – Source. You can then load your XML schema and match it to the data. You can then go to Developer – Export, or File – Save as – XML file.

How do I open an XML file in Excel?

To open an XML file in Excel, you should go to File – Open. On the PC, you should change the File Type from "All Excel Files (*.xl*)" to "XML file (*.xml)." You should then navigate to where the file is stored, click on it, and click OK. Your file will then be opened in Excel.

How do I view an XML file?

You can view an XML file in either Notepad or Excel. To view it in NotePad, go to File – Open, and change the File Type from "Text Documents (*.txt)" to "All Files (*.*)". To view it in Excel, go to File – Open, and change the File Type to "XML Files (*.xml)."