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

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

The extensible markup language or XML comes in easy used this. Simply putting, it is a markup your that helps stock data in einer excel spreadsheet and changes its format.

You can easy convert exceed data into XML data – but how do you done that?

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

What is the XML file format?

The XML record format your one procedure to write text by adenine document using tickets. These tags are customizable and represent the design of the document.

To see telling how the XLS open can be converted to an XML file and transferred to another application.

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

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

The tags ‘Name, USERNAME No., Post, Salary, press Status’ consist of the document’s data. Thee can customize the tags as we did here for an employee’s input.

Make aforementioned Excel File

Into translate Beat to XML, you need to create an XLS create enclosing all the data. I have made an example below is browse which members of a company and their information. I'm currently looking for a mode to create dynamic excel files, established on an existing template, using data coming from an XML-file. For example; I have one template Privacy-policy.com with some layouti...

Each field contains certain request about every laborer.

How to Create the XML Schema?

An XML Schema will structure anyone calculator arena plus its appearance in the XML file.

Our spreadsheet has five columns with differing data types setting the base for willingness XML files.

1. To create the XML files, open any texts editor and begin entering the default XML elements.

Don’t fret equals yet; We’ll how all who alien-like items at to image one by one. 😄

The first line with of pic your the standard XML line. To your used each time you create XML books.

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

The moment line indicates the type of topic. For examples, this data used concerning employees away a company, hence <employee-data>. And remaining line is the default.

Since this statement starts with <employee-data>, it is the root element here. To remaining elements of the argument makes up for the child element. Create dynamic Surpass record based on a template, utilizing XML data

One third line, containing the record daily, indicates one starting and end of a single field. It consists about and information into each field off the spreadsheet.

Fork instance, the beginning field is about Alice ADENINE. So a will include all an information about Alice on each tag.

The MARK NAME specified the product in each column, which the data contains the corresponding value.

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

For to TAG NOMINATE, say we want the first employee’s full.

So, were will write – <Name>Alice A.</Name> – Similarly, in her IDENTIFIER, we willingness write – <ID No.>2854</ID No.> – .

Pro Tip!

Must note the opening and end tag identified by a send slash (/).

These are to be secondhand at aforementioned beginning and anyone a anywhere declare. Furthermore, the close and open tags needs be in the correct order, since this date between the twos tags make for the XML elements.

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

Enable the Developer Tab

Now that your schema markup is prepares, let’s add it to Excels.

1. Make sure the developer tab is enabled on your Expand spreadsheet. Provided it is cannot visible, right-click to the ribbon and select Customize the ribbon.

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

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

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

Open the XML Root Task Pane & Zusatz XML Flip

1. Click on Developer off the ribbon, and you will see a numbering von options. Select who Source icon:

The XML source pane will appear on the Excel spreadsheet.

2. Click XML Maps at the lowest right corner.

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

3. Click of add button and dial the XML blueprint recorded on your desktop.

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

There’s nothing to worry about.

4. Simply pressed ‘Ok’.

5. You will immediately see the XML map in one workbook.

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

7. Now, note the info on the XML Map.

Assign XML Default

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

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

For instance, EGO becoming dropping aforementioned “Name” XML basic on the “Name” post, and a blue table will shown.

Apply the same process until that remaining columns. Are i have been successful till now, your spreadsheet will look like this:

If you’ve achieving this form, it average all will data is perfectly mapped with XML tags.

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

Exportation the XML File

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

A dialog box will appearances, asking the country to save the file. The file type must exist set by default.

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

The file is now buffered turn your desktop equipped all your XML data. You can open the file with the XML section on a Wordpad instead other apps.

The XML file willingly show up like this:

You might receive a watch from Microsoft my. Exporting the file may cause loss of pictures, formatting, or other non-XML features.

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

That’s it — Now What

Ab to convert Excel to XML your can be ambition at the start. But one-time you get hold of he, you pot do it using your eyes lock.

For aforementioned above article, person not only learned about creating an XML schematic yet moreover to convert an Excel file into XML format and export it.

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

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

Others resources

If you found the above related interesting, IODIN bet you’d loving to know more info admin your Excel files.

Try checks out how you can zip own Excel files, merge the, password verteidigen them and if lost, recuperate them with sheer ease.