How to Convert Excel to XML – Step-by-Step Tutorial (2024)

We all know basic excel skills that help us work in excel spreadsheets with extensive data. But what if you need to transfer the excel file somewhere and change it into other formats?

The extensible markup language or XML comes in handy for this. Simply put, it is a markup language that helps store data in an excel spreadsheet and changes its format.

You can easily convert excel data into XML data – but how do you do that?

For that, download our FREE Exercise Workbook here and continue reading the article below. 😀

What is the XML file format?

The XML file format is a way to write text in a document using tags. These tags are customizable and represent the structure of the document.

It also tells how the XLS file can be converted to an XML file and transferred to another application.

XML format uses the DOM or Document Object Model as its basic structure and can help separate text in HTML.

For instance, XML data for a company employee might be:

The tags ‘Name, ID No., Post, Salary, and Status’ consist of the document’s data. You can customize the tags as we did here for an employee’s data.

Create the Excel File

To convert Excel to XML, you need to create an XLS file containing all the data. I have created an example below that lists the members of a company and their details.

Each field contains certain information about every employee.

How to Create the XML Schema?

The XML Schema will structure each spreadsheet field and its appearance in the XML file.

Our spreadsheet has five columns with different data types setting the base for our XML file.

1. To create the XML file, open any text editor and begin entering the standard XML elements.

Don’t fret just yet; We’ll explain all the alien-like items in this image one by one. 😄

The first line in the image is the standard XML line. It is used each time you create XML documents.

The version 1.0 encoding UTF specifies that it is an XML format.

The second line indicates the type of text. For example, this data was about employees of a company, hence <employee-data>. The remaining line is the default.

Since this statement starts with <employee-data>, it is the root element here. The remaining elements of the argument make up for the child element.

The third line, containing the record tag, indicates the beginning and end of a single field. It consists of the information in each field of the spreadsheet.

For instance, the first field is about Alice A. So it will include all the information about Alice on each tag.

The TAG NAME specifies the items in each column, whereas the data contains the corresponding value.

You don’t have to manually input the exact and each column name; they just need to be in the correct order. Excel can help us do the rest.

For the TAG NAME, say we want the first employee’s name.

So, we will write – <Name>Alice A.</Name> – Similarly, for her ID, we will write – <ID No.>2854</ID No.> – .

Pro Tip!

Must note the opening and closing tag identified by a forward slash (/).

These are to be used at the beginning and each of each statement. Also, the closing and opening tags must be in the correct order, as the data between the two tags make for the XML elements.

The last closing tag  </employee-data>  marks the end of the file. In the last step, save the XML data file as “employee-data.xml.”

Enable the Developer Tab

Now that your schema markup is prepared, let’s add it to Excel.

1. Make sure the developer tab is enabled on your Excel spreadsheet. If it is not visible, right-click on the ribbon and select Customize the ribbon.

2. A dialog box will appear on the screen. On the bottom right side, mark the develop option as shown and click ok.

3. Now, you should be able to view the developer’s tab on the ribbon like this:

That’s it – now what? Now we add the Schema file to Microsoft Excel. 😃

Open the XML Source Task Pane & Add XML Maps

1. Click on Developer from the ribbon, and you will see a number of options. Select the Source icon:

The XML source pane will appear on the Excel spreadsheet.

2. Click XML Maps at the bottom right corner.

It shows a dialog box that searches for the XML map in the workbook.

3. Click the add button and select the XML map stored on your desktop.

You will see a warning like this one below from Microsoft Excel upon adding the map.

There’s nothing to worry about.

4. Simply press ‘Ok’.

5. You will now see the XML map in the workbook.

6. Click ‘Okay’ and move to the XML source pane, which looks like this.

7. Now, mark the data on the XML Map.

Assign XML Elements

Now that you have the XML schema and the XLSX ready, mark the XML tags to the spreadsheets’ data.

All you need to do now is drag one XML element from the source pane. Then drop it on the corresponding spreadsheets’ column name.

For instance, I will drop the “Name” XML element on the “Name” column, and a blue table will appear.

Apply the same process to the remaining columns. If you have been successful till now, your spreadsheet will look like this:

If you’ve achieved this form, it means all your data is perfectly mapped with XML tags.

Now that we’ve come this far, it is only fair we export it. So, onto the next step.

Export the XML File

1. To convert Excel to XML file formats, open the developer tab and click the export command.

A dialog box will appear, asking the location to save the file. The file type should be set by default.

2. Select a location, enter the name and click export.

The file is now saved on your desktop with all your XML data. You can open the file from the XML section on a Wordpad or other apps.

The XML file will show up like this:

You might receive a warning from Microsoft office. Exporting the file may cause loss of pictures, formatting, or other non-XML objects.

That’s not an issue. Go ahead and hit ‘Ok’. 🙂

That’s it — Now What

Beginning to convert Excel to XML files can be challenging at the start. But once you get hold of it, you can do it with your eyes closed.

In the above article, we not only learned about creating an XML schema but also to convert an Excel file into XML format and export it.

But that’s only about creating and retaining your Excel file in different formats. There’s so much more to Excel that you’d still want to learn like the VLOOKUP, SUMIF and IF functions.

Want to learn them all? Click here to sign up for my free 30-minute email course to master these functions.

Other resources

If you found the above article interesting, I bet you’d love to know more about managing your Excel files.

Try checking out how you can compress your Excel files, merge them, password protect them and if lost, recover them with sheer ease.