Random Access   chris, kp & rob
Toolbox Tip
Import XML into Excel
by kp

Excel offers some powerful tools for managing XML documents. It can assist in creating XML schemas, updating XML data and formatting XML into a readable document. To begin, start with an existing XML document. You can create a simple XML document using Notepad or any other basic text editor. In this example, I've created a simple XML document in Notepad and verified my XML in Microsoft IE. (download sample XML document )

Next, open Excel and create a new blank document. To import the XML document, go to Data -> XML -> Import XML. After you've located the XML document, click Import. Excel sends a prompt as to where to place the data - in cell A1 of the existing sheet or new sheet. Select XML list in existing worksheet.

Import Data

Once the import is complete, Excel arranges your XML document in a list according to the structure of your XML file. Notice that Excel places a blue bounding box around your XML data. Inside this area, you can add or delete rows to your XML by adjusting the border.

List Toolbar

For additional XML controls, go to View -> Toolbars -> List. This menu provides tools to refresh your XML from your source file, import/export XML, etc.

List Toolbar

To view a map of your XML, go to Data -> XML -> XML Source. Excel displays the hierarchical view of your XML and is useful for reviewing your data structure.

List Toolbar

This technique can be useful if you have regular updates to make within a spreadsheet. For example, if you use Excel to update customer information on an invoice, the XML sync can save time with its auto-refresh feature. Plus, you can export the XML to use in other Office applications such as Word to create form letters or mailing envelopes. For more information, refer to the reference articles from Microsoft.

Microsoft: Importing XML into Excel
Microsoft: Using XML in Word

Get Random Access

Understanding Tech

Print this article

Shop Online


Your Name:

Your Email:

Your Friend's Name:

Your Friend's Email:

 © Micro Center