Today we are going to create a little unusual, but quite natty chart in Excel. Image below shows how it works.

There is **no VBA** involved and the whole idea is based on the usage of **conditional formatting** only.

Before we start writing proper formulas controlling the way our chart is going to look, let’s prepare “the stage”. What we need to do is to prepare an area of 100 (10 rows and 10 columns), preferably square, excel cells (unless you have a better idea about how it could look like).

Our basic “stage” is now set and I am quite happy about cells sizes. The only thing is that everything looks a little boring. And we don’t want to be boring, do we? So let’s add some colors. I decided to set the background color to blue and add white cell borders. Finally, let’s turn the worksheet grid off. Our chart starts to slowly take a final shape.

Now, what we need to do is to set conditions for **Conditional Formatting**, which will turn individual cells on and off, depending on the helper cell (where we keep the amount of cells which need to be coloured).

Let’s select all 100 cells of our chart, go to **the Conditional Formatting dialog box** and create a new rule. My first approach to creating a proper rule is:

=OR(ROW($B$11)-ROW()<=INT($M$3/10)-1,AND(ROW($B$11)-ROW()=INT($M$3/10),COLUMN()-COLUMN($B$11)<($M$3/10-INT($M$3/10))*10))

Where $B$11 is the bottom left cell (corner) of our chart and in $M$3 we keep the percentage (number of squares we want to highlight). You will probably have to adjust it slightly to meet your own needs.

I decided that I want all the cells fulfilling my condition to be green.

After a few minutes I realized though, that it could be done with a simpler formula (formatting condition):

=((ROW($B$11)-ROW())*10+COLUMN()-COLUMN($B$11)+1)<=$M$3

As before, $B$11 is the bottom left cell (corner) of our chart and $M$3 is the cell with the number of squares we want to highlight.

Until now, our starting point is the bottom left corner of the chart. But what if we wanted to start from the bottom right corner? Or, even better, what if we would like to have a choice?

All we need to do is to add a list with our choices (to do so I used the Excel validation feature), and to change our formula slightly, which will now look like this:

=((IF($A$5,ROW($B$11),ROW($K$11))-ROW())*10+ABS(IF($A$5,0,COLUMN($K$11))-COLUMN())-IF($A$5,COLUMN($B$11),0)+1)<=$M$3

The cell A5 contains TRUE if “Left2Right” direction is chosen, and FALSE otherwise.

If you have any doubts please download the example file attached and play around with my formulas.

Marcin