Feature Summary

With TableWiz you can:

  • Capture details of tabular datagrids – Excel tables (aka Lists) and Ranges – in Excel ® worksheets including column headers, format, formulas, validation (coming soon), and format (coming soon).
  • Capture and create multiple views of data grids
  • Clean existing datagrids by finding invalid references in validation formulas and regular formulas
  • Create the standard version of a data grid (range or Excel table) from saved grid “metadata”
  • Create a multi-worksheet workbook in one function / action / step
  • Edit the metadata in a text editor, in an Excel workbook, or in the visual editor (coming soon)

We’re developing and releasing new features regularly.

Capture details of tabular datagrids

Capture the grid’s structure, including column details (title and datatype), source details (worksheet and range address or table name, format (column width and alignment) and formulas.

Stop the table structure drift and the copy + paste bloat! Build a library of standard tables.

If you’re using tables frequently for your work or application area, you’ll know that worksheets can get bloated and slow from repeated “copy+pasta” operations, or slowly diverge from a standard and validated structure. Local optimisations abound and with them errors both explicit and hidden. Create your application tables from scratch each time you start a new project or task and avoid these problems.

Create datagrid from metadata

From a saved “schema” of datagrid metadata, create a new instance of the range or table. Optionally include ‘dummy’ data.

Many business environments use Excel to capture and manage data in the same structure and format, over and over again.  An example might be a project: each project creates the same tables all the time: issues, risks, decisions, actions, deliverables, stakeholders, RACI and so forth.  Another example is a regular download of data from another system, which is set up for translation, mapping, and analysis.

The usual method for propagating tables is to copy a version of the workbook and start.

But this is fraught with problems.

  • variation / modification from standards due to local optimisations and corner-cases, like extra columns, lookups, validations, and formula changes. if teams where many people are using the same basic tables and modifying them, the number of variant formats grows and continues to grow.
  • workbook “bloat” such as Names that are copied inadvertently from one worksheet to another and pivot tables and other elements of excel that add bumf to the workbook
  • formula errors that are copied from one version to the next and the next

TableWiz helps you create the same version of your datagrid in workbooks, over and over.

Capture and create multiple views of datagrids

The TableWiz metadata files contain a minimum of a single view (the physical table), but you are not limited. You can create multiple views from the same set of columns, with different metadata, e.g. different column Titles for the same column name, a subset of columns or different orders, widths even different formulas.

TableWiz automatically creates a second view if it finds any hidden columns in the source data. The second view contains only visible columns.

Create an “Application Workbook” in one function

An “Application Workbook” is a workbook with a set of tables and worksheets designed to work together on a single “application”, e.g. a project workbook for Project Managers. Tablewiz allows you to select the relevant metadata and create all the tables in one go.

TableWiz allows you to create an entire new workbook of multiple worksheets and data grids in one operation! Create a template workbook as the target “container” and splice in the standard versions of data grids at the start of each project.

Create and maintain a standard version of a datagrid

TableWiz helps you consolidate improvements from multiple sources into one standard version that multiple users can use to create their workbooks.

Local optimisation and enhancement of standard workbooks is good if it creates new capabilities or innovations. But the problem then arises of how to capture and re-use various innovations and distribute them around the group like a team or even an enterprise. Often there will be one or two different variants in multiple worksheets in different stages of evolution.

TableWiz enables you to consolidate and rationalise these innovations into a new master version. Refer to the section below regarding “Edit and document the metadata”.

Edit and document the metadata

The schemas are text files that contain all the information needed to recreate the data grid. These can be augmented by editing the file in a standard text editor.

TableWiz also helps you create reference documentation for datagrids by creating a table of attributes called “Doc Table” which lists out all the details of the field attributes. This DocTable can be edited and the changes re-created as an updated version of the schema do you can create self-documenting datagrids. For example if you add or edit the column “Description” this text will be added to the column header as a note.

Navigating and using datagrids

Validation: when you’re using a table, how can you validate that it has all the right columns you need and that the formulas are correct when compared with a company standard or process reference.

Views: when you’re working with large tables, it’s useful to be able to hide and unhide groups of columns. Excel provides a grouping and outlining feature to help with that but it’s tricky to set up, and only works on contiguous columns and rows. TableWiz allows you to apply

Benefits

TableWiz provides these benefits to all users of datagrids in Excel, but especially to intensive users.

  • Generate a new worksheet (or even an entire workbook) as a fresh clean baseline, with known properties (columns, formulas, validation etc)
  • Start a new task with a brand-new datagrid without stray formulas, names, inconsistent column structures etc
  • Focus on the data and less on the structure
  • Select from a range of pre-built table schemas, or capture an existing table schema from an exemplar worksheet
  • Create (or generate) standardised pivot tables based on the known table structure
  • Input data into external tools (such as Tableau) and be confident that the data is set up correctly