For maximum impact follow these simple principles and guidelines when creating DASHBOARDS.

The supporting excel file provides examples of how leading MVP’s use these principles on a daily basis.

Data distortion / misrepresentation

D A chart may be accurate but if the underpinning data is out of context it’s pointless. Smoothed
lines are great for identifying trends but can be misinterpreted as actuals.

D Aesthetics
A Use easy to read fonts, sizes and formats for text inputs. Ensure text is proper (and not in
upper or lower case). Keep axis label alignment horizontal as it’s easier to read.

Simplicity
S Keep charts as simple as possible. Inclusion of unnecessary information pollutes key
messages and adds clutter. Ensure the chart is the focus so the key messages are clear.

Harmony
H If you have multiple charts in your dashboard ensure they have a similar look and feel. Avoid
mixing formal & informal charts. Avoid overpowering colours / themes styles.

Best chart selection

B Chart selection depends upon the type of data you need to visualise and the key message(s) you
want to get across. eg tracking trends & relationship : Pie Column Chart Variations

Overpowering 3D effects
O They may look funky but they should be avoided. They distract from the simplicity and they distort
data visually. As a result they are prone to confusion and incorrect interpretation.

Audience
A Who is your audience and what key messages are important to them? This determines which
style you use (formal or informal) and the level of detail you contain within it.

Removal of non-essential items

R The more clutter you add to a chart (gradient backgrounds, full bodied axis lines, labels etc)
the harder the chart will be to read. They also have severe printing limitations.

Dynamic / interactive charts

D Great for providing your audience with the ability to control the content within a chart. To
prevent confusion ensure controls are keep simple and intuitive to control .

Sensible scale and axis selection

S Your scale / axis selection can have make a significant difference to the visual effect of a chart.
Incorrect selection or categorisation will distort trends and misrepresent the data.
The majority of the example charts used in this guide were sourced directly from, or variants of John Walkenba
Many thanks to John for giving permission for their inclusion.

All other sources are referenced accordingly.

rom, or variants of John Walkenbach's book 'Excel 2007 Charts'.
 1 2
Month Calls Per Hour Target
Apr 30 80
Calls Per Hour Calls Per Hour
120 120
May 40 80
Jun 45 80 100 100
Jul 32 80
80 80
Aug 40 80
Sep 55 80 60 60
Oct 61 80
40 40
Nov 60 80
Dec 80 80 20 20
Jan 100 80
- -
Feb 100 80 Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
Dec Jan Feb
Mar 32 80 Calls Per Hour Target Calls Per Hour Target

Both charts are technically accurate, however, chart '1' is completely out of context so should be avoided.

3 4
Month Sales
Apr 0 Line Chart with Straight Line Line Chart with Smoothed Line
May 8 10 10
Jun 0 8 8
Jul 0 6 6
Aug 9 4 4
Sep 0 2 2
Oct 0 0 0
Nov 7 -2 -2
Dec 5 -4 -4
Jan 0 -6 -6
Feb 0 -8 -8
Mar 5 -10 -10
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar

Chart 4 has smoothed lines so distorts the appearance ie it looks like there are negative values

5
Team Qtr 1 Qtr 2 % Change % Qtr 2 Contribution Team Sales
Team A 1,500 1,555 3.7% 61.5% 60%
Team B 800 900 12.5% 35.6%
Team C 50 75 50.0% 3.0%
40%
Qtr 2 Total 2,530

20%

0%
Team A Team B Team C

Using percentage change can be misleading. Team C may have the greatest qtrly change 50% change but they only contributed to 3% of sales
 6
Month Region 1 Region 2
Apr 1093 786 REGIONALPERFORMANCE
May 1283 873
Jun 1101 659 (firsthalf)
Jul 1235 711 1,80 Note:Thistex isto smal tobel [Link] hat's
OKbecausenobdywil readitanyway. Thistex is
Aug 1457 739 to smal tobel [Link] hat'sOKbecausenobdy
wil readitanyway. Thistex isto smal tobe
Sep 1574 745 1,60 [Link] hat'sOKbecausenobdywil readit
anyway.

1,40
1,20
1,0
Region 1 Region 2
80
60
40
20
0
S o u rce : A c o u n t i g D e p a rt m e n t

Appreciate this is an extreme example of what not to do, but it really emphasises the point about consistent formatting

7
When ever possible avoid altering the alignment of Staggered Labels Using ALT+Enter
your axis labels. 5

If you have long axis labels which can not be shortened, try 4
one of Andy Pope's solutions on the following link : 3
2
[Link]
1
If that doesn't work consider changing the chart type eg 0
make the column chart a bar chart.
Label_On e Label_Th ree Label_Five Label_Seven
Label_Two Label_Fo ur Label_Six

These labels were quite long. Instead of changing the alignment of the text you can stagger the text
 8 9
Day Call Volume
Mon 100
Daily Call Volumes Daily Call Volumes
300 300
Tue 125
Wed 150 250 250
250
Thu 175 225
Fri 200 200
Call 200
200 Volume
Sat 225
175 150
Sun 250
150
150
125 100

