How to Open an XML File in Excel

Excel can open XML files directly, but the way it reads them depends on the file's structure

XML files are plain-text documents with tags that organize data — they look nothing like a spreadsheet, but Excel can import them if the data inside is arranged in rows and columns. When you open an XML file in Excel, the program tries to guess how to display the information. Sometimes it works perfectly. Sometimes Excel shows you a dialog asking how you want the data structured. Sometimes the file opens but looks nothing like what you expected.

The outcome depends on whether the XML file was built to be spreadsheet-friendly. A well-designed XML file will open into rows and columns automatically. A poorly structured one might open as a single column of text, or not open at all. You can also convert XML to a format Excel prefers, like CSV, before opening it — that step takes a few minutes and often produces cleaner results.

Key Takeaways

Open an XML file in Excel by going to File > Open, selecting the file, and letting Excel decide how to display it or choosing a structure from the import dialog.
If the XML file opens as a single column or looks wrong, the file's structure does not match what Excel expects for a spreadsheet.
Converting an XML file to CSV format before opening it often produces better results, especially if the original file is complex.
You can also use Excel's built-in XML tools to map data to specific cells, but this requires understanding the file's tag structure first.

The simplest way: File > Open and let Excel handle it

Start by opening Excel and going to File > Open. Navigate to the folder where your XML file is stored, then select it. If you do not see XML files in the list, change the file type dropdown from "Excel Files" to "All Files" so the XML file appears.

Click Open. Excel will either display the data in rows and columns right away, or it will show you an XML Source dialog. If a dialog appears, you will see options for how to structure the import. The simplest choice is usually the default option — just click OK and see what happens. If the result looks wrong, close the file without saving and try a different approach.

When Excel shows the XML Source dialog

The XML Source dialog appears when Excel detects that the file has multiple possible ways to be organized. You will see a list of elements (the tags inside the XML file) on the left side. The dialog is asking you to choose which elements should become columns in your spreadsheet.

If you see a single root element listed, click the plus sign next to it to expand and see the data tags inside. Drag the tags you want as columns into the empty area on the right side of the dialog. For most files, dragging the main data element (often called "row" or "record") into the right panel will create a table automatically. Click OK when you are done.

If this feels confusing, it is because XML files are not designed the same way spreadsheets are. The dialog is trying to translate between two different languages. If the result still does not look right, close the file and try converting it to CSV instead.

Converting XML to CSV for easier opening

CSV (comma-separated values) is a format Excel loves. If your XML file is not opening cleanly, converting it to CSV first often solves the problem. You do not need special software — you can do this with a text editor and a few minutes of work, or use an online converter.

To convert manually, open the XML file in Notepad (right-click the file, select "Open with", choose Notepad). Look at the structure: you will see opening tags like <name> and closing tags like </name>. Each piece of data sits between a pair of tags. Write down what each tag represents, then create a new file in Notepad with the data arranged as comma-separated values — one row per record, with commas between fields. Save it as a .csv file and open it in Excel. The data will appear in columns automatically.

If the XML file is large or complex, use an online XML-to-CSV converter instead. Search "XML to CSV converter" and paste your file's contents into the tool. read the CSV result and open it in Excel.

Using Excel's XML mapping for precise control

If you need to place specific XML data into specific cells, Excel has an XML mapping feature. This is more advanced and requires you to understand the XML file's structure, but it gives you exact control over where each piece of data lands.

Go to File > Options > Trust Center > Trust Center Settings > Trusted Locations. Add the folder containing your XML file to the trusted list. Then go to Developer > Source (if you do not see Developer in the ribbon, go to File > Options > Customize Ribbon and check the Developer box). Click XML Maps, then Add, and select your XML file. Excel will show you the file's structure. Drag elements from the structure panel into the cells where you want them to appear. This approach works well if you are importing the same XML file repeatedly and want the data in the same place each time.

Why your XML file might not open the way you expect

XML files can be structured in many different ways. A file designed for a database might have deeply nested tags that do not translate to a flat spreadsheet. A file designed for a website might have formatting tags mixed in with data tags. Excel can only work with files where the data is organized in a clear, repeating pattern.

If your XML file opens as a single column, or if most of the data appears in one cell, the file's structure is not spreadsheet-friendly. The CSV conversion method usually works in these cases because you are extracting just the data you need and arranging it yourself. If conversion is too time-consuming, ask whoever created the XML file whether they can export it as CSV or Excel format instead.

Checking if your XML file is valid before opening

A broken XML file will not open properly in Excel, even if the structure is otherwise good. XML files must follow strict rules: every opening tag needs a closing tag, special characters must be escaped, and the whole file must have a single root element wrapping everything else.

To check if your file is valid, open it in Notepad and look for obvious problems: mismatched tags, text outside the root element, or special characters like & or < that are not escaped. If you spot errors, you can fix them in Notepad and save. If the file looks correct but still will not open, try opening it in a different program first — some XML editors will show you exactly where the error is.

Frequently Asked Questions

Can I edit the XML file directly in Excel?

Excel is not designed for editing XML. If you open an XML file, make changes, and save it as .xlsx, you lose the XML structure. If you need to edit XML data, edit the file in Notepad or an XML editor, then re-import it into Excel. If you only need to edit the data once it is in Excel, open the XML, make your changes, and save as .xlsx — you will have a spreadsheet, not an XML file anymore.

What if Excel says the file is corrupted?

This usually means the XML file has a structural error — a missing closing tag, an unescaped special character, or something similar. Open the file in Notepad and look for mismatched tags. You can also try opening it in a web browser; most browsers will show you where the error is. If you cannot fix it, ask whoever created the file to provide a corrected version.

Can I open multiple XML files at once in Excel?

You can open them one at a time in separate Excel windows, but Excel cannot merge multiple XML files into a single spreadsheet automatically. If you need to combine data from several XML files, convert each one to CSV, then use Excel's Data > Get External Data feature to import them into separate sheets or consolidate them manually.

Why does my XML file open with all the data in one column?

The file's structure does not match what Excel expects. XML files designed for databases or websites often have nested tags that do not translate to spreadsheet columns. Try converting to CSV instead, or use the XML Source dialog to manually select which tags should become columns.

Is there a way to automatically update Excel when the XML file changes?

Excel can refresh data from an external XML file if you set up a data connection, but this requires the file to be in a specific location and the connection to be configured correctly. For most users, it is simpler to re-import the XML file when it changes. If you need automatic updates, ask your IT department whether they can set up a data refresh schedule.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.