Article ID: 282161 - Last Review: August 21, 2007 - Revision: 2.4

XL2002: Opening XML Files in Excel

View products that this article applies to.

This article was previously published under Q282161

SUMMARY

In Microsoft Excel, you can open and save Extensible Markup Language (XML) files. This article explains how XML files are displayed when opened in Excel.

Back to the top

XML is a meta-markup language that provides a format for describing structured data. XML is a subset of the Standard Generalized Markup Language (SGML) that is optimized for delivery over the Web. It is defined by the World Wide Web Consortium (W3C), ensuring that structured data will be uniform and independent of applications or vendors.

In XML you can define an unlimited set of tags. Tags are used to describe the type of data that is enclosed. The following XML example is a weather report:

<?xml version="1.0" ?>
<weather-report>
   <date>March 25, 1998</date>
   <time>08:00</time>
   <area>
      <city>Seattle</city>
      <state>WA</state>
      <region>West Coast</region>
      <country>USA</country>
   </area>
   <measurements>
      <skies>partly cloudy</skies>
      <temperature>46</temperature>
      <wind>
         <direction>SW</direction>
         <windspeed>6</windspeed>
      </wind>
      <h-index>51</h-index>
      <humidity>87</humidity>
      <visibility>10</visibility>
      <uv-index>1</uv-index>
   </measurements>
</weather-report>

Opening Files in Excel

When an XML file is opened in Excel, Excel looks for a tag for an XML style sheet (XSL). The XSL describe how the data is to be presented. If the tag is present, Excel prompts you with a choice to apply the style sheet or not. If you choose to apply an XSL, the XSL dictates how to present the data, and then the remainder of the information in this section does not apply.

If there is no XSL tag, or you choose not to apply the XSL, the first tag of the element is used like a title, and is put into cell A1.

The rest of the tags are sorted alphabetically and put across the second row. The example above would sort into the following list:

/area/city
/area/country
/area/region
/area/state
/date
/measurements/h-index
/measurements/humidity
/measurements/skies
/measurements/temperature
/measurements/uv-index
/measurements/visibility
/measurements/wind/direction
/measurements/wind/windspeed
/time

If there is one set of data, the data for each of these columns are put on the third line of the spreadsheet. If the XML data contains duplicate fields inside an element, Excel spreads the data over multiple rows. Each row contains one unique set of data. This can cause a small set of data in XML to be displayed as a larger number of rows in Excel.

For additional information on saving in XML format with Microsoft Excel, click the article numbers below to view the articles in the Microsoft Knowledge Base:

287739 (http://support.microsoft.com/kb/287739/EN-US/ ) XL2002: Error Message: The Following Features from Your Workbook Will Not Be Saved in the XML Spreadsheet

287734 (http://support.microsoft.com/kb/287734/EN-US/ ) XL2002: Excel Saves XML Files in the XML Spreadsheet Format

Back to the top

REFERENCES

For more information on XML, see the Microsoft Developer Network XML Home or the XML Software Developer's Kit Web site at:

http://msdn.microsoft.com/xml/default.aspx (http://msdn.microsoft.com/xml/default.aspx)

http://msdn2.microsoft.com/en-us/xml/default.aspx (http://msdn2.microsoft.com/en-us/xml/default.aspx)

Back to the top