100
100 50
Mon Tue Wed Thu Fri Sat Sun
0
50

0
Mon Tue Wed Thu Fri Sat Sun

Chart 8 is full of unnecessary colour and clutter which distracts from the key message.

On one of Chandoo's blogs Jon Peltier explains …

A good dashboard report is not defined by a fancy colour scheme. It is defined by the information it conveys, its clarity, its comprehensiveness, its succinctness. It’s best to use colour sparingly, so when it
appears, it really means something.
Chandoo, an Excel MVP recently arranged a dashboard creation competition. There are some 10
cracking examples from lots of different people that demonstrate great use of simple colours and Sales Dashboard 2008-2009

consistent themes. The attached images just one example, but you can see all submissions on A: Sales by month for Region: All, Product: All, Customer: Planet from J anuary 08 to December 09

the link below. 18,000

16,000

14,000

12,000

[Link] 10,000

8,000

There's lots of other fantastic material on Chandoo's site, however, the section on design principles is 6,000

particularly relevant to this section.

4,000

2,000

[Link] Controls
J an-08 Feb-08 Mar-08 Apr-08 May-08 J un-08 J ul-08 Aug-08 Sep-08 Oct-08 Nov-08 Dec-08 J an-09 Feb-09 Mar-09 Apr-09 May-09 J un-09

B: Personal Sales by Customer

J ul-09 Aug-09 Sep-09 Oct-09 Nov-09 Dec-09

Region Product Customer 400,000

All All J ames Kirk

All 350,000
East Luke Skywalker 300,000
Light Sabres Enterprise
West
Hansolo 250,000
Transponders Galaxy

Excel 2007 has some great theme colours pre built in the fill options.
South
200,000
Glue Guns Planet
Chewbacca
North
150,000

The following ad-in from Andy Pope gives 2007 users extra pattern selection options. Date range

From: J an-08 To: Dec-09

Categories on chart B:
100,000

50,000

[Link]
Customer
0
Enterprise Galaxy Planet #N/A
 11 12

Scotland Wales Performance by Country Performance by Country

Qtr 4 1,200 1,200
Qtr 3 Qtr 4 1,000
1,000
Qtr 3
800
800 Qtr 1
Qtr 1 600
600 Qtr 2
Qtr 2 Qtr 1 400
Qtr 3
200 400
Qtr 2 Qtr 4
- 200
Qtr 1 Qtr 2 Qtr 3 Qtr 4
Scotland Wales England Ireland -
Scotland Wales England Ireland

England Ireland
3,000 Performance by Country
Qtr 4 Qtr 4
Qtr 1 2,500
Qtr 4
Qtr 3
Qtr 1 2,000 Qtr 3
1,500 Qtr 2
Qtr 3 Qtr 1
Qtr 2 1,000
Qtr 2
500
-
Scotland Wales England Ireland

QTR Scotland Wales England Ireland

Qtr 1 1,082 1,093 1,099 1,076
Qtr 2 544 566 597 619 If you are trying to compare performance the pie charts in '11' are completely ineffective.
Qtr 3 183 217 241 450 A far better option would be one of the column charts in '12'
Qtr 4 87 308 387 698

There are lots of great reference sites that give examples on chart selection, for example, Chandoo's top 10 charting ideas to compare actual values with targets …
[Link]
 13
SalesWales
Per Country
28%
Country % Sales
Scotland 22%
Wales 28%
England 28%
Ireland 22%
Scotland England
22% 28%

Ireland
22%

The slices in this pie chart make it look like Wales sold more than England yet their achievement is the same

14
Qtr Scot Wal Eng Ire
Qtr 1 34% 23% 24% 10%
Qtr 2 27% 29% 24% 26%
Qtr 3 28% 23% 26% 31% 35%
Qtr 4 11% 26% 27% 33% 30%
25%
20%
15%
10%
Wal
5%
Ire
0%
Qtr 1 Eng
Qtr 2
Qtr 3 Scot
Qtr 4

This chart is hard to make accurate compassions

15

Qtr Scot Wal Eng Ire

Qtr 1 34% 23% 24% 10% 35%
Qtr 2 27% 29% 24% 26% 30%
Qtr 3 28% 23% 26% 31% 25%
Qtr 4 11% 26% 27% 33% 20%
15%
Ire
10%
5% Eng
0%
Wal
Qtr 1
Qtr 2
Qtr 3 Scot
Qtr 4

Adding perspective tends to obscure data points

 16
100 Qtr 4 Sales Take a Dive
90

Qtr Sales 80
Qtr 1 95
Qtr 2 90 70
Qtr 3 85
60
Qtr 4 10
50
Qtr 1 Qtr 2
40 Qtr 3
30

20

10
Qtr 4
0

Make sure the key message is right for your audience !

17

Apr
Qtr Sales
2,483
Customer Complaints Decline
3,000
May 2,046
Jun 1,726
Jul 1,606 2,500
Aug 1,528
Sep 1,520
2,000
Oct 1,459
Nov 1,371
Dec 1,324 1,500
Jan 1,203
Feb 1,071
1,000
Mar 941

