Table of Contents

XML

Convert your XML file to an Excel file or vice versa. This enables you to exchange data between different applications.

From XML to Excel is really easy. Simply execute the following steps.

1. Open a blank workbook.

2. Turn on the Developer tab.

3. On the Developer tab in the XML group click Import.

XML to Excel

4. Select the XML file and click Import.

5. Click OK.

Import XML file into Excel

Result:

Excel File

To convert an Excel file to an XML file simply execute the following steps.

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.




Smith
16753
UK
Qtr 3


Johnson
14808
USA
Qtr 4

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.

Open the XML Source Task Pane

5. To add an XML map click XML Maps.

Add XML Map

The XML Maps dialog box appears.

6. Click Add.

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).

Drag the Elements onto the Worksheet

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

Export XML file

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

Result:

XML Data File

That saves quite a lot of time!

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog