Here are the topics/best practices to talk about regarding creating Dashboards using PowerPivot based on what I have learned from others and a lot of trial & error. (This is another one of those posts where the primary audience in the future of it – will probably be me – I just don’t want to lose some of these ideas/URLs and I am using this information to start a checklist for future dashboards)
- Decide what your dashboard focus is
- Make the data User Friendly or at least User-friendlier
- Dashboard layout
- Dashboard aesthetics
Each of these I get into more detail below, but jump straight to #4 if you are looking for good dashboard tweaks to make the dashboard pop a little more.
First #1 – Decide what your dashboard focus is
This is much bigger than what I will offer up here, but let’s point out a few good starting points. First it was Kasper de Jonge’s blog post that took me to a TechEd presentation on Dashboard Design Best Practices presented by Dan Bulos.
Where it asks the question – What are you trying to show? – Comparisons, Compositions, Distributions – the type of chart you need depends on the type of information you want to share – I thought his below chart from the TechEd talk was a great aid.
Many people have also pointed to Stephen Few’s book Information Dashboard Design
which I can only imagine as a great resource although I have not read it yet – I can guess that however based on another Stephen Few book “Now you see it” – a GREAT read of his that I am reading right now that covers visualization for quantitative analysis (this book will need to be a future post as it is a big topic and a huge book)
2) Make the data User Friendly or at least User-friendlier
That means :
- Sanitize all of the data in your Power Pivot Field List – go back to the PowerPivot screen and hide all Dimensions and Data Elements that users won’t need (such as all of your syndicated key columns, internal IDs, etc
- Rename your Dimension and Relationship tables to user-friendly names, don’t force the users to select fields from tables in the PowerPivot Field List named DimCustomerInfo, etc – Name the Measures in Customer Friendly names – especially since dragging the measures into any Pivot Chart adds a column caption of the same name and it’s no fun seeing columns named BilledAmountYearOverYearPercentageGain.
- Hide or Delete unused or unneeded Excel worksheet tabs – just make sure you don’t delete the worksheets that PowerPivot added for any PivotCharts you have added– doing so, you will find that they no longer respond to slicer actions – instead, just right-click on these and select “Hide”.
- Setup data element and measure formatting in the PowerPivot worksheet – don’t resort to Excel spreadsheet formatting within the PivotChart – that way the user is not forced to re-add it each time they use them in a different chart – be consistent with the formatting – for example, if possible, make sure your percentages all display the same number of decimal places, and all currency either uses commas or does not, etc – as long as it is consistent
3) Dashboard layout
One of the first issues you will run into creating dashboards and adding multiple charts and tables and slicers is the fact that PivotTables are not allowed to overlap – and since future refreshes of data may add more rows (or columns) to your PivotChart, you have to make sure it has room to grow – otherwise you get errors like this “A PivotTable report cannot overlap another PivotTable report.”
To prevent that, I try to align my fixed size charts and graphs at the top just below my slicers, and my variable length PivotCharts nearer to the bottom where they can grow without running into other worksheet objects
Once you have columns sized the way you want them to display the proper length, right-click on the PivotChart, and select “PivotTable Options”, here Uncheck the “Autofit column widths on Update” option and make sure the “Preserve cell formatting on update” option is selected as seen below. This will make sure your screen is dancing around on refreshes changing the size of columns, etc.
Same thing with PivotCharts – right-click on the graph, and select “Format Chart Area”, and select “Don’t move or size with cells” so that any resizing that does happen doesn’t shrink or stretch your graph.
4) Dashboard aesthetics
I always struggled with this one to make my dashboards look less “Excel-y” until I stumbled across this post on the Powerpivotpro.com site by guest blogger Dan Battigan of the Excel team – Excellent advice and now I am hooked on his suggestions. I have summarized his list below but please go to his link where he demonstrates each of his points with graphics and has links to his spreadsheet examples.
(all emphasized lines below by underlining has been added by me – Brad)
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.
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.
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.
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.
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.
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).
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.
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.
p 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.”
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.
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.
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.
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.
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
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)
He is right, the Segoe UI font does look great on dashboards and is now part of my checklist, as well as his points on leaving 1st row & column empty, turning off gridlines, etc – it’s a great piece to help one get started in creating a professional looking dashboard that looks a lot cleaner. And contrary to popular belief (and every BI-controls advertisement in software dev magazines today, dashboards apparently aren’t required to be neon colors on a black background with 3-D charts and the obligatory funnel chart. PS. – you lose all credibility (and somewhere an angel loses its wings ) every time you force your users to decipher what your stupid funnel chart represents.
And finally, for more formatting & aesthetic tips, I loved the Angry Koala’s tips on formatting and positioning of slicers (even removing the slicers from their bounding box which I had not known about until watching his video). Having issues with slicers autosizing and the like – watch Grant’s video below.
Check out his talk titled “Killer Real-World PowerPivot Examples” here to learn more about those types of topics and making dashboards in PowerPivot look more like dashboards.
That’s a lot of people that have helped contribute to my starting checklist/best practices guide for me as I try to improve with each new dashboard I create – I am thankful to each for sharing their tips and ideas and hope this helps others looking for the same.