500

0
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar

Great message and use of clip art but you wouldn't want it in the middle of a formal dashboard
 18 19
Median Home Prices 1989 - 2006
San Diego vs. Phoenix Median Home Prices

Adjusted for inflation $700 K

San Diego
$700,000 $600 K

$600,000
$500 K
$500,000
$400 K
$400,000

$300,000 Phoenix $300 K

$200,000
$200 K
$100,000
Adjusted for inflation $100 K
$0
1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006
Phoenix San Diego $0 K
1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006

The John Walkenbach examples above demonstrate the benefits of removing non essential elements. Chart '19' solves printing limitation issues and is in line with the principle of
"data Ink" maximization. Edward R Tufte has written books on this principle and Jon Peltier templates are other great examples of this line of thinking.
 20
There are countless sites and books that provide brilliant tutorials on how to make your charts and
dashboards dynamic.
The links below are just a few examples of my favourite, and most used reference sites and
resources that relate to dynamic functionality.

PHD's training section on creating dynamic charts

[Link]

Contextures has lots of really usefuly tutorials on dynamic ranges

AJ P Excel I nformation
[Link]

Andy Pope's tutorials eg using spinners and dynamic date ranges

[Link]

Using dynamic chart source data

[Link]

John Walkenbach has numerous reference books to stimulate and inspire your dynamic solutions.
This one is particularly brilliant for charting tips such as dynamic ranges etc

[Link]

There are additonal on-line dynamic tips within John's speadsheet tips section
[Link]
Month Sales 21 22
Apr 11,291
May 11,688 Sales Remained Flat Throughout the Sales are on the rise!
Jun 12,096 Year 13,500
Jul 12,021 14,000
Aug 12,132 13,000
12,000
Sep 12,470
Oct 12,893 10,000 12,500
Nov 12,780 8,000
Dec 12,954 6,000 12,000
Jan 12,584
4,000
Feb 12,687 11,500
2,000
Mar 12,973
- 11,000
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar

If you don't use a sensible value axis on your chart key messages are likely to be misleading or under/over stated

23 24
Net Income: 37 Years of Growth Net Income: 37 Years of Growth
8,000,000
Year Net Income
1970 56,890 8,000,000 7,000,000
1980 1,837,130 7,000,000 6,000,000
1990 3,870,981 6,000,000 5,000,000
2000 5,197,329 5,000,000
4,000,000
2005 6,697,992 4,000,000
2006 7,008,873 3,000,000
3,000,000
2007 7,298,326 2,000,000
2,000,000
1,000,000 1,000,000
0 0
1970 1980 1990 2000 2005 2006 2007 1965 1970 1975 1980 1985 1990 1995 2000 2005 2010

Chart '23' paints a misleading picture as standard category labels are used. Chart '24' is better as the axis type is set to the dates

25

3,000 3,000
Month Qty
Apr 95 2,500
May 293
2,500
Jun 1,032
2,000
Jul 1,293
Aug 1,456
Sep 1,183 1,500
2,000
Oct 1,392
Nov 1,582 1,000
Dec 2,083
Jan 1,893 1,500
500
Feb 2,594
Mar 2,899
-
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar 1,000

3,000
2,500
500
2,000
1,500
1,000
500
-
-
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
c
t
g
n

b
r

Oc

De
Ap

Au

Fe
Ju

Remarkably these graphs are from the same data set yet the initial interpretation would be different for each chart.
Advisor Score
26
1 10 Score Distribution
2 68 Bin Bin Frequency 8
3 25 <=20 20 4 6
4 56 20-40 40 4
4
5 16 40-60 60 4
6 72 60-80 80 7 2
7 30 80-100 100 1 0
8 42 <=20 20-40 40-60 60-80 80-100
9 19
10 63 27
11 67 Bin Bin Frequency
12 52 <=10 10 1 Score Distribution
13 60 10-30 30 6 10
14 78 30-40 40 1 8
15 37 40-50 50 1 6
16 17 50-60 60 3 4
17 99 60-100 100 8 2
18 24 0
19 61 <=10 10-30 30-40 40-50 50-60 60-100
20 74
Unequal bin sizes in a histogram can misrepresent the data

Advisor Score
Apr 785 28
May 832
Jun 974 JRT Travel Services
Jul 850 Results of June Promotion
Aug 759 5,000
Sep 4,890 In John Walkenbach's book 'Excel 2007
4,900 Charts' there are a couple of methods.
Oct 689 This one uses two overlying charts.
Nov 725 4,800
Dec 840 4,700
Jan 785
4,600
Feb 881
Mar 754 4,500

1,000
900
800
700
600
500
400
300
200
100
0
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar

29
25

Andy Pope also demonstrates another

method that incorporates an additional
series and data label to recreate the
20 effect.
10

[Link]
[Link]

0
A B C D E F

These examples demonstrate methods of visualising a broken axis. Very handy is your data has large ranges.