The Starting Document
To begin we’ll need an actual spreadsheet. Here’s one with almost zero formatting. There’s a bunch of data thrown in and arranged into sections, but not much else has been done.
Fix Your Alignment
As I point out in almost all of our design discussions, the tendency here is going to be to center align all or many of the columns as with the example above. For some reason we have it in our heads that a center alignment makes the data easier to read.
In reality, center-aligned columns of data make your eyes follow a ragged edge and therefore have a tendency to take longer to read because of the increased movement (subtle but present).
To address this issue, let’s give all the data a strong left alignment. This may seem boring (isn’t that what we’re trying to avoid?) but trust me, it makes the document much more attractive and user friendly.
Create Clear Headers
Our starting spreadsheet has already taken a significant step in creating headers that stand out by formatting the headers in all caps. Though this is by no means a necessity, it does help them clearly stand out from the rest of the content.
However, we need to take this principle a little further for it to really be effective. If you glance around the image above you’ll notice that the text still basically looks all the same. You’ll probably intuitively know how to fix this, but make sure that every step you take in designing a document is intentional with clear logic.
As you’ve probably guessed, we’re going to want to bold the headers. This is a quick and easy way to make sure your headers are getting the attention they deserve.
Remember to not go crazy with bolding, now that you’ve made bold text the key differentiator of the headers, bolding too much else will cause visual confusion. If you try to make everything special, nothing is going to be special.
Use Colors For Good and Not Evil
Adding color is easy and fun right? You can’t go wrong with a little color! Guess again. I saw an actual spreadsheet the other day that used colors similar to the one below.
It might be hard for many to accept this, but this is ugly. And I don’t mean it’s just a little too busy, I mean irreparably, makes-your-eyes-bleed ugly. If you end up with anything vaguely reminiscent to the spreadsheet above, erase everything and start over because you’ve failed.
The key to using color in a spreadsheet is to use restraint. Don’t act like a kid who just got his first paint set. Instead think about where color will really improve the readability of the document. In our case, remember that we’re focusing on the headers. This is a natural place to start with the color.
See how much the headers really grab your attention now? This little bit of color has vastly improved the speed at which you can scan the document because each section is now clearly defined.
Zebra striping is another incredibly useful tool that can easily ruin a document in the wrong hands. The reason we implement it is because those numbers way over on the right aren’t very easy to connect with the categories on the left. Your eyes have to traverse too much whitespace and can easily get lost and settle on the wrong number.
Check out how much this is improved by a few simple stripes.
Now it’s a lot easier to connect those distant numbers to the appropriate category. Notice how subtle the stripes are. If I would’ve made them darker they would’ve started to compete with the headers for visual prominence. Also, a little contrast is good if placed in the right places, too much contrast all over the page can make your eyes hurt.
Room to Breathe
One tendency I see in a lot of spreadsheets is formatting the cells so that they are just big enough to fit the content and not a fraction of an inch larger. The logic makes sense, you’re trying to cram a lot of information into a small space to save paper, scrolling, etc.
While attempting to save space, you often sacrifice a lot of the attractiveness of the design. Don’t be afraid of a little whitespace, both in height and width. Many spreadsheets these days stay purely digital and are never printed anyway so the paper argument doesn’t always apply.
The only difference between the spreadsheet above and the one below is that I added some height and width to the cells. If you glance quickly at each, you can’t help but feel that the top sheet looks a little sloppy and unprofessional while the bottom one feels nice and clean.
Break Out of the Spreadsheet
Remember what I said at the beginning of the article? Spreadsheets are boring. Even with all the little tweaks we made, you’ve still got a big boring page full of numbers.
If you’re submitting a report to your boss, client or teacher and you hand over a simple spreadsheet, there’s not a lot to be impressed with outside of your formatting (and your work of course). However, it’s best to make a great impression right off the bat, as soon as the person sees the document. Back in college I can’t tell you how many of my business school professors wrote “Looks Amazing!” on a report that I turned in simply because I went a step beyond all of the people who handed in a plain looking document.
The best way that I’ve found to fix a spreadsheet is to kill the spreadsheet. What I mean by this is to use spreadsheets more as a quick and easy way to format a fancy table and less of an end product. Granted, if you need to constantly update numbers in a sheet full of formulas, you’ll need to keep the spreadsheet. In most other circumstances though you can vastly improve your end product by doing something like this:
Suddenly our boring data is looking fantastic! The reason for this is that we’re not overwhelmed by the giant grid of endless data but instead see manageable chunks of clearly separate and easily readable information.
This document looks so much better than the original spreadsheet that your boss would no doubt give you a promotion and a raise for your dazzling design skills. Ok, probably not. In fact, it might only result in you getting permanently stuck with the job of fixing everyone else’s spreadsheets!
To sum up, everyone in the professional world has had their fair share of lame, hard to read spreadsheets. Your job from this point on is to always consider how you can improve both the functionality and aesthetics of your documents.
Remember to watch your alignments, create clear headers, use color wisely and selectively, consider using subtle zebra striping and always break out of the spreadsheet for the end product (when you can).
Leave a comment below and let us know the tricks you use for improving your spreadsheets. There are still plenty of items left for you to discuss and try: highlighting important cells, varying fonts, differentiating your first column and a whole lot more.