A Funny And Useful Conditional Formatting Trick Sharing

In the previous tutorials, we have mentioned the use of the Conditional Formatting function many times. I don’t know whether you have mastered it. But, today I am going to share with you an interesting technique. Let’s take a look at the effect first.

Firstly, we need to type =LARGE(C2:C14, F2) in cell F3 and press Enter key.

Now, cell F3 displays #NUM, because cell F2 is blank.

We only need to fill in any value from 1-13 in cell F2 to get the corresponding ranking sales in cell F3.

Select the area from cell C2 to C14 first, and choose New Formatting Rule behind Conditional Formatting.

Choose Icon Sets in the Format Style list, then pick one style you like.

I choose Flags here and change Type into number in the list. Then get cell F3 to be the value.

Remember to change the other two flags to be No Cell Icon. Hit OK.

Well, we manage it.

If you feel that it is too troublesome to modify or enter a value in cell F13, we can add a control button here.

Go to Developer and insert a spin button of Form Controls.

Right-click the button to change its Format.

Choose cell F2 as the Cell link, and hit OK. Bingo!