In the heat of daily battle urgent needs arise and the solution seems obvious: quickly throw up a spreadsheet and put the data in it. There is nothing wrong with that. There is very little effort to execute, and if after the hot action that spreadsheet can drift back into history, we have scored a victory for the day.
The enforced structure of ordered rows and columns immediately creates a sense of order. The gratification of applying arithmetic functions to masses of rows and columns can be nearly sensuous when compared to older manual solutions.
Where this becomes a problem is when the solution to an immediate problem becomes the go-to solution tomorrow. Someone turns up a derivative requirement (“Hey, I think I have an Excel [sic] that will handle that”) and, taking a spade to settled dirt, exhumes the cadaver.
Perhaps in doing this one can refine what had been done and sublimate the earlier creation to something at a higher plane. But there is a dark side and the result becomes a member of the odious undead, wrenched back into spasmatic animation now shuffling around the office.
If we were looking for the higher solution, we might not be using a spreadsheet. But in the real world real people need to solve problems with the tools and skills available and cannot tolerate the time to research and deploy the more correct solution.
Here are some thoughts to provide guidance to quickly build a useable spreadsheet model that will not develop into an unruly child or true monster. Some of these ideas are derived from loftier principles of database theory, but as is true of solid and useful theories they make sense and have actual use to mere mortals.
The Basic Rules
More details about these rules follow.
- Put raw data, and only that data, on its own worksheet.
- Only put one kind of data on its own worksheet.
- Put summarizations and other analysis each on their own worksheet.
- Charts and other visualizations should be on their own worksheets, not combined with the raw or aggregated data.
More Details
- Put raw data, and only that data, on its own worksheet.
Avoid adding any totaling cells or rows in the data worksheets. Exception: put totals or other summarizations in unique columns or rows. It is much, much, much easier to keep your formulas straight by doing this.
Adhere to a simple form of rows that represent things (transactions or objects) and columns that only contain data of the same type, e.g. daily sales, unit cost, number of attendees, etc. You will save yourself enormous problems in the future.
Excel does allow you to wrap text data to multiple rows within a cell. That’s OK to make things readable but don’t fall for stuffing multiple distinct data into the same cell. Bad example: A cell in a column called “Phone” which contains a mobile phone and a work phone. Good Example: Name a column “Mobile” and another column “Home” or “Other” and keep the data separate.
A caveat, if you are using formulas, to avoid problems later is to consider what will happen if you have to go back and insert rows in an older area. Are there formulas that will get knocked out by that rearrangement?
- Only put one kind of data on its own worksheet.
As an amplification and refinement to the above, if you have multiple sets of data with different columns, put each set on its own worksheet. Avoid mixing up data with different column structures on the same page. For example, do not put a list of contacts on the same sheet as a list of transactions of sales data. You will find both lists easier to maintain if kept separate. You will also find it much easier to build analysis and summarizations later because they can be built on the most simple elements.
- Put summarizations and other analysis on their own worksheets.
Particularly with large sets of data, e.g. volumes of sales records over a period, you can have a requirement for multiple groupings such as monthly, quarterly, annual, etc. Refrain from mixing these together: put the monthlies only on their own sheet using linked formulas to extract the sum or other function results. Linked formulas are simple to add, and you never then have the problem of multiple sources of raw data. Never copy a calculated result to a new sheet by writing it in as a value. Use a link to bring the value forward. If you have to revise any of the raw data the summarized values will recalculate automatically.
If you ever have to modify raw data in the future, you will be thankful that you only have to update it in one place.
Excel permits you to create as many sheets in a workbook as you want. There is a technical maximum but it is unlikely you’ll ever get close to that. Don’t be stingy with them…you are not saving any trees or enlarging your carbon footprint.
- Charts and other visualizations should be on their own worksheets.
Resist the urge to complicate any of the raw data or simple summary worksheets with colorful eye candy, instead, put these objects own their own sheets. You can even create a single or multiple dashboard type of worksheet with charts, graphs and linked tables with colored highlights exposing underlying data.
The Reasons Why
At first, following this discipline appears to add work to obviously simple tasks. However, what you have done is isolate data into discrete areas. When the (inevitable) requests come along to amplify your original work, you will find you only have to make relatively small changes. You have completely separated your data sources from the presentation. You can make changes to the data source without upsetting the presentation and likewise, you can add much more freely to the presentations without molesting your underlying data.
The purpose of these guidelines is to help steer one away from committing “easy” tricks that cause severe problems later. I look forward to hearing any successes in using them.
###
Mark Thomas is a Data Solutions Master. He is always happy to receive inquiries on any topic. He can be reached here.
