Box Chart

When we think of charts in Excel we often think of column, line, pie, bar, area, scatter and donut charts. However Excel also provides us was to create customs charts. In the below example I have two charts, each with a 10 by 10 array totaling 100 squares. My goal is to fill in each square so that the total squares filled matches my header. For example, Satisfaction has a score of 58, so there are 58 squares shaded in my Satisfaction matrix. Price has a score of 43 so there are 43 squares out of 100 shaded.
Satisfaction
Price








































Brand X











































Satisfaction
58
42




















Price
43
57




































































































Now you would think that creating this type of chart is difficult. It is not.
To start we need our key. In this case I have my data in the following cells.


A
B
C
1
2
3
4
5
6
Brand X

7


8
Satisfaction
58
9
Price
43
10

I format the columns in my matrix to be as wide as the row height creating squares.



A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
1
Satisfaction
Price
2
3
4




















5




















6
Brand X





















7






















8
Satisfaction
58




















9
Price
43




















10




















11




















12




















13





















I also have some hidden data off to the right. This data is also in a 10 by 10 matrix, each number representing a square in the box.


AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
1
2
3
4
91
92
93
94
95
96
97
98
99
100
5
81
82
83
84
85
86
87
88
89
90
6
71
72
73
74
75
76
77
78
79
80
7
61
62
63
64
65
66
67
68
69
70
8
51
52
53
54
55
56
57
58
59
60
9
41
42
43
44
45
46
47
48
49
50
10
31
32
33
34
35
36
37
38
39
40
11
21
22
23
24
25
26
27
28
29
30
12
11
12
13
14
15
16
17
18
19
20
13
1
2
3
4
5
6
7
8
9
10

Now for the fun part! I select cell D13 (the first square of the blue matrix).

I create a conditional format. From the Home ribbon I select Conditional Formatting > New Rule > 
Use a formula to determine which cells to format.

In the format values where this formula is true I type =AA4<=$B$8.

$B$8 locks the formula to my Satisfaction total cell.

Once I have my formula typed, I click the format button. Change the tab to Fill and choose the color of the cell that I want (in this case blue). I click the OK button and then on the Edit 
Formatting Rule window I click the OK button. Finally I click the OK button on the Conditional Formatting Rules Manager window.

The result is that since 58 is greater than 1, the cell is shaded.



Satisfaction




















Brand X























Satisfaction
58










Price
43



















































Now I copy the highlighted blue cell to the other 99 cells in my matrix. I can do that with a simple copy then paste. First I copy the cell to the right.
Satisfaction




















Brand X























Satisfaction
58










Price
43



















































Then I copy the entire row of blue (10 cells) upward.
Satisfaction




















Brand X























Satisfaction
58










Price
43



















































Now to do the second green matrix I repeat the same process except that I change my formula to reference the Price total in cell B9     =AA4<=$B$9

The last bit of setup to do is to hide the columns AA through AJ.

So now that you know the trick, how can you use this technique to add some WOW to your dashboards?

Want to see the spreadsheet?

Download this example from Scribed

No comments:

Post a Comment