Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Remove Duplicates Example in Excel
  • How To Create Pareto Chart in Excel
  • How to count table rows in Excel
  • Working With Tables in Excel
  • Get column index in Excel Table

References

  • How to use Excel ROW Function
  • How to use Excel OFFSET function
  • How to get last row in text data in Excel
  • How to use Excel COLUMN Function
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation date in next 30 days
  • Excel Data validation must begin with
  • Excel Data validation exists in list
  • Excel Data validation allow weekday only

How to convert Excel file to XML data file

by

You can convert your Excel file to an XML data file, or vice versa. This enables you to exchange data between different applications. First, turn on the Developer tab.

Here’s the data we want to convert to an XML data file.

First, we will create a schema based on XML source data. The schema defines the structure of the XML file. We have to do this outside Excel.

1. For example, open Notepad and add the following lines.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<record>
<LastName>Smith</LastName>
<Sales>16753</Sales>
<Country>UK</Country>
<Quarter>Qtr 3</Quarter>
</record>
<record>
<LastName>Johnson</LastName>
<Sales>14808</Sales>
<Country>USA</Country>
<Quarter>Qtr 4</Quarter>
</record>
</data-set>

Note: the tags refer to the column names, but you can give them any name you want! For example, instead of LastName use Name.

2. Save the file as schema.xml.

3. Open data-set.xlsx.

4. On the Developer tab, in the XML group, click Source to open the XML Source task pane.

5. To add an XML map, click XML Maps.

The XML Maps dialog box appears.

6. Click Add.

7. Select schema.xml and click OK twice.

8. Now simply drag (map) the 4 elements from the tree onto the worksheet (row 1).

9. On the Developer tab, in the XML group, click Export.

10. Save your file as data-set.xml and press Enter.

Result:

That saves quite a lot of time!

Note: to import an XML file, open a blank workbook, on the Developer tab, in the XML group, click Import, and select the XML file.

Post navigation

Previous Post:

VLOOKUP without #N/A error in Excel

Next Post:

Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Complete List of Excel Logical Functions, References and Examples
  • How to return blank in place of #DIV/0! error in Excel
  • How to use Excel FALSE Function
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get work hours between dates and times in Excel
  • How to determine year is a leap year in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Get last day of month in Excel
  • How to calculate Day of the Year in Excel

Grouping

  • If cell contains one of many things in Excel
  • How to randomly assign people to groups in Excel
  • Map text to numbers in Excel
  • Group arbitrary text values in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Check if multiple cells have same value with case sensitive in Excel
  • 44 Practical Excel IF function Examples
  • How to increase by percentage in Excel
  • How to generate random date between two dates in Excel
  • Lock Cells in a Worksheet Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning