Excel 2010: Working With XML Format

Excel has an inherent quality of working with XML file even if it doesn’t comply with universally accepted  standards, XML file may contain data in incompatible formats, which eventually begets the problem of exchanging data between systems over the internet. Converting data in to XML can mitigate the probable of encounter any incompatibility problem. XML is use to store data in files or in databases, and through Excel 2010 you can retrieve data from XML files for performing advance operations over it.

XML is made up of well structured table, it contains disparate data with parents and child nodes disperse in the document. XML supports user-defined tags, so there are no rules or constraints to follow. To get you start with it, first we will look at the very simple XML file(without schema reference).

It contains simple tag which encapsulates user data, as there are no constraints or rules applied, so you can write your own tags. We want to include CATALOG of CD collections in to Excel worksheet as shown in the screenshot below.

cd xml

Now launch Excel 2010, open a worksheet in which you want to open XML file, from File menu, click Open.

open 1

Upon click a dialog will appear asking you to choose an XML file, select the file and click Open.

choose file

You will reach a dialog Open XML, select the way you want to open XML file, you can open it as read only, as XML table, and as ‘using the XML Source task pane’, as we want to open it as a XML table so we will select this option, and click OK.

open xml


If your XML file does not refer to schema, then Excel will create it for you. As we are opening a simple XML file without any schema reference, which contains basic XML tags so will click OK.

excel dialog

Now you will see the XML file in the datasheet with default settings of Excel, as shown in the screenshot below.

xml

Now in Excel you can do a a lot with XML file, you can search, sort and filter data, apply formulas, condition, and rules over desired portion of data, analyze it with different types of graph, charts, sparklines, and make a SmartArt graphic of data for better understanding. In fact Excel has made XML file editing much easier.

If you want to filter down the XML file, from cell’s drop-down button select desired options. If we want to view the albums from specific year of release, we will simply enable only the specific check box as shown in the screenshot below. you can also sort out the data from the drop-down context menu. Click OK to see the results.

filter

Here you can see the data is filtered now as we required.

1971

On saving the document, specify the XML file name. On opening XML file you will notice that it now contains schema reference as shown in the screenshot below.

schema enabled 1

You can check out previously reviewed guides on Difference between Absolute & Relative Referencing & How to split columns.

Advertisement
  • Nacho Momma

    Excel 2010 does not always open xml. It usually FAILS. Like Miley Cyrus singing Nirvana.

    • Ohen

      Hilarious and true!

  • mvanderaa

    Check the spelling in the article. Seems unprofessional

  • Androider

    It’s not really a problem with spelling. He just has some incorrect grammar which is probably a result of English not being his first language.

  • Mary Ann

    I was hoping to learn how to make a file format correctly using an XML schema. I keep looking!