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
