Using Formulas in Conditional Formatting

Today I'm going to look at how you can use conditional formatting to quickly give you visual cues about your data. I'm going to shamelessly plug the Product Analyst throughout this post to demonstrate how you can use conditional formatting to help you understand your data.

Lets start with our spreadsheet:


Ignoring the fact that I don't have enough sales to actually necessitate visual cues, lets say you were much more successful, and you wanted to know at a glance, what item you sold the most of (we can use this technique for many other things, but this is pretty straightforward).

Before we get to the conditional formatting, you'll need to be familiar with the large formula. This formula will give the xth largest number in an array you define (see prior post for definition of an array).
=Large(array,k) where k is the xth largest number

So, lets open up the conditional formatting screen:


By default, it's on "cell value is". This is great if you're trying to format your cell based on a value of another cell, but I can't use the formula I want here, so I'm going to switch to "formula is" with the drop down on the left.

Now, I'm going to use my "large" formula. When using the "formula is" option, the conditional formatting will occur if the formula returns the result true. Thus, if I wrote "1=1" in there, the conditional formatting would apply. If I were editing cell A1, and I said "A1=1", and the value in cell A1 was one, the conditional formatting would apply.

I'm going to conditionally format cell C7. In the conditional formatting, I'm going to write =C7=Large($C$7:$C$11,1). Lets look at this in more depth. I'm looking for the LARGEST value (as indicated by the 1), in this array. then I'm asking if the largest value in that array, is equal to the value in C7. If so, I want to use the formatting I specify. If not, I want to do nothing.

I am going to duplicate this for the 1st, 2nd, and 3rd positions (I can add conditional formatting).

Once you have done this in C7, you can copy and paste the formatting over the area you want to have look at this. Now, my number 1 selling item will be the darkest green, and will get progressively lighter until I get through my 3rd highest selling items. Note that items of the same sold amounts will be colored the same, if in the top 3.

When finished, it looks like this.