Guest Post: 15 Spreadsheet Formatting Tips

 
IF YOU ARE ARRIVING HERE FROM THE EXCEL BLOG:  Welcome!  In addition to reading Dan’s excellent post below, I highly recommend you look into PowerPivot if you haven’t already.  Microsoft has never given you a bigger gift than PowerPivot and you just need to take it.  A quick glance at the top five benefits it provides will whet your appetite and then this collection of links can get you started.

Last week’s post about the importance of formatting spreadsheets published to SharePoint drew a lot of interest, including from some of my old colleagues on the Excel team back in Redmond.

Dan Battagin is one of those guys who seems to have a special deal worked out with Time.  As in, Time has agreed NOT to apply to him.  While often doing the work of two or three people at MS, Dan was typically completely remodeling his house, staying super-fit, and as a hobby, programming a number of robust applications in his “free time” at home.

So when Dan sent me a list of tips he’d been compiling on spreadsheet formatting and related topics, naturally I pressed him to expand that into a blog post.  He said “well I’m not sure how long it will take me, no promises,” then sent me the following opus about 36 hours later.  Showoff.

Yes, it is possible to create nice looking spreadsheets

Guest post by Dan Battagin

As a member of the Excel team I’ve been known to wear clothing that says “recalc or die,” (yes, I’m a geek) and one thing that makes me smile is a great looking spreadsheet. Luckily, I smile other times too, because basic spreadsheets don’t generally look that great – like everything, Excel gives you the tools, but leaves it up to you to use them.

Today, I’ve pulled together 15 tips that can help you step up your spreadsheet design So let’s jump right in and create some sweet looking spreadsheets!

Here’s a pretty typical spreadsheet. It shows data about a Windows Phone 7 application, to help understand how much it’s getting downloaded, and what its rankings are in the phone marketplace.

clip_image002[5]

I see a lot of spreadsheets like this – it works, and has all the data you need. But boy is it ugly. In fact, I’d call it ugly three ways:

1. Layout – nothing is really organized. It’s like someone threw up data in the sheet.

2. Style – it has the default Excel style – another way of saying it doesn’t have any.

3. Structure – it uses the grid, but Excel’s got a lot more tools than that folks!

So, let’s see how we can turn this into a great looking spreadsheet by addressing these three areas. In the end, here’s what I’d like to see instead – these screens show the same data, but using the tips below to make this spreadsheet something that I wouldn’t mind looking at each time it was open (and not just because of the kittens).

 

clip_image004[4]

clip_image006[4]

 

 

 

clip_image008[4]

 

 

Tip 1: Don’t use row 1 or column A

Just because the grid looks like a table doesn’t mean you need to use all the cells. Using row 1 and column A almost always puts your data too close to the row and column headers (A, B, C, … and 1, 2, 3, …) and things look squished. Do yourself a favor and use the first row and column for spacing. Personally, I size them to 10px and then start in B2.

Before

After

clip_image009[4]

clip_image010[4]

Note from Rob:  I sometimes go one step further and make the first row and column a single pixel, THEN make the second row/column about 10 pixels as Dan does here.  That way I can park selection in that first cell and have it be barely noticeable:

image

That “dot” in the upper left is cell A1 Smile

The other thing I often do is select a cell that is completely hidden behind an image.

Tip 2: Resize rows and columns

You can make your spreadsheet look a lot better if you space things out a bit, and leave room to read the info it contains. Make cells that contain data wide enough to read most of the content. Leave empty rows or columns between data as a visual separator. For finer grained control, leave multiple rows or columns, and size them to just a few pixels, so you can adjust later.

Before

After

clip_image011[4]

clip_image012[4]

Tip 3: Merged cells are evil, instead use Center Across Selection

This one surprises a lot of people, but it’s true. When you merge a cell in Excel, you’re really limiting what you can do in the future because Excel still “thinks” in rows and columns, and merged cells muck that up. For instance, you can’t insert a cell if any of the cells in that column are merged across multiple columns; you can’t create a table if there are merged cells in the range; and you can’t sort or filter a range with merged cells either. There is a workaround however and it looks exactly like merged cells once it’s applied – it’s called “Center Across Selection” and it’s available on the Cell Format dialog. To use it follow the steps below, or check out this video from Mr. Excel.

1. Select the cells you were going to merge

2. Open the Format Cells dialog (Home Ribbon, Font group, little button in bottom right)

