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