May 23, 2021

7 Golden Rules for your Data Sheet

David Brown

Data capture is where it all starts and I am afraid that is where it starts getting wrong.

Most inefficient systems and processes I see can be traced to a poorly thought out data collection process. So you end up spending so much time cleaning up the data mess before any meaningful analysis can be done.

Worse, because you have wasted time on data prep, you don’t have time to dig into what the numbers are really saying!! And then the vicious cycle restarts for the next reporting period.

At dbrownconsulting, we devised a simple set of rules for you to know when your data needs work, we call it the 7 golden rules of data.

Let’s see how it works.

Rule 1. One Row of Headings

Each column of your table must have a descriptive heading typed out in the first row. If your heading is so long, just wrap the cell, do not type the heading in two rows.

Rule 2. No Empty Columns

Many confuse empty cells with empty rows, you can have empty cells in your data (not great for analysis though) but, you must not have completely empty columns. If you have headings for each column in your data, then this means you do not have any empty columns.

Rule 3. No Empty Rows

Just as above, you must have content in at least one cell of each row. Never have a completely empty row, that is usually something to make your report look great, but we are not talking about reports here, we are talking about the raw data that makes up the report.

Rule 4. All Dates must be in a Single Column

Dates are probably the most important data in your tables, they allow you to generate all sorts of time intelligence calculations (YTD, MTD, date Comparisons etc). And here is where most people get it wrong, in your reports, dates can be anywhere (rows, columns, etc, but in your data table you must have them in a SINGLE COLUMN.

This is probably the rule break that wastes the most time to correct.

Get t right the first time. remember this is the dataset and not the report.

Rule 5. Every Unique Data must have its own Column

Look critically at every column, is the content the same context? In our reports we tend to stagger information to save space, so column B could have a Continents name and directly below it in the same column, you have Country names, then after a few more rows, another continents name, then another set of countries.

This Column, column B, in effect has two unique data types (Continent and Country) and it will be impossible to analyse the data effectively, there should be two columns for these two data sources.

Rule 6. No Totals or Subtotals anywhere in your Table

Totals and subtotals belong to your reports. It is the values in your dataset that get summed up to totals. Your data must not have totals, just the real raw values from source.

Rule 7. No obstructions around your data

DO NOT type comments at the end of your data or insert a total row at the bottom, or type something directly above your headings. All these examples are obstructions that break the rule.