Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > XML and Excel > XML in Excel
Deze pagina in het Nederlands

XML and Excel

XML in Excel

Let's get a bit more practical and use Excel to do something with XML files. With Excel 2003/2007/2010/2013 importing XML data is very simple. Unfortunately, the XML facilities are only available in Excel 2007 and up and in the Microsoft Office Professional or ultimate versions of Excel 2003, or when Excel 2003 is bought as a standalone package. When you select File (Office button in Excel 2007 and up), Open from Excel, then you will see xml file types are shown along with the available Excel files:

By default, XML files show when you try to open a file with Excel
Figure 2: The File, Open window of Excel 2007 and up.

Selecting the file test-en.xml causes a small window to appear (see figure 3).

The Open XML selection dialog of Excel
Figure 3: Excel asking what to do with the XML file

By selecting the third option Excel will analyse the structure of the XML file and present the structure in the taskpane (figure 4). Excel does not open the XML file at this moment.

The structure of the xml file shows up in the taskpane
Figure 4, The structure of the xml file shows up in the taskpane

The elements of the XML file can now be tied to cells on the sheet, simply by dragging them from the task pane to a cell in Excel. See Figure 5 and 6.

Dragging an element to an Excel cell
Figure 5: Dragging an element to an Excel cell

Result after dropping the element
Figure 6: Result after dropping the element

By dragging an element which contains multiple other elements (or by selecting multiple elements in the taskpane using control+click or shift+click) all elements are placed next to each other as a list:(see figure 7).

List produced by dragging the "employee" element to the sheet
Figure 7: List produced by dragging the "employee" element to the sheet

Now a template has been defined which can import data from XML files which have the same structure as the one we "opened" in Excel. We can import the data from test_en.xml, simply by pressing the refresh button on the "External Table Data" group of the custom "Table tools, design" tab of the ribbon in Excel 2007 and up, or by clicking the Refresh XML data button on the List toolbar of Excel 2003 (see figure 8 and 9). The data from test_en.xml will be read into our newly created template. See figure 10.

Refresh Data using the ribbon
Figure 8: Refresh Data using the ribbon

Button "Refresh XML data"on the “List” toolbar
Figure 9: Button "Refresh XML data"on the "List" toolbar

Result of importing the data
Figure 10: Result of importing the data

To import a different file into the same structure, use the connection properties dialog, which you can access by clicking the dropdown arrow below the refresh button (see figure 11):

Changing connection properties to Import another XML file into Excel

Figure 11: Changing connection properties to Import another XML file into Excel

After clicking connection properties, click the Details tab on the dialog that shows up and you can hit the browse button to access a different XML file. Or -in Excel 2003- Simply press the "Import XML" button on the List toolbar.



Showing last 8 comments of 201 in total (Show All Comments):


Comment by: Chris P (12-7-2017 00:11:46)

Hi Jan

I have searched the internet and this incredible thread of help to no avail.

I want to add an XMLMap, then automatically map it to a ListObject. I have looked at `CreateXMLList()` above, but my XMLMaps will vary, so I want to *derive* `strXPath` from the XMLMap, to pass into `lcNewCol.XPath.SetValue mapContact, strXPath` in a simple loop over all elements in the XMLMap. Is this possible? I just want the default behaviour when you right click on the root of the XMLMap, click Map element... OK.

Huge thanks


Comment by: Jan Karel Pieterse (12-7-2017 10:39:41)

Hi Chris,

I suspect this is possible, but I am too tied up to help out now, suggest to post a question at


Comment by: Bruce Schneider (28-8-2017 04:28:18)

Think I've cracked the code on Excel not changing data into a table format automatically when drag & drop mapping from XML Source pane!


Comment by: When pulling xml data is not coming like a Table (4/22/2015 2:34:45 PM)
deeplink to this comment
Hello, When I am pulling fields from XML source into cells, usually it gives me a table with respected header.

But unfortunately for one of the link it is giving like a blue border boxes with hear option (like, handle).

Suggested solution

Excel appears to be looking for multiple <record> folders in the XML Map. By using XML Notebook (free download from Microsoft), I could open a sample XML file from TestLink (I'm trying to import to this) and duplicate one of the lowest level <testcase> folders. In the Easy-Excel demo, they call theirs <record>. As soon as I saved this XML file, opened up my Excel data file, clicked XML Maps..., opened the modified XML map I'd just saved, then dragged the top folder over the upper left data item: BINGO, Excel converted my data file to a table with 18 rows automatically (that's how many rows I had. 2.5 hours on that one, so I hope this will save someone else some time. Best Wishes, Me!


Comment by: Thomas (8-1-2018 16:41:03)

This thread is very useful for understanding better the ways to manipulate the XML maps, creat them etc using VBA. But i noticed at least one comment that didnt get and answer and is the same problem I have.

so I have an rss feed mapped from yahoo finance using a url as such

(This is the only element i want to update/change in an existing mapped XML map

Is this possible?

In case I am not asking correctly

what i want to know is it possible for a different url (made with a macro) to somehow import into that already mapped XML schema to simply replace the link and url elements in the already mapped XML map??

I noticed one comment had this code

Sub GetInterestRates()
Dim TargetPath As String
TargetPath = ActiveSheet.Range("S12").Select
ActiveWorkbook.XmlMaps("interestRateCurve_Map").Import URL:=TargetPath
End Sub

so specifically, if i had a cell with a new URL value and a new link value

Is there a simple code for the xml maps already created to simply replace one or two elements then run the regular refresh databinding routine?


Comment by: Jan Karel Pieterse (9-1-2018 08:26:41)

Hi Thomas,

This appears to work for me:

ActiveWorkbook.XmlMaps(1).Import "", True

So if you have a cell in which the new URL is located you could do something like:

ActiveWorkbook.XmlMaps(1).Import Worksheets("Sheet1").Range("A1").Value, True


Comment by: Thomas (9-1-2018 15:29:45)

Outstanding!!!!    That worked for me. Thank you so much Jan


Comment by: keepITcool (29-9-2019 04:56:00)

please note that you for large tables it really pays to get the count of the records you will be importing and set the range prior to import.

'This takes a whopping 60 secs for 60'000 records x 10 fields
lst.XmlMap.Import "c:\data\dummy.xml"
'This takes less than 5 secs.
lst.Resize lst.Range.Resize(data.count)
lst.XmlMap.Import "c:\data\dummy.xml"



Comment by: Jan Karel Pieterse (29-9-2019 20:36:00)

Hi KeepITCool,

Thanks! Nice to hear from you again.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.