5 Steps to Convert an Excel File to XML File

You might want to export your Excel worksheet as an XML file for various reasons. However, converting an Excel file to an XML file is relatively easy. This article will show you only 5 steps to convert an Excel file to an XML file. So, let’s begin.

Export an Excel File as An XML File.xlsx

Table of Contents

Introduction to the Dataset

I’ve created the following dataset in a new worksheet. Now I’m gonna export this dataset as an XML file.

Guide to Convert an Excel File to XML

For your better understanding, I’ve broken down the whole process of converting an Excel file to an XML file into 5 simple steps. I hope this will help you convert your own Excel file into an XML file.

First Step: Create an XML Schema

To export your Excel worksheet as an XML file, the first ever thing you need to do is create an XML schema.

I’ve made a different tutorial on creating an XML schema using data in Excel. If you don’t know how to create an XML schema for your own dataset, I’m recommending you go through that tutorial first.

Anyways, the following markup code is the schema for my dataset. In this XML schema, I’ve described how data from my Excel worksheet will be stored in the XML file.

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <record>
    <FirstName>Ulrick</FirstName>
    <LastName>Copner</LastName>
    <Email>ucopner0@naver.com</Email>
    <Gender>Male</Gender>
  </record>
  <record>
    <FirstName>Tabbie</FirstName>
    <LastName>Aysik</LastName>
    <Email>taysik1@topsy.com</Email>
    <Gender>Female</Gender>
  </record>
</dataset>

A Complete Guide to Convert a CSV File to an XML File in Excel

Second Step: Open XML Source Task Pane

XML files related commands are available in the XML group. You will find the XML group in the Developer tab. However, by default, you may not find the Developer tab in the main ribbon. If you don’t see it in the main ribbon, you have to activate it first. From the following link, you can read 2 ways to add the Developer tab in Excel.

Once the Developer tab is visible in the ribbon, you can start working now.

Step_1: First, select the Developer tab.

Step_2: Then, click on the Source command in the XML group.

Now the XML Source task pane will appear on the right side of your Excel worksheet.

Third Step: Open Your XML Schema

At this stage, you need to import the XML schema that you created at the beginning.

To do that,

Step_1: Click on the XML Maps command at the bottom of the XML Source task pane.

The XML Maps dialog box will appear.

Step_2: Click on the Add button in the XML Maps dialog box.

Step_3: Now navigate to the folder where you’ve saved the XML schema.

Step_4: Double-click on the XML schema to add it to the XML Maps dialog box.

After selecting the XML Schema, the following dialog box will appear.

Step_5: Just hit the OK button to proceed.

Now check out the XML Maps dialog box. You will see the XML schema have added to the XML Maps dialog box.

Step_6: Now hit OK to go back to your worksheet.

Now you will see all the column names of your XML schema in the XML Source task pane. This means you have successfully added your XML schema to your Excel file.

How to Import XML Data from Web to Excel

4 Ways to Open an XML File in Excel

Fourth Step: Map XML File to Excel File

Now you need to create a map between your Excel dataset and XML file.

For that,

Step_1: Drag each column name from the XML Source task pane.

Step_2: Then drop each of them in the corresponding column header of your Excel dataset.

Step_3: Repeat dragging and dropping until you’re done with all of your columns.

As you drag and drop the column names from the XML Source task pane, the columns in your Excel dataset will be converted into tables. Thus, you can be sure that the mapping has been done correctly.

Also, you will see that each of the column names in the XML Source task pane has been turned into bold letter format. This is another way to be sure that the mapping has been done correctly.

To check it, you can click on any column names in the XML Source task pane. After selecting a column name in the task pane, the corresponding column in the Excel dataset will automatically be highlighted straightway.

Fifth Step: Convert Excel File to XML File

Finally, you can export your Excel file as an XML file now.

To export,

Step_1: Select any cell of your dataset.

Step_2: Then go to the Developer tab in the main ribbon.

Step_3: Click on the Export button in the XML group.

The Export XML dialog box will appear.

Step_4: Give your XML file a name in the File Name box.

Step_5: Then hit the Export button to start exporting your Excel dataset.

To check whether your Excel dataset has been converted into an XML file or not,

Navigate to the folder where you have saved your XML file.

After that open the XML file.

The result will look like the following picture. There you will see that each of the rows of the Excel dataset has been saved inside the “record” tag.

Conclusion

Throughout the article, I’ve tried to show you to convert an Excel file to an XML file with 5 simple steps. I hope, after reading this blog, you can export your own Excel file as an XML file with ease. Still, if you face any problems, let me know in the comment section. Regards!

Related Articles