Conditional formatting is a useful way to visualise specific formulas or areas of your sheet.
Excel’s ready to use Conditional formatting options are based on the cells value.
But what if we need to format a cell based on another cell’s value?
To do this, we can use a formula based conditional formatting rule.
Creating the Formula
Let’s see this with an example
In the following table we want to highlight the Month with the highest sales.
We will first select the area where the conditional formatting will apply, in our example will be A2:A13
Then under Conditional Formatting in the Home tab click on New Rule:
In the New Formatting Rule window click on: Use a formula to determine which cells to format:
In the Format values where this formula is true field, enter the formula so the month with the highest sales is highlighted in green cell background colour:
Use the MAX function to get the maximum value:
This tells Excel: if cell B2 equals to the maximum value of the range, then highlight the cell A2 green.
The setup of the range ‘B2’ is crucial here. Note that there is no $ symbox before the row number but there is before the column.
$B2 so the formula doesn’t only check B2 alone, but the all of the cells in the range that the formatting is applied to - this is done by NOT placing a $ in front on the 2.
Click on Format to set the cell colour:
In the Format Cells window, click on the Fill tab and choose a green colour for the highest sales month:
Click OK and OK again in the New Formatting Rule window and the table should now look like this:
Let’s also highlight the month with the lowest sales in red. Follow the steps above and in the Conditional Formatting Rule formula, enter the following formula:
Choose a red colour in the Format Cells window and the results should be this:
Now, highlight amber the Month with sales equal to 560.
Again follow the steps above and in the formula bar in the Conditional Formatting Rule enter:
The result will be: