Excel to XML: How to Transfer Your Spreadsheet Data Onto an XML File

excel to xmlExcel 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. Hundreds, sometimes even thousands of cells crammed with information… that’s a lot of data to manually re-type!

Extensible Markup Language, or XML, is an easy to use, read, and format document markup language that can easily store your Excel files and help you transfer them to other applications.

In this guide, you’ll learn how to convert Excel to XML the quick and easy way. 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.

Converting Excel to XML

The first thing you need is, of course, an Excel spreadsheet. Below, we have an example spreadsheet with six columns and 26 rows of data. The columns separate the last names, first names, ages, majors, GPAs, and school year of each student listed in the spreadsheet.

Step 1 – Create your Excel spreadsheet

udemy excel to xml 1

This is a very basic Excel document and lacks advanced functions. It is a simple spreadsheet storing numerous types of data, separated by columns. It keeps track of 25 college students, and their last names, first names, ages, majors, GPAs, and school years.

Step 2 – Create your XML schema

After you have your Excel document setup, you need an XML schema. An XML schema is the document that defines an XML file’s structure. You can set one up easily in Notepad using the following code:

<?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>

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, <TAGNAME> and <TAGNAME2> and so on have become <LastName> and <FirstName> and so on, after the columns in our data set.

Also take note of the <student-data> tags that hold the contents of each <record> tag. <student-data> should be renamed according to the name of your Excel spreadsheet. In this example, our Excel workbook with the student data is called, fittingly, student-data.xlsx, and so our tags should be <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 in the options. Click on File, and then go all the way down to Options. Click on the Customize Ribbon section, and in the column on the right, there should be a list of tabs called Main Tabs that you can check or uncheck.

udemy excel to xml 3

Find where it says Developer, and make sure the box is checked. Then click OK. The Developer tab should now be up top, to the right of the View tab.

If you’re having trouble navigating Excel and its various options, it would be beneficial to take a basic Excel course for beginners, and leanr your way around.

There are also plenty of advanced Excel training courses for those who have a basic understanding, but need experience with the more complicated elements.

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 so on.

udemy excel to xml 5

Find the XML section between Controls and Modify, and 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 window will pop up. Click Add to add a new XML schema, find your schema.xml file in the browser, and click OK.

udemy excel to xml 6

Now that your XML file is added, click OK again 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: LastName, FirstName, Age, and so on. Drag each element individually into its appropriate column, starting at Row 1.

udemy excel to xml 7

Converted columns should turn blue. Do this until all of your columns have been fitted with the XML elements.

Step 7 – Export your XML data

Once you’ve finished converting each column into an XML element, return to the Developer tab, find the XML section again, and click Export. Save your XML file, and then open it in Notepad to see your results:

<?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>

… and so on, until the end of our data set. That’s right! 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 a hundred or thousand cells, but even if you have a couple dozen and need to save time.

You’ve done it! You’ve successfully converted Excel to XML. This was a very simple example, though, and in the future you might find yourself facing some tougher scenarios.

Learn how to program in XML with this beginners course, and learn Excel from the ground-up with this introductory Excel training course.