Viewing an XML file in Excel

Applies to
Microsoft Excel 2002

You can work with any well-formed Extensible Markup Language (XML) file in Microsoft Excel. (This means any file that is structurally correct according to the XML standard.) Microsoft has also defined the XML Spreadsheet (XMLSS) format designed specifically for Excel worksheets. This means you can:

  • Open any well-formed XML file.
  • Create Web queries to well-formed XML data sources.
  • Save an entire workbook in the XML Spreadsheet (XMLSS) format.

This article explains how an XML file is displayed when you open it in Excel.

What is XML?

XML is a method for putting structured data (such as information in a worksheet) into a text file that follows standard guidelines and can be read by a variety of applications. You can create customized tags in XML so that data can be defined, transmitted, validated, and interpreted between applications and between organizations.

XML tags describe the data in the text file (for example, specific text strings such as university names might be described as "name" as in the example below). Unlike Hypertext Markup Language (HTML), though, XML tags do not specify formatting or what the data should look like when viewed on screen. XML describes the content of a Web document, whereas HTML tags describe how the document looks. In XML you can define an unlimited set of tags.

Sample XML

The following XML example is a list of universities, including name and location. This is an example of the way XML appears when you open an XML file in a text editor:

<?xml version="1.0" ?>
<universities>
  <university>
    <name>UCSB</name>
    <location>Santa Barbara, CA</location>
  </university>
  <university>
    <name>University of Texas at Arlington</name>
    <location>Arlington, TX</location>
  </university>
  <university>
    <name>USM</name>
    <location>Hattiesburg, MS</location>
  </university>
</universities>

What is a style sheet?

A style sheet is a well-formed XML document that uses a special set of instructions to transform the information in the XML file. If a style sheet is applied to an XML file, the style sheet controls the formatting or the way the XML file is displayed. The standard for XML style sheets is called Extensible Style Language (XSL).

How does an XML file appear in Excel?

When you open an XML file in Excel (assuming you choose not to apply any linked style sheets), the XML file is "flattened" or transformed into a two-dimensional worksheet made of rows and columns. The XML tags appear as column headings, and the data appears in rows below the appropriate column headings. XML files are opened as read-only in Excel so that you don't save your original source file in the XML Spreadsheet (XMLSS) format.

When you open an XML file that is linked to a style sheet, you can either open the file without applying the style sheet or apply a specific style sheet (if the file is linked to more than one). If you choose to apply a linked XSL, the XSL determines how the data is presented, and the example below does not apply.

If there is no linked XSL or you choose not to apply it, the first element (the root node) is used like a title and is displayed in cell A1 (/universities in the example below). The rest of the tags are sorted alphabetically across the second row. The sample XML shown earlier appears like this when opened in Excel.

/universities
/university/location /university/name
Santa Barbara, CA UCSB
Arlington, TX University of Texas at Arlington
Hattiesburg, MS USM

The data for each of the tags appears on the third and following rows of the spreadsheet. Each row contains one unique set of data.

Encountering an error message

XML is a structured language. Well-formed XML must have both a start tag and an end tag, and it must be authored using the proper syntax. For Excel to recognize and open an XML file from a non-Excel source, the file must contain the <?xml version="1.0"?> tag and also be a well-formed XML document. If this tag is missing, the file is not recognized as XML and is opened as a text file in Excel.

When you open an XML file in Excel that is not well-formed, you receive an error message explaining why the file cannot be opened, sometimes including the line and position of the error found. For example:

  • File cannot be opened because:
    The following tags were not closed: tagname
  • File cannot be opened because:
    A string literal was expected, but no opening quote character was found.
    Line x, Position y.
    <tagname attributename=value1>value2</tagname>

When the error is corrected, you can successfully open the file in Excel.

More information

Features and limitations of XML Spreadsheet format

Getting data fom the Web in Excel

XML on the Microsoft Developer Network (MSDN)

INFO: Microsoft Excel 2002 and XML (Q288215)

Transform XML Files with XSLT When Importing into Microsoft Excel 2002

XL2002: Excel Saves XML Files in the XML Spreadsheet Format (Q287734)