How to Convert an Excel Spreadsheet to XML (Import And Export Data Easily)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Extensible markup language (XML) is a common format for working with data. And if you want to get your spreadsheet from Excel to another program, you might have to use it.

Excel lets you convert your spreadsheets to XML files easily—but it’s not obvious how to get started.

Let’s take a look at creating an XML schema, adding that schema to your spreadsheet, and saving your new XML file.

We’ll also go over the basics of XML, in case you’re not familiar with it.

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

We’ll be using a spreadsheet throughout this article to see how XML works.

If you’d like to follow along, you can download that same spreadsheet below.

Grab it now and let’s learn about XML!

cloud-download

BONUS: Download the XML Exercise Workbook File to go along with this post.

What is XML?

XML is a way of marking up data. It doesn’t actually do anything—it serves to tell a program what particular pieces of information represent.

For example, you might have some XML that looks like this:

xml-sample

The tags—site, title, and topic—don’t have any built-in function or purpose. But another program might be able to interpret them in a way that’s useful.

When you convert Excel data into XML, you simply add markup information like that above to your spreadsheet. Then that information can be better used by some other application.

Creating a markup schema

Before you convert your spreadsheet into XML, you need a markup schema. This defines each field that you’ll use in your XML file.

In our spreadsheet, we have four types of information: car make, car model, year of manufacture, and value. So our Excel schema will have four different tags.

For simplicity’s sake, we’ll call them “make,” “model,” “year,” and “value.”

Open up a blank text file, and add the following lines:

xml-declaration

These are standard pieces of information that XML files need. Don’t worry about them too much right now.

See where we typed “car-data”? You should use that space to include a descriptive name for your XML schema.

Kasper Langmann, Co-founder of Spreadsheeto

Now add the following lines:

xml-examples

The record tags define a single record described by our spreadsheet. The other tags define the relevant pieces of information for that record.

Finally, add the closing tag to the end of the file:

xml-closing-tag

Now, save the file as “car-data.xml.”

Kasper Langmann, Co-founder of Spreadsheeto

Why add two record?

You’ll notice that we entered the information for two cars (two records) in the XML file. This is important.

If you don’t do this, Excel won’t know that each XML element is meant to be repeated throughout the spreadsheet. With a non-repeating element, you can only assign a single cell to any category.

And that doesn’t work for most spreadsheets.

Adding the markup schema to Excel

Now that we have an XML schema, we need to add it to Excel.

First, make sure the Developer tab is visible.

Right-click on the Ribbon, and select Customize the Ribbon:

customize-ribbon

In the resulting window, make sure the Developer tab is checked:

developer-tab-1

Now we need to add the schema file to Excel.

Kasper Langmann, Co-founder of Spreadsheeto

Open the Developer tab, and click on Source in the XML section:

xml-source

In the XML pane, click XML Maps:

xml-maps

Click Add in the resulting window, then navigate to your XML file and select it.

You’ll see a warning message from Excel:

create-schema

Click OK. You’ll now see your XML map:

added-map

Finally, click OK again, and you’ll see your map added to the XML Source pane:

xml-source-map

Now that the XML schema has been added, we can start assigning data to it.

Kasper Langmann, Co-founder of Spreadsheeto

Marking up your data

With the XML schema loaded into Excel, we can begin the process of assigning XML tags to the data in our spreadsheet.

All you need to do is drag the XML element to the corresponding column in your spreadsheet.

Below, you can see me dragging the “make” element to the “Make” column:

adding-element

If everything has gone as planned, the column will be highlighted in alternating shades of blue.

Kasper Langmann, Co-founder of Spreadsheeto

To assign the rest of the elements, drag them to the corresponding columns.

When you’re done, all the columns will be highlighted. That means all the data in your spreadsheet has been correctly tagged with XML.

tagged-xml

Just to make sure, we’ll export the XML and take a look at it.

Kasper Langmann, Co-founder of Spreadsheeto

Exporting XML

To export your data into an XML file that other apps can read, go to the Developer tab in the Ribbon and click Export:

export-xml

Choose a location and a filename, then click Export.

You now have an XML file with all your Excel data in it!

If you navigate to that file and open it in a text editor, you’ll see the full XML:

exported-xml

Starting converting Excel to XML

Once you’ve gone through this process a couple times, you’ll be able to do it very quickly.

Soon you’ll be exporting your Excel spreadsheets to XML in no time at all!

2019-10-04T14:00:09+00:00