From the Not Just Numbers blog:
“Learn the fundamentals of the game and stick to them. Band-Aid remedies never last.”
When someone comes to me with a problem in an existing spreadsheet, the problem is invariably in the layout of the data. The spreadsheet is built for one purpose and works OK for that until something slightly different is required and it proves almost impossible to get the report that’s needed.
If a few simple rules are followed when laying out your data, then producing additional reports from that data, and using it for different purposes, becomes simple, instead of the nightmare it is for many users.
These rules apply to any lists of data, be it monthly financial information, transactional data (such as lists of sales, purchases, payments or receipts), customer or supplier lists. If you are going to store data in your spreadsheet to produce reports from, you need to follow these rules.
At the heart of these rules is the approach – you are not laying out your final report here, you are laying out the data in a format that can be reported from! These are two very different things (see my OAP approach to reporting in Excel).
The rules to follow:
- Columns with headings and no gaps
- Every column should have its own UNIQUE heading, in the first row;
- There should be no empty columns;
- These columns represent the fields of a database, e.g. Customer Code, Customer Name, Telephone Number, Email Address, etc.
- One row per record and no gaps
- Every record should have all of its data on one row. E.g. in the above example, one row per customer;
- There should be no empty rows;
- Don’t group data by putting it in different columns (THIS IS THE ONE THAT ALMOST EVERYONE GETS WRONG)
- Don’t split out financial or numerical data into separate columns to categorise the data into months, expense categories, customers, agents, etc.
- Do have one column for the financial or numerical data and create a column for month, expense category, customer or agent, to categorise each row;
- You can use data validation drop-down lists to select the appropriate category for each row;
- This one is counter-intuitive because in any report, you will almost certainly will want a column (or row) for each of these categories – but if you do this in the data you will massively restrict what you can do with it.
- Data following the rules above is perfectly prepared to be analysed using countless tools within Excel, for example: pivot tables, autofilter, SUMIF, COUNTIF, etc.
- Most changes to the data don’t require a change to the data layout. New categories, e.g. expense categories, customers, agents, etc. can just be added to the drop-down lists. Any new entries in these columns will be automatically picked up by pivot-tables, autofilter, etc. with no work involved.If you had to create a new column each time, you would also need to edit every report that used the data.
- You can choose to analyse the data by any category you want. It takes seconds to edit a pivot table that has a column for each month and change it to a column for each expense category. This is almost impossible if the data was laid out in those columns.
- You can add additional category columns to the data if needed and these can even be calculated from the data. You might, for example, introduce departments – simply add a department column to the raw data, and your pivot tables can analyse the data by this category as well, or instead of existing categories.