Conditional Formatting is a powerful tool. In this example, I’ve populated the range A1:A20 with some numbers. Now I want a visual cue of which numbers are at each end of the spectrum. For my purposes, I want to know which numbers are within 10% of the largest number and which numbers are more than 90% away from the largest number.
First, select the range A1:A20 (ensuring that A1 is the active cell) and choose Conditional Formatting from the Format Menu. The completed Conditional Formatting box looks like this
A couple of items to note: The first formula compares A1 to 90% of the maximum value in the range A1:A20. If it’s greater than that number, the font color is changed to blue. The second formula compares A1 to 10% of the maximum value and colors the font red if it’s lower. The A1 in both formulas is relative (no dollar signs) so that as the formatting is applied down the range A1:A20, it uses the current cell.
Here’s what the final formatted range might look like:
Now for the real power! When the numbers change, the formatting adjusts. Since I used RANDBETWEEN to generate the numbers, a simple recalc (F9) changes the numbers to look like this:
You can use any formula that your heart desires. The formulas I used may not be useful to anyone but me, so find the formula that gives you the best information and use it.
Knowledge may be power, but Conditional Formatting is absolute power. Use it wisely, grasshopper.