The simplest way to open XML in Excel

The fastest way to open an XML file in Excel is to use the File > Open menu. Navigate to your XML file, select it, and click Open. Excel will read the file and display its contents in a spreadsheet format, converting the XML structure into rows and columns.

This method works for most straightforward XML files. Excel automatically parses the tags and nests the data so you can see it as a table. If the file opens but looks jumbled or has extra columns you don't need, the XML structure itself may be complex — but the data is still there and readable.

If you're on Windows, you can also right-click the XML file, select Open with, and choose Microsoft Excel from the list. On Mac, use the same File > Open path from within Excel itself.

Key Takeaways

  • Excel opens most XML files directly through File > Open without any special setup or conversion.
  • Complex XML structures with nested data may display across multiple columns or sheets, but the information is still accessible.
  • If Excel doesn't recognize the file format, you can manually change the file extension from .xml to .txt, open it, and use the Text to Columns feature to parse the data.
  • XML files opened in Excel are read as data only — you cannot edit the original XML structure within Excel itself.

When Excel opens the XML automatically

When you open an XML file in Excel, the program looks at the tags and converts them into column headers and rows. Simple, flat XML structures work best — for example, a file with customer names, addresses, and phone numbers will appear as three columns with each record as a row.

Excel creates a new sheet for each major section of the XML if the file has nested or repeating elements. You'll see multiple tabs at the bottom of the window, one for each section. This is normal and makes it easier to navigate complex data.

The original XML file is not changed. Excel is simply displaying the data in spreadsheet form. If you save the file after opening it in Excel, you'll be asked whether you want to keep it as XML or convert it to an Excel format like .xlsx.

What to do if the XML file won't open

If Excel doesn't recognize the file or shows an error, the XML may have formatting issues or an unusual structure. First, check that the file extension is actually .xml. Some systems hide file extensions, so you may need to look at the file properties to confirm.

If the extension is correct but Excel still won't open it, try renaming the file extension from .xml to .txt temporarily. Open the .txt file in Excel, and the data will appear as plain text. Then use the Data > Text to Columns feature to split the content into columns based on the XML tags.

Another option is to open Excel first, then use File > Open and set the file type filter to "All Files" instead of just spreadsheet formats. This forces Excel to attempt opening the XML regardless of how it's categorized.

Opening XML files with the XML Source pane

Excel has a built-in XML Source pane that lets you map XML elements to specific cells. This is useful if you want precise control over where data lands in your spreadsheet. To access it, go to File > Options > Trust Center > Trust Center Settings > Trusted Locations and add your XML file location, or open the file and look for an XML Source button in the Data tab.

The XML Source pane shows the structure of your file as a tree. You can drag elements from the tree onto your spreadsheet to place them exactly where you want. This method takes more time but gives you control over the layout.

Most users don't need this feature. The automatic import works fine for everyday use. Use the XML Source pane only if you're working with the same XML structure repeatedly and want a consistent layout each time.

Saving and converting XML files in Excel

When you open an XML file and make changes, Excel will ask what format to save in. If you want to keep it as XML, choose File > Save As and select "XML Spreadsheet" or "XML Data" from the file type dropdown. The exact option depends on your Excel version.

If you save as .xlsx (Excel's native format), the file becomes a standard spreadsheet and loses its XML structure. You can always open the .xlsx file again, but you won't be able to use it in systems that expect XML input.

For most purposes, save as .xlsx if you're done working with the XML structure. Save as XML only if another program or system needs to read the file in its original format.

Common problems and how to fix them

If your XML file opens but shows strange characters or symbols, the file may use a character encoding that Excel doesn't recognize immediately. Try opening it with Notepad first to see the raw content. If you see garbled text there too, the file itself may be corrupted.

If the data appears in a single column instead of multiple columns, the XML tags may not be structured the way Excel expects. You can still use Text to Columns to split the data manually, or copy the content into a text editor, clean it up, and reimport it.

If Excel opens the file but shows it as read-only, check the file properties on your computer. Right-click the file, select Properties, and uncheck the "Read-only" box if it's selected. Then try opening it again.

Frequently Asked Questions

Can I edit the XML structure inside Excel?

No. Excel displays XML data as a spreadsheet, but it doesn't let you edit the XML tags or structure directly. If you need to modify the XML itself, use a text editor or XML editor instead. You can edit the data values in Excel and save it back as XML, but the tag structure stays the same.

What if my XML file is very large?

Excel can handle large XML files, but performance may slow down if the file is several megabytes or larger. If the file is extremely large, consider splitting it into smaller pieces or using a dedicated XML tool. You can also open it in Excel and save specific sections as separate files to work with them more easily.

Will opening XML in Excel change the original file?

No. Opening a file in Excel does not modify the original unless you explicitly save over it. Excel creates a copy in memory. If you want to keep the original unchanged, save your work as a new file with a different name.

Can I import XML data into an existing Excel spreadsheet?

Yes. Open your existing spreadsheet, then go to Data > Get External Data > From Other Sources (or similar, depending on your Excel version) and select your XML file. Excel will import the data into your current sheet or create new sheets as needed.

What's the difference between opening XML and importing it?

Opening an XML file creates a new spreadsheet from that file. Importing brings XML data into an existing spreadsheet. Both methods read the XML structure and convert it to rows and columns, but importing gives you more control over where the data lands.