PowerPivotPro

 
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.

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).

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

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:

That “dot” in the upper left is cell A1

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

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

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:

Uncentered Title

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

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

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

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

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

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

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

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

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

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

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

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

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

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.