EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools XML in Excel
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

XML in Excel

By Pradeep SPradeep S

XML in Excel

XML in Excel (Table of Contents)

  • Definition of XML & XSD
  • How to Import XML File in Excel?

Introduction to Extensible Markup Language (.XML)

Designed for storing and transporting data. It is a markup language similar to HTML. XML File appears as below mentioned, where it is a note to John from Craig.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

XML File type & structure appears as below mentioned:

<note>
<to> John</to>
<from> Craig</from>
<heading>Reminder</heading>
<body>Don’t forget me this weekend!</body>
</note>

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,858 ratings)
  • XML is just a piece of information wrapped in tags, just like the above-mentioned note.
  • Usually, most of the XML applications work as expected even if you add new data (or it is removed).
  • XML Simplifies tasks like data transport, data sharing, data availability & platform changes.
  • The XML standard is a flexible & easy way to create information formats.
  • XML helps out in electronically sharing structured data via the public Internet or through a corporate network.
  • XML is a hierarchical structure.

Definition of XML & XSD

  • XML Schema language is also referred to as XML Schema Definition (XSD).
  • XSD defines the structure of the XML file, which needs to be created and also it indicates where the columns need to be mapped in the tabular excel data.
  • It is also referred to as Extensible Stylesheet Language Transformation (XSLT) or (.xslt file).
  • XSD implement data type and validation to file.
  • XSD allows you to create elements, Data relationships & Data validation is defined in XML.
  • XML data files (.xml), which contains custom tags and structured data.

How to Import XML File in Excel?

You can import the XML file in Excel from the source option in the Developer tab. If the Developer tab is not available in the Ribbon, you need to add the developer tab with the mentioned steps or procedure.

Go to “File” and select “Options.” and select “Customize the Ribbon”, or you can right-click on the Home tab and select “Customize Ribbon”, as shown below.

In Excel Options –

Excel Options -XML

When you return to the main Excel page, you will see the Developer tab next to the view tab at the top. In that, if you need to select the source tool to map elements in your XML to your spreadsheet.

Developer Tab -XML

Let’s check out the steps available to convert an excel file to an XML data file & vice versa.

Step 1 – To Convert your Spreadsheet into XML, you need a Markup Schema

This markup schema defines each field that you’ll use in your XML file, i.e. the structure of the XML file. We have to do this outside Excel, in the other filetype.

In the below-mentioned spreadsheet, I have four types of column & information, i.e. Car makes, Car model, its year of manufacture, and at last, Car cost or value. So, in our Excel schema will have four different tags, and we need to create them.

XML Spreadsheet

In the above spreadsheet, it contains four columns, each of which will become an element. The car data in the above cells of excel are not independent. For example, I have four cells in a row; all that row data is related to each other, it provides details about a car type and its information, i.e., the brand name, model name, and year of purchase it is cost or value at present.

Open Notepad and add the following two lines; they are the standard pieces of information that XML files need at the beginning. (Note: It is optional ).

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

Notepad - Codes

The element is declared from line three onwards. The record tags, i.e. < >, defines a single record described in our spreadsheet. Whereas other tags define the relevant pieces of information under that for that record. i.e.

<record>
<Make>Toyota</Make>
<Model>Fortuner</Model>
<Year>2010</Year>
<Value>949999</Value>
</record>

Finally, add the closing </record> tag at the end of the file in the note pad.

NOTE: The tags (< >) in the above notepad under the record refers to the column names; you have an option to give them any name you want apart from the original name mentioned in the excel tabular data! For example, instead of ‘’Value”, you can use “Cost”.

I just added the first-row data for the explanation purpose. You can add as many rows, based on your choice. Again, you can start with <record>, after the </record> tag. Once you entered the data in the notepad, you can save it as test.xml in the file name and save it on the desktop.

Save File name

Later, you can create a new excel file with filename XML.xlsx

As you have noticed, we entered one car detail (one record) in the XML file (NOTEPAD). Now, you have created a markup schema and saved it on your desktop.

Step 2 – How to Add this Markup Schema to New Excel File

In the Developer tab, click on Source in the XML section.

XML 5

XML window pane appears; click on XML Maps.

 XML Source

XML Maps window appears; in that, Click Add in the resulting window.

XML Maps

Then you can navigate to your XML file on the desktop, which you saved as Abc.xml, and you can select it.

Select XML Source

Due to excel version issues in my system, I am uploading other .xml file with another option. The other option in the developer tab is to select Import in the Home tab.

Import XML file

Once you select a .xml file on the desktop, a popup appears; click Ok.

Excel Schema

Now to import this excel data, right-click on the XML file name and select the MAP Element option.

Map Element

Then you will see the following popup, select the location to where you want your data.

XML Source 1

Then you will see the table headers in your excel sheet.

XML 7

Now under the developer tab, click on Import.

developer import

It will open a window to select the file.

Import XML file 1

Output:

ABCD

Another popup appears asking for cell reference, where you want the data to be placed.

import data 1

The import .xml file type appears as shown earlier. Similarly, the Export feature in the developer option will be available in your spreadsheet contains XML file data; otherwise, an export option will be greyed out.

Export File 2

Here, I opened an excel file containing XML file data; I can export it and save it as a .xml file type anywhere with an export option in the Developer tab.

Export File 1

Things to Remember About XML in Excel

  • Every bit of data you enter has to start and end with an identical tag, i.e. start with < Record> Data </Record> (End with).
  • Tag names are case sensitive, i.e. capitalization in the end tag should be the same as the beginning tag.
  • The XML file should always begin and end with a root tag. There can only be one root tag in a file. In the example above, the root tag is <Record>.
  • You can have an empty tag, i.e. you can mention or put the slash at the end of the tag instead of the beginning: <Record> </Record>.

 Recommended Articles

This is a guide to XML in Excel. Here we discuss How to Import & Export XML Data files along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. Database Function in Excel
  2. Excel Hacks
  3. Excel Import Data
  4. Power Query in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formulas, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more