I just received a dashboard report that I also need to discuss. So for the next few weeks I’ll turn into a spreadsheet critic.
Splattered Data
Imagine dropping a can of paint onto a parking lot from a 50-story building. Every car in the lot would be splattered. Randy’s workbook does the same thing with its data. So do many other workbooks I’ve seen.
The workbook has more than a dozen report pages. Most of them contain manually entered, raw data intermingled with formulas. And both the data and the formulas have been carefully formatted for presentation. This design approach can be big a problem for several reasons:
—If your raw data is splattered all over your workbook, your data is virtually impossible to update accurately or quickly.
—Your raw data often must be transformed in many ways before it can be presented. For example, you might need to change its unit of measure, scale it, apply adjustments, and so on. However, if you change the raw data itself, in random places around your workbook, the data no longer matches the source. This makes error-checking virtually impossible.
—Changing the structure of your report — your output — also changes where your raw data can be found. This turns the updating process each period into an Easter Egg Hunt.
—It’s a lot of work to format, display, and transform raw data that’s splattered all over your workbook!
—Etc.
Here are a few ways these problems can affect your career:
—Poor workbook design can cause you to spend a lot of time to update your reports and analyses. So you’re not nearly as productive as your co-worker who uses more efficient techniques.
—Similarly, poor design can cause you to take a lot longer than others would when your boss asks you to change a report.
—Poor design tends to produce more errors.
—Poor design makes it more difficult for you to explore your data and find hidden problems and opportunities that your company should know about.
—Even if your boss isn’t a spreadsheet expert, it’s easy to recognize a cluttered workbook. And people correctly distrust such clutter.
—Etc.
Therefore, wherever possible, you should separate your data from its presentation.