3. On the Alignment tab, set the Horizontal alignment to “Center Across Selection” and click OK.

4. Type the value you want centered into the left-most cell of the selection from step 1.

Seen this?

Use this

clip_image014[4]

clip_image015[4]

Note from Rob:  I admit, I still use a lot of merged cells to center titles and such.  I know about merge across but old habits die hard.  That said, I think the unspoken tip here is to center your titles in the first place.  Consider these examples:

image

Uncentered Title

image

Centered Title

Tip 4: Align everything, and keep related data close

All too often, data ends up wherever you clicked and then pasted it into the spreadsheet. Charts end up “near” the data. Shapes and images are placed semi-randomly. Spend some time to align everything and you’ll get a much cleaner look. If you have two tables of data, make sure they start in the same row. If you have a chart, set it to Snap to Grid, and then make it the exact width of the columns you want it to span. If you have multiple charts, line them up. The human eye gets distracted when objects aren’t aligned because our brain tries to align them internally…so help your brain.

Before

After

clip_image016[4]

clip_image017[4]

Tip 5: Keep it to 3-5 colors – white, gray, and 1-3 accents

When it comes to color in your spreadsheet, less is more. For the background, use white. Studies have shown that dark text on white is much easier to read (and looks better to most people). For most text, and for anything that’s black by default in a chart (labels, axis, gridlines, etc.) use a shade of gray – it’s less jarring than black, and it “feels” modern. And when it comes to colors, don’t go overboard – choose an accent and stick with it throughout the entire workbook. I tend to like a shade of green but any accent will do (and less saturated ones tend to look better). If you’re building charts, you may need a second or third color too to differentiate the series – that’s fine. Note that Excel doesn’t actually help you here – we added a lot more colors in Excel 2007 (from 64 to infinite) and that tends to make people want to use them (all). More colors are good, but only because you can pick the few that you like.

Before

After

clip_image018[4]

clip_image019[4]

Tip 6: Don’t use more than 2 fonts

By default, everything in Excel is one font (and that font depends on your version – it might be Arial, Calibri, or even Segoe UI), but the font picker is so prominent in all versions that I see a lot of workbooks with a dozen or more fonts. Like colors, it turns out that less is more here. Choose a couple of fonts and stick with them – use font size (as opposed to different fonts or bolding) to differentiate parts of the spreadsheet. A clean, modern looking combination is Segoe UI for “normal” cells, and Segoe UI Light for any headers. And remember – make most of your text gray (you can do this easily – right click on the Normal style in the ribbon and change its color).

Before

After

clip_image020[4]

clip_image021[4]

Note from Rob:  Whoa!  Multiple fonts!  Never occurred to me.  Hmmm….

Tip 7: When you’re done, turn off gridlines and headers, and chart borders

Nothing can do more for your spreadsheet than turning off the gridlines and row/column headers. If you structure your data right (use tables – see Take advantage of tables) then you have all the borders you need and the gridlines just make things look busy. And the row/column headers might remind you that you’re in Excel, but when you’re viewing (as opposed to authoring) a workbook, you just don’t need them. While you’re at it, turn off any borders on charts – it looks a lot better when the graph is just “on the background” of the sheet.

Before

After

clip_image022[4]

clip_image023[4]

Tip 8: Don’t forget the images

With the prominence of the grid in Excel, it’s easy to end up with a spreadsheet that’s just text and numbers – boring. Spice it up a bit with your company logo – or better yet, the logo of the subject of the spreadsheet. Don’t go overboard here, but a few representative images will go a long way to making the end result nice to look at. Oh, and if you can, try to get ones with white backgrounds, so you don’t end up with a bunch of intrusive rectangular images.

Before

After

clip_image024[6]

clip_image025[4]

Tip 9: Ignore the fancy formatting options

This is a follow-on from Tip 8: Don’t forget the images, but applies to other elements of the spreadsheet as well (charts, shapes, WordArt, etc.) Nearly all the time you think that a drop shadow or reflection or glow or rotation or gradient or perspective or bevel would look good, it usually won’t. What it might do is distract from the clean simplicity of a modern spreadsheet. So, rather than look for “cool formatting to add,” remember that “simple formatting is cool formatting.”

Before

After

clip_image026[4]

clip_image027[4]

Tip 10: Take advantage of tables

