How to Open XML Files in Excel?

by

XML files are frequently used to store and exchange data between various systems, such as databases, applications, and web services.

Sometimes you will interact with XML files as you work with Excel.

However, XML files cannot be used directly in Excel; therefore, you need to know how to import or open such files in Excel.

This tutorial shows you three methods for opening XML files in Excel.

What is an XML File?

An XML file (called the Extensible Markup Language file) is a file that uses a markup language to store data in a structured format.

The following is an example of an XML file showing two records of book titles:

example of an XML file

XML is intended to store and transport data and is often used to exchange data between different systems.

XML files can be opened and read by any application that supports XML and can be used to store a wide variety of data, including text, numbers, and images.

If the XML file contains much textual data, you can use a text editor such as Notepad to read it.

Conversely, if it includes many numeric data, you can import the file into Excel and use Excel’s powerful features to analyze the data. 

Now let’s plunge into the tutorial and learn how to open an XML file stored locally on your computer and the Web using Microsoft Excel.

Also read: CSV vs. XLSX Files – What’s the Difference?

Method #1: Open an XML File in Excel Via the File Tab

We can use the options on Excel’s File tab to open an XML file that is stored locally on the hard drive. 

Suppose we have the following XML file called Book Catalog stored on our local hard drive. 

XML files stored locally on your system

We want to open this XML file in Excel, and we can do that using the File tab in the Excel ribbon.

Below are the steps you can follow to open the XML file in Excel quickly:

  1. Open the Excel application.
  2. Click the File tab to open the Backstage window of the Excel application.
click the file tab
  1. On the Backstage window, click Open option on the left sidebar and then click on the Browse option that appears on the right panel.
click the browse option
  1. In the Open dialog box that opens up, navigate to where the XML file is stored on your system, select it, and then click the Open button.
open the XML file from your system

Note: Ensure the file type in the box next to the File name box is set to All Excel Files.

  1. Select the As an XML table option on the Open XML dialog box that appears, and then click the OK button.
select the as an XML table option

A Microsoft Excel dialog box will appear with the message, “The specified XML source does not refer to a schema. Excel will create a schema based on the XML source data.”

The specified XML source does not refer to a schema. Excel will create a schema based on the XML source data.

Note: A schema refers to the layout or structure of a dataset, including the names and types of columns.

  1. Click OK on the Microsoft Excel dialog box to allow Excel to create a schema based on the XML source data.

Once you are done with the above steps, you will see that the XML file has been opened in Excel, and the data has been structured in separate columns In Excel, as shown below.

XML file opened in Excel

One limitation of this method is that if your original XML file changes and you want your data in Excel also to change to reflect the updated data in the XML file, you will have to repeat the entire process. This is a suitable method if you want to do this once or twice, but in case you want to regularly update your XML table in Excel, which is linked to an Excel file on your system, you should use the next method covered in this tutorial

Also read: How to Convert a CSV File to Excel?

Method #2: Open an XML File in Excel Via the Data Tab

Another quick way to open an XML file in Excel is by using the Get & Transform options in the Data tab.

Suppose we have the following XML file called Book Catalog stored on our local hard drive. 

XML file in notepad

Below are the steps to open the XML file in Excel using the options in the Data tab

  1. Open the Excel application.
  2. Click the Data tab.
  3. In the Get & Transform Data group, click the Get Data option. This will show you a drop-down with some options.
  4. Hover the mouse pointer over the From File option and click From XML on the sub-menu.
click on the from XML option
  1. In the Import Data dialog box that opens, navigate to the folder where the XML file is saved, select the file, and click on Import button. 
select the XML file stored on your system

The preview of the imported data is shown in the Navigator window (as shown below). 

XML file opened in power query

When you scroll to the right of the Navigator window using the scroll bar at the bottom of the screen, you notice that the Attribute:id column appears at the very end of the dataset. 

The Attribute:id column is supposed to be at the beginning of the dataset. Therefore, do the following to bring the Attribute:id column at the start of the dataset:

  1. Click the Transform Data button at the bottom of the Navigator window.
click on the transform data option
  1. In the Power Query Editor, scroll to the end of the dataset.
scroll to the end of the data set
  1. Right-click the header of the last column, hover the mouse pointer over the Move option on the shortcut menu that appears and click To Beginning on the sub-menu.
move the column to the beginning by selecting the to beginning option

The Attribute:id column is pushed to the beginning of the dataset:

attribute ID column is now at the beginning of the Dataset
  1. Click the Close & Load button to load the data onto the Excel worksheet and close the Power Query Editor. 
click on close and load in the file tab

The above steps would insert a new worksheet in your workbook where the XML file data would be imported from the file that was locally saved on your system

XML data opened in Excel

One amazing thing about using Power Query to open an XML file in Excel is that if your data in the original file changes and you want to import that new data into your Excel file, you can simply right-click on the table you got and click on the refresh option. when you refresh the query, it repeats all the steps in the back end and fetches the latest data from the file

Also read: How to Open DAT Files in Excel?

Method #3: How to Open XML File That’s on the Web in Excel

Sometimes, you may want to import the data from an XML file that is stored on the web, and you have a URL of that XML file.

This can again easily be done using the Get & Transform option in the Data tab in Excel.

For the purpose of our demo, we will use the following Forbes site map at https://www.forbes.com/sitemap_index.xml to show how this technique works.

When you open the above link, you will notice that the page that opens is an XML file and looks like something as shown below:

XML data stored on a URL

We use the steps below to import the XML file into Excel:

  1. Open the Excel application.
  2. On the Data tab, click the Get Data drop-down in the Get & Transform Data group, hover the mouse pointer over the From Other Sources option, and choose From Web on the sub-menu.
click on the from web option
  1. Copy the Forbes’ site map URL and paste it into the From Web input box that appears and click OK: 
  1. Click Connect on the Access Web Content message window to connect anonymously to the site map.
click the connect button

The preview of the imported data appears in the Navigator window:

preview of the XML data on the web in power query

If you want to make any changes to the data, you can click the Transform Data button at the bottom of the screen to open the Power Query Editor, in which you can make the changes and then load the data onto an Excel worksheet. 

  1. Open the Load drop-down at the bottom of the Navigator screen and choose Load To.
click on the load to option
  1. In the Import Data dialog box that appears, select the Table and the New worksheet options and click OK.
select the table and new worksheet options

The above steps would add a new worksheet to your Excel workbook, and the Forbes XML file would be imported into Excel (as shown below):

XML data inserted in Excel in a new worksheet

One advantage of using the Get & Transform Data feature to open XML files in Excel is that in case there are updates to the XML file, you do not have to repeat the process of importing.

Instead, you can right-click the data and choose Refresh on the shortcut menu that appears, and the dataset will be updated accordingly.

right click to refresh the query

In this tutorial, I cover three simple methods you can use to open an XML file in the Excel application quickly.

If you have the XML file stored locally on your system, you can use Method 1 or Method 2 (which uses the File tab option and the Data tab option, respectively).

And in case your XML file is stored on the web, and you have the URL, you can use the third method that uses Get & Transform option the first open the XML file in Power Query and then imports that XML data into a new worksheet in Excel.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment