Data Import & Export

How to Fix Excel Cannot Convert a One-Row XML File into an XML Table

Natalie Taylor Oct 15, 2026 870 views

Question details

The user is unable to format a single-row XML file as a proper XML table in Excel, which results in hidden headers and a missing Table Design tab.

How to Fix Excel Cannot Convert a One-Row XML File into an XML Table

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing an XML file that contains only one row of data into a spreadsheet for analysis or formatting.
Observed behavior: Excel hides the column headers and does not display the Table Design tab, failing to recognize and convert the single row of data into a structured XML table.

Before you start

Ensure your XML file is well-formed and valid by opening it in a standard text editor. It is also helpful to have a sample XML file with dummy data ready for testing the import process.

Solution 1Recommended

Import XML Using the Get Data Feature

Use Excel's Power Query (Get Data) tool instead of opening the XML file directly to enforce structured table formatting regardless of row count.

When you open an XML file directly, Excel attempts to map the XML schema automatically. If there is only one data node (row), Excel may treat it as flat data rather than a repeating table structure.

Using the 'Get Data' feature routes the file through Power Query, which accurately parses single-row XML files and loads them as fully functional tables with visible headers.

Open a Blank Workbook

Launch Microsoft Excel and create a new blank workbook to serve as the destination for your XML data.

Navigate to Get Data

Go to the Data tab on the Excel ribbon, click on 'Get Data' (or 'New Query' in older versions), select 'From File', and then choose 'From XML'.

Select and Import the File

Locate your single-row XML file in the file explorer, select it, and click 'Import'. This will open the Power Query Navigator window.

Load the Data as a Table

Select the appropriate data node in the Navigator preview, then click 'Load' (or 'Load To...' and choose 'Table'). Excel will now display your single-row XML data as a proper table with the Table Design tab available.

Power Query Automation: Once imported this way, you can easily refresh the query if the underlying XML file is updated with more rows in the future.

Solution 2

Temporarily Add Dummy Data to the XML File

Modify the XML file structure to include a second row, forcing Excel to recognize the repeating pattern and generate a table during a direct open.

Free Microsoft Office alternative

Try WPS Office for Your Data Management Needs

If Microsoft Excel's XML import quirks are disrupting your workflow, consider switching to WPS Office. It provides a highly compatible, lightweight, and free alternative for all your spreadsheet tasks, complete with a familiar user interface that makes transitioning effortless.

1. Download and Install: Visit the official WPS website to download the free installation package for your operating system.
2. Launch WPS Spreadsheet: Open the WPS Office suite and click on 'Spreadsheet' to start managing your data.
3. Open Your Files: Import your existing data files seamlessly and enjoy a highly compatible editing environment.

Fully compatible with Microsoft Excel formats including .xlsx, .xls, .csv, and XML dataLightweight installation that runs smoothly on older devicesFree to use with a familiar, tabbed user interfaceSeamlessly manage complex data imports and daily spreadsheet operations

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel hide headers for single-row XML files?

When mapping an XML schema, Excel looks for repeating elements to infer a table structure. A single-row XML file lacks repeating elements, causing Excel to treat it as flat data rather than a structured table, which results in hidden headers.

Can I force Excel to show the Table Design tab for a one-row XML?

Yes. The most reliable method is to import the XML file using Data > Get Data (Power Query) rather than opening the file directly. Power Query correctly parses the data and loads it as a structured table, enabling the Table Design tab.

Is there a way to automate this XML import process for future files?

Absolutely. By importing the XML file via Power Query (Get Data), Excel creates a persistent data connection. You can simply replace the source XML file in your folder and click 'Refresh All' in Excel to automatically update the table with new data.

Excel & Data Problems