Excel for Mac: Does it Support XML Data?

This week in our series about Microsoft Excel for Mac, we show how Excel for Windows and Mac differ regarding how each handles XML data.

If you use XML data files, Excel has some tools specifically designed for importing and working with your data.  However, these tools are only available in Excel for Windows.  Excel for Mac doesn’t have those tools, but it will let you bring the data from the XML file into your spreadsheet.  Don’t fret, though, because Power Query on Mac supports importing data from XML files just the same as Power Query on Windows.

Special XML tools in Excel for Windows

By using XML and Excel for Windows, you can add, identify and extract specific pieces of business data from Excel documents.  For example, an invoice that contains the name and address of a customer can be imported, revised and exported back to the same source or other databases and applications.  To learn more, you can read Microsoft’s help article about it - Overview of XML in Excel.

The tools for working with XML data can be found on the Developer tab of the Ribbon, as shown in the screenshot below.

Opening XML data files on Mac

In Excel for Mac, the special XML tools are not available.  Instead, you can simply open the XML data file and Excel will interpret it and should provide a table with the data laid out accordingly.

In summary, Excel for Mac will open your XML data file and present it in a reasonable format that you can understand and work with, but it doesn’t provide any special tools to enable you to map it to different parts of your document, revise values and then export it back to XML. as you can with the XML tool package available in Windows.

Below is a screenshot of sales data that was in XML format and then opened into Excel for Mac.  You can see in the table headers that it includes the tags from the XML structure of the data.

Power Query on Mac supports XML

To gain more control and usability of your XML data in Excel for Mac, you can use Power Query.  Just go to the Data tab of the Ribbon, choose Get Data (Power Query), and then choose XML as the data source type as shown in the screenshot below.  Then you’ll be able to choose your XML file:

Then, press ‘Get Data’ and ‘Next’ to load the XML file into the query editor.  You’ll most likely need to expand one or more of the columns to see the data within the XML schema, as shown in the screenshot below.  Once you get the data into Power Query, you can take advantage of its full capability, just as you can with any other data.

We hope you found this topic helpful.  Check back for more details about Excel for Mac and how it’s different from Excel for Windows.