I’ve been fortunate enough to be exposed to a lot of different corporate cultures and a lot of different spreadsheets. Over the years I’ve noticed that certain habits seem to propagate throughout a business and almost create a Microsoft Excel Culture, unique to each organization.
There’s a habit that I see about 30% of my clients use. And it’s a bad one! That habit is using text color or background fill color to code or store some type of information. It’s a bad habit so don’t do it!
Here’s a quick example of what I mean by color-coding. I threw together a quick list of blog posts EeloSoft could do over the next few months. I color-coded them to indicate my progress. I included a legend at the bottom. Here’s a snapshot:
After reading and looking at that, you’re probably thinking 1 of 3 things:
- What’s the big deal? That looks pretty good and intuitive to me.
- I’ve done that, and I feel a little guilty but am not exactly sure why…
- You’re supposed to be an Excel expert! What are you doing!?! That is a bad habit.
Okay. So if you already understand the bad habit, you probably don’t need to read on. If you thought that looked like a good approach, let’s do a little role-playing. Imagine you’re the owner of the topic list in the picture above, and I’m your manager. I might ask make a number of reasonable requests that you could use this list tofulfill:
- How many blog posts have we completed/published this year?
- We need something pretty quick, but I want it to be a good one. Give me a list of posts that you have drafts for or are working on.
- Give me a list of all posts that we haven’t started on.
These questions are pretty easy to answer from the list I just gave you. You can answer the first question from a quick glance at the chart. To manage the other two, you might just cut & paste rows to re-arrange them. Now imagine this list of blog posts was 1,000’s of entries long. Could you still answer those questions I just posed in a few seconds? Impossible.
So what’s the better solution. Use a separate column to convey any information you’re tempted to code with color. The basics of proper table design is to use a row for each entity that has data describing it and to use a column for each type of description used to classify that entity. So let’s add another column and label it “status”. We can still keep it short with a legend if we’d like. It might look something like this:
Maybe now you’re thinking, “Erik! That looks boring, and it’s much more difficult for me to see what’s going on.” I hear you, but this allows me to easily use Excel’s built-in functionality to answer those questions rather than manually trying to process this. I can select the table and choose Data->Sort to order the posts. Or I can apply a filter to only view posts with the desired status. If you’re not sure how to do either of those things, that’s okay. Consider subscribing to this blog and/or signing up for some training.
Maybe you understand where I’m coming from but are tempted to ignore this advice because it’s just not as easy to interpret. There’s good news. We can have our cake and eat it, too! Using built-in functionality that came with Excel 2003 and was improved with Excel 2007 and beyond, we can store the data properly and still get color-coding, too. Explaining how to implement conditional formatting is beyond the scope of this post but please take away these best practices for spreadsheet design:
- Store extra information in extra columns, rather than via color or other formatting
- Implement Conditional Formatting, targeting those extra columns, to make data more legible via color
Here’s what our spreadsheet would look like with appropriately used color to display column headings, filters applied for easy data viewing, and conditional formatting implemented to make the data easier to read:
If you’ve read this far, I hope you’ve found this advice useful and will start implementing it with future spreadsheets you create. If you already have color-coded spreadsheets with 1’000’s of rows of data that you’d like converted into a more usable format, let me know. I do have some automated tools I’ve developed and can usually clean these messes up for about $20-$50 per dataset.