Conditional Formatting Based on Other Cells

Copy of New Blog Post Format.png

Overview

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.

Example Sales Data

Example Sales Data

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:

Select the New Rule option under Conditional Formatting

Select the New Rule option under Conditional Formatting

In the New Formatting Rule window click on: Use a formula to determine which cells to format:

Select the type of Conditional Formatting to create

Select the type of Conditional Formatting to create

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:

=$B2=MAX($B$2:$B$13)

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:

Select the Format button

Select the Format button

In the Format Cells window, click on the Fill tab and choose a green colour for the highest sales month:

Select the Format to use

Select the Format to use

Click OK and OK again in the New Formatting Rule window and the table should now look like this:

The new Conditional Formatting Rule is active

The new Conditional Formatting Rule is active

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:

=$B2=MIN($B$2:$B$13)

Choose a red colour in the Format Cells window and the results should be this:

Add the minimum value in Red

Add the minimum value in Red

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:

Add an additional rule for Amber

Add an additional rule for Amber

The result will be:

The end result with 3 formatting rules applied

The end result with 3 formatting rules applied

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit