How to Make Your Spreadsheets Less Lame

by on 24th September 2010 with 47 Comments

screenshot

Spreadsheets are lame. There’s no two ways about it. We designers hate spreadsheets because they’re a shining example of boring data presented in a boring manner.

Unfortunately for designers, spreadsheets are a necessary evil and could even be called a great and essential tool in the business world. Today we’re going to see how to improve both the aesthetics and readability of your spreadsheets with a few super basic design principles that literally anyone can follow. Let’s get started!

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.

screenshot

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.

screenshot

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.

screenshot

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.

screenshot

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.

screenshot

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

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.

screenshot

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.

screenshot

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.

screenshot

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:

screenshot

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!

Conclusion

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.

Comments & Discussion

47 Comments

  • http://www.gamedesignerwannabe.com Michael R. Keller

    Alignment:
    Don’t left align numbers in the manner shown in the examples. This is almost as bad as center aligning everything. The mental gymnastics required for a person to compare left-aligned numbers is criminal.

    RIGHT-align your numbers.

  • Joshua Johnson

    Interesting insight, I hadn’t really thought of how left-aligning affected the lining up of the place values… right aligning does make more sense. As long as it’s not centered I’m happy!

  • http://www.jongamblellc.com Jon Gamble

    I have to agree with Michael. Right aligning numbers makes it a heck of a lot easier to read.

    One thing I usually do as well is add a little size to my headers. One or two pixels isn’t that much but it adds just a little more spacing around it and makes it pop out to the reader just a little more.

    Great article though! Thanks.

  • http://www.twitter.com/AboveFunction Grant

    Great article and this is great but do people care that much about Spreadsheets?

    This may be great especially if this work is for a client, or it needs to be produced in a report but if its a tracking system or an internal system is it not just procrastination or wasted time styling the document?

    Just my two cents, great article though.

  • Joshua Johnson

    Grant, I think for internal documents it’s worth the time to make it actually readable. For the most part though, as I indicated, the superfluous styling is aimed at professional reports for clients or school. It really does make a good impression and is sure to earn you a few oohs and ahhs.

  • Misel

    The left alignment of numbers was the first thing I noticed while reading this article.

    In fact I think you could write a whole article about number formatting only :)

    Here’s what immediately came to my mind:

    Integers should be aligned to the right and if possible grouped in triples (i.e. 3,123,000 instead of 3123000). Triples easily show the order of magnitude of a number and you can immediately spot larger and smaller numbers without actually reading them.

    But rational numbers should be aligned to the center with the center being the decimal dot. Of course the decimals should only be shown where they are significant. When you compare numbers and they differ in several orders of magnitude it’s (usually) not necessary to be overly precise.

    But other than that it’s a great article. Thank you very much :)

  • http://www.liveanime.org helium

    I deal with too many badly formatted sheets from manufacturers etc that have to be changed for upload, sigh, is impossible to look through them when theres 10,000 rows.

    Another Tip:
    Static header rows so you always have the column names when scrolling down larger sheets. Save alot of pain ^_^

  • Jon

    I don’t care about spreadsheets and I do hate Excel. But if I HAVE to look at a spreadsheet I do all I can to make it look better. If information is presented in a visually pleasing manner, people are more prone to actually pay attention to the info.

    Great article! Especially love the color restraint principle!

  • desi nerd

    I agree, the numbers should be right-aligned.

    Also, it seems that you have no experience using Excel. If the data is boring, you can use charts to make it easier to read. Changing the color and adding space is not going to make it less boring.

    Excel is not designed for pretty presentations, just like Illustrator is not designed for crunching numbers. I have seen office clerks to research scientists use Excel and 99% of them don’t care about making it pretty. Perhaps you should use AI to create this report and see how much time it takes.

    Lastly, it seems you are using the Mac version of Office, which I agree is lame. You should try Office 2007 or 2010 for Windows. It’s much better.

  • jfiosi

    Two issues. I’ll bet that a spreadsheet that’s bigger than a 100% view on your monitor doesn’t get printed. Instead, what is printed more often is a condensed, report-oriented version of a spreadsheet — financial summaries and highlights. So yes, these can be spruced up with formatting, colors, font treatment, etc.

    However, we see this too often–form confused with function. Currency amounts in number format need to be right-aligned, at least in the currencies I deal with. Designers, don’t try to manipulate everything in your relentless search for creative solutions. Some things are just best left well enough alone.

    Lastly, try updating your article with a right-aligned number screen capture and see how dramatically better it looks.

  • Pingback: CSS Brigit | How to Make Your Spreadsheets Less Lame

  • ana.staackmann

    Good article!

    A good tip is to “hide” the normal grid.
    Not a lot of people do it, but when you do, it improves readability and the data doesn’t seem so overwhelming.
    Do NOT paint the background white to hide the grid, this only makes the file huge. Instead, use excel’s option to hide the grid. These are the steps to do it on excel 2007:

    Go to “View”
    Show/Hide
    De-select the Gridlines option

  • http://cameraconcierge,com?atlanta1 zackdaddy

    “Great article and this is great but do people care that much about Spreadsheets? ”

    YES

    The article is powerful because it also opens up the mind to considering other data that is similarly ignored for presentation

  • http://yahcc hhgfh df

    welcome to our website === w w w yahcc. – c o m === ..The new update, a large hot ..
    WE ACCEPT PYAPAL PAYMENT.
    YOU MUST NOT MISS IT!!!

  • Cogito Dexter

    Left aligned columns of numbers? No no no no no!

    Everyone is accustomed to lining up numbers along the column of the least significant digit. It’s the way kids are taught to perform arithmetic and it sticks with us for the rest of our lives.

    Clear headers, yes. Good colours, yes. Zebra striping, if appropriate (not good in a set of accounts, excellent for a table of prices against products). But never EVER left-align numbers. It’s criminal.

  • http://www.jeffmoskovitz.biz jeff

    Great article and great comments, most of which I agree with, particularly the comments about right-alignment. My other initial reaction was to highlight the numeric range and format the numbers with comma separators, as someone else suggested.

    Do we care about spreadsheets? By virtue of the fact that we’re having this conversation, the answer is most definitely yes. More crucial decisions are made based on spreadsheets than we may realize. A sloppy spreadsheet, as any document, is unprofessional at best. Additionally, rightly or wrongly, all other things being equal, an aesthetically-prepared spreadsheet tends to have more credibility. It pays to take that extra few minutes.

  • Pingback: Internet Briefing Blog / Wochenendsurftour

  • Ron

    Great article. Yes, spreadsheets are very important, and we should care about them, if only because much of the amount of data that is best expressed in the form of a table.

    I’d go a step further about number alignment: I’d align on the decimal point and add add commas (if you’re where they use commas).

    Also, the percentage of total is misleading in terms of precision. Percentages should be expressed with no more significant digits than appropriate and necessary. So, 79% or 78.8% at best for the first one.

  • http://www.bennglazier.com Benn Glazier

    Left aligning values is the WRONG way to go abour your numbers.

    Always right-align. The only time you could get away with center alignment is when it you’re putting hourly rates or something that has no real consequence with the above and below data.

  • http://undo-dog.com Alex Pile

    A correction and addition:

    Correction: Numbers should be right-aligned.

    Addition: Make sure the data can be sorted, if it will be viewed on a computer. I’m always annoyed when someone uses Excel like Word. If the data can’t be sorted, then you’ve lost the functionality of a spreadsheet.

  • http://fernabel.com fernabel

    all good but please right-align the numbers!!

  • Endre

    Great article, not particularly fond of excel, but every now and then, you do have to dive in… One other suggestion, use Conditional Formatting if you allready know which values when have to stand out

  • http://redbitbluebit.com John Uhri

    Actually, not to nitpick, but the spreadsheets should align based on the decimal point regardless of the number of digits.

  • http://www.bohemianpixel.net Andy Thornton

    You can dynamically link a spreadsheet with a word doc or ppt show. When you update the spreadsheet, it automatically adjusts your word doc. That way you can have a spreadsheet feed a good looking report.

    In the spreadsheet, highlight your data, choose copy and in the target application choose paste special -> Paste Link.

  • Ling Ooi

    great article..greater tho if figures were aligned to the right.

  • Read Weaver

    Once you’ve zebra striped, you no longer need the row lines; change them to invisible. Column lines are usually unnecessary; the numbers usually sort themselves visually into columns, as they do in the example shown. If you’ve zebra striped and the column lines are needed, white column lines may look better. In considering zebra striping, consider whether the spreadsheet will be printed by multiple users, and print it, preferably on a variety of printers. You may be unpleasantly surprised at the results.

  • http://www.ne-web.com Ne Web

    Thanks for the tips, will be useful when re-desiging our product spreadsheets for clients.

    I also agree with the first comment that right aligning the numbers is best.

  • NoloContendere

    People really do care about spreadsheets and how data is presented. It could make the difference between the recipient giving it real thought and the recipient just tossing it on the pile of all the other dense, black-and-white reports on their desk.

    Usability is a huge issue; that’s why so many web developers have backgrounds in graphic design, as well as writing code. Same goes for anything else — textbooks, brochures, newspapers, journals, and business reports. This article is a good one even if it just prompts people to think twice about what they’re presenting to others.

    Hopefully next week’s topic will be “Avoiding Acts of PowerPoint-Related Violence”. This world needs it.

  • Santiago

    That is not a Spreadsheet. That is a table.

  • Greymase

    I looked in Excel help for “Zebra Striping” – found nada. How is this achieved other than manually slogging through row by row? I saw an auto-format that does it, but it imposes all of its other formats as well. Yep – noob.

  • Michael

    Also, definitely take advantage of the conditional formatting options offered in the newer versions of Excel if you have it. It allows you to have, say, negative numbers automatically display in red (as a simple example). Proper use of this tool can make the spreadsheet infinitely easier to interpret quickly.

  • http://radio-pr.com/ frank

    Thank you for sharing.
    It is always a delight to feel what I believe – that
    information is inspiration

  • http://www.bigdevelopment.com vinh

    please go on tour and spread your gospel ;-) thank you for this.

    vh

  • Pingback: How to create a spreadsheet

  • Kirk

    Glad I found your article. Amazing what a little extra effort will produce.

  • steve

    I find Zebra stripes as described below are excellent for making an ordinary table stand out.

    http://excelprofessor.blogspot.com/2011/12/zebra-strips-using-conditional.html

  • Christian.a

    If you don’t have newer excel, you can use conditional formatting with this formula:

    =Mod(Row(),2)=0

    This formula highlights every other row of a range that is selected.

  • marleng

    Once you really get the hang of excel you will find how nice it can make your work look plus there are allot of trick out the that people should know not only to make the spread sheet better presentation wise but also to make work faster and simpler. I learned allot form my helpful talks with the people at Reporting Guru I highly suggest giving them a call especially if you are looking into using reporting systems for your bussines.

  • Pingback: Relative Advantage to Spreadsheets and Databases – Alison Ruoff's EdTech Blog

  • Pingback: Simple Profit and Loss Planning Template | the naked leaf

  • Pingback: Simple Profit and Loss Planning Template

  • http://mywaytonowhere.wordpress.com/2013/07/05/jak-se-squatuje-v-okanagan-valley/comment-page-1/ ayala condo,ayala condominiums,real estate philippines,housing in the philippines,philippines property

    I have read so many articles or reviews concerning the blogger lovers however this paragraph is
    actually a fastidious paragraph, keep it up.

  • http://www.modernbajubatik.com/ Baju Batik Modern

    Please let me know if you’re looking for a article writer for your blog.
    You have some really good articles and I think I would be a good asset.
    If you ever want to take some of the load off,
    I’d really like to write some articles for your blog in exchange for a
    link back to mine. Please send me an e-mail if interested.
    Regards!

  • http://hands57bush.blog.com/ Terrence

    As ffor every investigation, Revival capsul is
    discovered to be as a harmless treatment to aid oxugen carrying capability
    of cells. In this posting I hope to present yyou with facts on which
    herbal therapies are at thiis time in use aand whether there is any proof to support their efficiency.
    Nonetheless, mainly because it is a purely natural compound, propolis
    can be aspect of a preventative well being treatment system and can be taken each
    day.

  • http://topgarciniacambogia.edublogs.org/ Fat Burning Of NatureWise Of YoungYou

    hello there and thank you for your information – I’ve definitely picked up something new from right here.
    I did however expertise a few technical points using this site,
    since I experienced to reload the web site a lot of times previous
    to I could get it to load correctly. I had been wondering if your web host is OK?
    Not that I’m complaining, but sluggish loading instances times will often affect
    your placement in google and can damage your high quality score if ads and marketing with Adwords.
    Well I’m adding this RSS to my e-mail and can look out for a lot more of your respective exciting content.
    Ensure that you update this again very soon.

  • http://erektilnidysfunkce.wordpress.com co je erektilní dysfunkce

    I read this post completely concerning the difference
    of most recent and previous technologies, it’s
    amazing article.

  • https://www.rebelmouse.com/batikmodern/ batik pria modern

    Right here is the right webpage for anyone who hopes to understand this topic.
    You understand a whole lot its almost tough to argue with you (not
    that I really would want to…HaHa). You
    definitely put a fresh spin on a topic which has been written about for many years.
    Wonderful stuff, just great!

Subscribe
Membership
About the Author