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.
Now launch Excel 2010, open a worksheet in which you want to open XML file, from File menu, click Open.
Upon click a dialog will appear asking you to choose an XML file, select the file and click Open.
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.
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.
Now you will see the XML file in the datasheet with default settings of Excel, as shown in the screenshot below.
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.
Here you can see the data is filtered now as we required.
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.
You can check out previously reviewed guides on Difference between Absolute & Relative Referencing & How to split columns.