Convert XML Data Files to Excel: Two Simple Ways

Excel's Power Query tool can be utilized to transform XML data before saving the file in the XLSX format. In addition to files already saved on your computer, XML files from the web can be imported if you're using Windows. However, if Microsoft Excel is not installed on your PC or Mac, the contents of an XML file can be copy-pasted from a text editor into a web-based converter.

To import an XML file, open a new blank workbook in Microsoft Excel. You can find Excel in the Start menu for Windows or in the Applications folder for Mac. Click on Blank workbook in the welcome screen to start fresh. Click on the Data tab located at the top of Excel. Then, click on Get Data icon situated on the left side of the toolbar at the top of Excel for Windows.

To select an XML file from your computer or connected network and cloud drives, choose From File (Windows) or Text/CSV (Mac). If you're using Windows, instead of choosing From File, select From Other Sources > From Web, enter the URL to the XML file, and click OK. However, on Mac devices, you will have to make sure the XML file you want to convert is already saved on your computer as you cannot open XML files from the web in Excel.

Next, select From XML (Windows only) to open your file browser. Click on the XML file and click on Import (Windows) or Next (Mac). The Navigator window will open. Select the XML table name and click Load. Choose the file name that has a table icon to its left as you can preview the data from that table. By clicking Load, the data from the XML file will appear on a new sheet in your workbook.

For Windows devices, if you want to modify the data, such as column headers, click on Transform Data instead. Make your changes in the Power Query window and click Close & Load at the top-left corner to add the data to a new worksheet. On Macs, click on Load first if you want to modify anything about the XML data. Afterward, launch Power Query by clicking the down-arrow next to the "Get Data" icon in the toolbar and selecting Launch Power Query Editor… Make changes as necessary, then click Close & Load on the Home tab.