Normalize data: from Userfriendly to Pivotfriendly

Normalize Featured

If possible I always use pivottables to create reports in Excel. Pivottables need well formed, normalized data tables as source, which are usually not suitable for data entry. In this post I describe two techniques to bridge the gap between userfriendly and pivotfriendly data structures.

Pivottable data connection models

External Data

The way a datasource connects to a PivotTable depends on the datasource. In this post an overview of the six connection models that you can create by means of Excel’s userinterface.

Excel and XML: four observations and two bugs

XmlObservationsAndBugsFeatured

I have been working with Xml files in Excel for some time now, and thought it is time to share some observations.

Create Xml from Schema

featured

In this post I describe a tool to create an Xml file from a schema (Xsd) using VBA and the DOM and SOM objects of the Microsoft XML library MSXML version 6.

Get XPaths from workbook – update

XPath Featured

In Get XPath objects from Excel workbook I described a method to retrieve all mapped XPaths from a workbook. In this post I describe another method, using the Worksheet.XmlMapQuery function. It’s simpler, and faster.