Tables were introduced in Excel 2003, but didn’t really become useful until Excel 2007. Now, if you have tabular data, it should be in a table. This gives you quick access to sorting and filtering, makes the data easy to reference into, and – even if you don’t use them for any of the data capabilities – gives you a bunch of nice formatting options. When you choose a format, choose one that uses your accent color, and try to grab one of the Light or Medium styles. Remember – less is more.

Before

After

clip_image024[7]

clip_image028[6]

Tip 11: Flatten your data

This tip is a bit more advanced, but the idea is that any data you enter in the spreadsheet should be completely un-aggregated. This means that each row should stand completely on its own. For instance if you know that on 3/11/2012, you had 12 downloads from the United States, and 82 from the United Kingdom, you should enter that as two rows with columns named “date,” “country” and “downloads” rather than one row with “date,” “United States,” and “United Kingdom.” The reason for this is that Excel is GREAT at aggregating data for you (with less mistakes)…which we’ll get to in Tip 12: Let Excel do the math and Tip 13: Don’t be scared of PivotTables.

Before

After

clip_image029[4]

clip_image028[7]

Tip 12: Let Excel do the math

Spreadsheets aren’t just a big table that you can enter values into – they all support formulas, which can do almost anything you want to do, faster than you can do it yourself. Need to add 100 values? Excel can do that for you. Need to find the average of some value for each month? Excel can do that for you.

Before

After

clip_image030[4]

clip_image031[4]

Note from Rob:  Or let PowerPivot do the math of course Smile

Tip 13: Don’t be scared of PivotTables

PivotTables in Excel are a way to group and summarize your data. If you’ve entered your data as suggested in Tip 11: Flatten your data, you probably have several categories of data in your table. By putting this into a PivotTable, Excel will automatically sort the data into those categories and show you a grouped view. Plus, just like Tables, PivotTables give you some really nice formatting options that make it easy to see where each group starts and stops, as well as totals for each group way faster than you could do the totals by hand. Handy, fast, and clean.

Before

After

clip_image032[4]

clip_image033[4]

Note from Rob:  most people reading my blog love pivots of course, but as I mentioned Friday, pivots are probably the most under-used feature of Excel.  It was (and is) one of the ongoing missions on the Excel team to help a broader audience adopt pivots.

Tip 14: Use charts, but do not use 3D charts

You should always use charts – a quick visual representation of data is always easier to understand and looks better than a big table of numbers – but when you do, make sure they are 2D (“flat” charts). Not only do 2D charts look cleaner and more modern (see Tip 9: Ignore the fancy formatting options), but you can actually read the data in them. Because of the way 3D charts work, it’s nearly impossible to see the size of bars or columns, and lines look like they are floating in space. Do yourself and the people who are looking at your spreadsheets a favor and just don’t use 3D charts.

Before

After

clip_image034[4]

clip_image035[4]

Note from Rob:  3d charts don’t render as 3d in the browser either.  See this post.

Tip 15: Name everything (esp. sheets and tables)

OK, this last tip doesn’t actually make your spreadsheet more visually pleasing from a “pretty” sense, but the more things you name, the easier it is to use the spreadsheet. It’s a lot easier to know what’s on the “Download Dashboard” sheet than it is to know what’s on “Sheet 2.” Likewise, if you name cells, tables, and other objects, you can refer to them by name in formulas, which makes it a whole lot easier to see what’s going on in the formula – =SUM(DataTable[Downloads]) is a lot easier to understand than =SUM(C17:C753).

Before

After

clip_image036[4]

clip_image037[4]

In Summary

So, those are my tips. Each tip by itself is pretty small – but once you embrace the lot, my guess is that you’ll be happy with the result. Plus, I’m sure there are more, and even some that I don’t know yet. And, if you’ve got a favorite (that I did or didn’t mention), let me know – post a response!

Oh, and if you’d like a copy of the sample spreadsheets I used, feel free to download them here.

