TableWiz Concept

Whilst most Excel Add-ins focus on data, TableWiz instead focuses on metadata. This section will take a look at some basic concepts that underpin TableWiz capabilities and what those unlock for its users. Some users may already be familiar with these concepts, but it’s probably worth a quick read to see exactly how they apply to TableWiz

Metadata

Metadata is usually referred to as “data about data”, or more precisely “data that provides information about other data” (Wikipedia). Whilst there are many different types of metadata at a generic level, in TableWiz we are specifically concerned with the question “what information do we need to capture about a datagrid to be able to fully reproduce a copy of the original in a new target location?”.

Datagrids

TableWiz captures metadata from existing datagrids in Ms Excel.

In Excel, a “Datagrid” can either be a single Range of contiguous cells or an Excel Table (also called “List Objects”). Tables can be referred to by their name. Ranges are referred to by a range address, e.g. “$A$1:$Z$42”.

Excel users set up their data on the worksheet grid in an almost infinite variety of ways, so we’ve come up with some terms to help characterise those layouts into categories. We look at data layouts in Excel as ranging from “Dense” to “Sparse”. This scale looks at the ratio between base data values and additional elements that provide descriptive, analytical or presentation support to the base data.

“Dense” datagrids are arranged as columns or rows of data in a contiguous range of cells that contain only the base data of the grid. There is no consideration given to format or layout, i.e. there are no spacing rows, intermediate headers or footers or subtotals. Dense datagrids typically have named columns or rows

“Sparse” datagrids intermix data with other information and presentation components, add graphics, notes and other media types.

In the initial version, Tablewiz is primarily focused on dense datagrids. That’s not to say you can’t capture metadata from some sparse layouts, but Tablewiz currently won’t capture many of those additional elements.

Other Excel Structures

In future, we are planning that TableWiz will also be able to capture metadata about other common information structures in Excel, for example PivotTables, Charts and PowerQueries.