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