19 Responses to Guest Post: 15 Spreadsheet Formatting Tips

  1. Nice post. I’m a visualization junkie, so I liked this post. #3 was new information, so thanks!

    My favorite is #7 – I can only hope you’ve created some converts so I have to see less workbooks with gridlines still visible in the background. Heh heh.

  2. Great article and I’m looking forward to see more tips on the subject.

    Meanwhile I would like to suggest a general book on UI for the web:
    Designing Interfaces, 2nd Edition
    Patterns for Effective Interaction Design
    By Jenifer Tidwell
    Publisher O’Reilly

    Kind regards,
    Dennis

  3. Great post Dan.

    One thing I might have added: use Styles for your cell formatting. It is a great way to keep you out of trouble regarding the Too many different cell formats error (which I agree is very rare on Excel 2007/2010).
    More so, if you stick to using styles, you’re forced to think about how you want to design your spreadsheet’s look and feel. And once you’re used to them, they are very easy to use.

    • Dan [MS] says:

      @JK – I couldn’t agree more. In fact, in the sample spreadsheet I made for this post, the way I did the “less fonts” and “gray text” etc. was through the styles – that makes everything in the book consistent, and much more pleasing to read.

      • Ho Yun says:

        First off, thanks for these tips. Great for those of us who feel stuck in a rut with our spreadsheet designs.

        If I create and use custom styles, what happens if someone takes a tab out of my spreadsheet and copies it to their own spreadsheet? Will the styles (and especially colors) stay as intended?

        • David says:

          Ho Yun, you seem to have been left without a reply. Custom styles will copy across to the other workbook, so should stay as you intended.

  4. SusanInAK says:

    Great tips, thanks for organizing and relating them. It is so easy to make things look good now (I’m using Excel 2010). Once you start using tables and styles, you never go back.

  5. João Pinto says:

    #1 I always do this
    #3 Like you said, “old habits die hard”. I often do the mistake of using merged cells and I already had big problems later when I wanted to work the data and had lot’s of merged cells. I had to change the complete data sheet layout in order to do what I wanted!

  6. Ed says:

    For the life of me I cannot fathom why “merged cells” is the default function of that icon in Excel today. I hate those. Leave the functionality in, because it sometimes is the only way to accomplish an effect (you cannot wrap and center across selection simultaneously for example, and a text box doesn’t always work in that situation), but for the love of all things binary, make that icon center across selection by default.

    I actually have a custom icon button in my Quick Access Bar that removes all merged cells for me. I have to use it a lot when receiving files before I can start manipulating them.

    • David Hatton says:

      “I actually have a custom icon button in my Quick Access Bar that removes all merged cells for me. I have to use it a lot when receiving files before I can start manipulating them.”

      Ed, how did you create such a marvellous and wonderous contraption?

  7. Dave says:

    Great post. I was glad to see the “get rid of chart borders” tip. Most Excel users I know want to “box in” everything with heavy black borders.

    I’d add in one more tip. #16) Delete empty sheets!

    As a consumer of spreadsheets from others, I hate checking “Sheet2″ and “Sheet3″ for data. My default for new workbooks is one sheet only.

    Thanks again,
    Dave

  8. Dima says:

    I like most of the tips, but I do have an issue with #15: when I see a formula that says =SUM(C17:C753) I know exactly which cells the formula is referencing, but when I see =SUM(DataTable[Downloads]), I now have to go to Data Manager to find out where those cells are. It is my strong belief that the best use for named ranges in Excel is for VBA and for referencing external data that’s pulled in by MS Query.

  9. Shailesh says:

    Excellent collection of tips ! I use most of them already – except I am going to exploit the one dot in cell A1 now

    One more point is for printing excel sheets – many a times, people like to print the gridlines and I dont, so before I begin, (Time=0 :) ) I ‘select all’ and change background/fill colour to white. I am not sure if this is the best way to do it, as otherwise someone else down in the editing chain selects all and turns on gridlines (eeek) and the file looks shabby again.

    Is there an easy way to prohibit someone from using the dark gridlines on an otherwise ‘nice’ looking sheet?

    Thanks for your advise and guidance.

  10. Samuel says:

    Excellent tips! i started using them and feel good. they are very useful and helpful!

    Thanks.

  11. Nitin Patel says:

    Thanks bhai…

  12. Brian says:

    One last tip:USE SECURITY.

    It is not that I think that my co-workers are dishonest but they sometimes forget to tell you they made changes that can screw up your data. You can make individual cells, sheets or the entire workbook Read-Only and have them send you any updates or changes to be made. If you are responsible for keeping this accurate, why not do this; I did and noticed that the data became a lot more accurate and there were a lot less issues.

  13. Rob says:

    If you have lots of Excel documents to format and print http://www.rapidformat.com can help.

  14. Jithesh says:

    I have one question. when I created power pivot table and added back ground color, color is going if I collapse the tree details in power pivot. is there any option to keep the color even after collapsing.

Leave a Reply