Apply conditional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For example, if the values in column B are greater than 75, make all data cells in the same row blue

You can apply conditional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For example, if the values in column B greater than 75, make all data cells in the same row blue.

You can watch the steps in this video, and the written instructions are below the video. The full transcript is also available below. |

In this example, we'll colour cells blue, if the number of units, in column B, is greater than 75. At a glance, we'll be able to see which rows have a high number of units.

Follow these steps to get started with the conditional formatting rule:

- Select the cells that you want to format (A2:D8 in this example)
- On the Ribbon's Home tab, in the Styles group, click Conditional Formatting

- Next, near the bottom of the list, click
the
**New Rule...**command

The New Formatting Rule dialog box opens, where you can enter the conditional formatting rule details

- In the top section,
**Select a Rule Type**, click on the last option:- Use a Formula to Determine Which Cells to Format

The lower section, **Edit the Rule Description**, changes automatically.

Now it shows a box where you can enter your formula:

- Format values where this formula is true

Any conditional formatting formula must be a **test, that gives a TRUE or FALSE result**.

For this conditional formatting rule, the formula will do a simple check:

- Is the number of units,
in column B, greater than 75?
- TRUE or FALSE

Before you create a conditional formatting rule, check which cell is active on the worksheet.

If the formula will have cell references, they might need to refer to the active cell's row and/or column

- In this example, cells A2:D8 are selected
- Cell
**A2**is the active cell - Formula should check the
**value in cell B2**-- the active cell's row - Formula would work for the active cell (A2):
**=B2>75** - Formula will need the correct type of reference to cell B2

In Excel, a cell reference can be an absolute, relative reference, or mixed reference. Which type of reference should be used in this conditional formatting formula?

**Absolute: $B$2**-- both column and row are locked -- all cells would check value in worksheet cell B2**Relative: B2**-- neither column or row are locked -- all cells would check value in their row, in column to their immediate right**Mixed: B$2**- column not locked, row locked - all cells would check value in cell in row 2, in column to their immediate right**Mixed: $B2**- column locked, row not locked - all cells would check value in their row, in column B

For this formula, we need to use the last option, a mixed reference -- **$B2**. With that reference, the conditional formatting will work correctly, in every cell that is selected, from A2:D8

- In the rule box, enter the following formula:
**=$B2>75**- column reference is locked ($B) , row reference is not locked (2)
- all cells will check the value in their
**own row**, in**column B**

The purpose of conditional formatting is to highlight cells that meet the criteria rules.

In this example, the cells should turn blue, if the formula result is TRUE. That will highlight the rows, so they're easy to spot in a long list.

Follow these steps, to set the formatting:

- In the New Formatting Rule dialog box, click the Format button.
- In the Format Cells dialog box, select the formatting options
that you want.
- In this example a light blue fill color was selected
- You could also select Font, Border, and Number formatting

- Click OK, to close the Format Cells window

Back in the Conditional Formatting Rules Manager, the new rule settings are shown

- Check the formula, and the formatting preview
- If everything looks correct, click the OK button, to apply the rule change.

On the worksheet, the new conditional formatting rule is applied.

- Rows with a number greater than 75 in the Units column, are highlighted with blue fill colour.

In the formula, it's important to use an **absolute reference** to column B, by putting a $ in front of the column letter -- **$B**

- The absolute reference ensures every column refers to the value in column B.
- Rows 3 and 6 have blue fill, because they have units greater than 75

If we use a relative reference -- **B -- without the $ sign**, the column reference would adjust automatically, in each column of the selected range.

- Instead of referring to column B, each column would refer to the column to its right.
- That would give incorrect highlighting with the conditional formatting, as shown below
- Cell C2 has blue fill, because the cell to its right is greater than 75
- Cells B4 and B5 have blue fill, because cells C4 and C5 are greater than 75

After you create a conditional formatting rule, you might want to change it. For example, in the previous example, a formula-based rule was created, to highlight rows with a 75 or more units.

Instead of setting that number in the conditional formatting rule, we'll change the rule, and refer to a cell on the worksheet, where a number is entered.

If you want to change an existing conditional formatting rule, follow the steps below:

- Select the cells where the original conditional formatting rule was applied -- A2:D8 in this example
- On the Excel Ribbon, click the Home tab
- In the Styles group, click the Conditional Formatting command
- Next, at the bottom of the list of options, click on Manage Rules

The Conditional Formatting Rules Manager opens, showing a list of rules for the selected cells (Current Selection).

**Note**: To see other rules, you can select from the "Show Formatting Rules For" drop down list, at the top of the Rules Manager dialog box.

To change one of the conditional formatting rules:

- Select a rule in the list, and click the Edit Rule button
- In the Select a Rule Type section, the current rule type is highlighted
- In the screen shot below, the rule type is
**"Use a formula..."**

- In the screen shot below, the rule type is
- In the Edit the Rule Description section, the current settings are shown
- In the screen shot below, the rule uses a formula:
**=$B2>75**

- In the screen shot below, the rule uses a formula:

In the bottom section, **Edit the Rule Description**, you can make changes to the rule and its formatting. In this example, the formula will change:

- from the old rule, with a number:
**=$B2>75** - to the new formula with a cell reference:
**=$B2>$F$1**

First, follow these steps to change the formula:

- In the formula box, delete the number, 75
- Move the Conditional Formatting Rules Manager, if necessary, to see cell F1, where the number is stored
- Click on cell F1, to add that cell reference to the formula

Next, you can also change the formatting for the rule, if you'd like to. In this example, the rule will have green fill colour, instead of blue.

- Click the Format button
- In the Format Cells dialog box, click the Fill tab
- Click on the light green colour
- Click OK, to close the Format Cells dialog box

In the Conditional Formatting Rules Manager, the new rule settings are shown

- Click the OK button, to apply the rule change.

On the worksheet, the new conditional formatting rule is applied.

- Rows where the number of units is greater than the value in cell F1 have green fill

This is the full transcript for the Colour a Row in Excel Based on One Cell's Value video, at the top of this page.

With Excel's conditional formatting, you can easily highlight a cell if it's over or under a certain value, or if it meets a value that you've set.

But in some cases, instead of just a single cell, you might like to highlight a whole row in a table, if one of the cells in that row is over a certain number or under.

In this case, we would like to highlight each row in this list if the number of units sold is greater than 75.

So to do that, I'm going to select all of the rows, all of the columns in each row. So I've selected from A2 down to D10.

On the Ribbon, on the Home tab, I'll click Conditional Formatting, and none of these preset rules will do exactly what I want. So I'm going down to New Rule, and in here I'll select a formula.

So I'm going to use a formula to determine how to color each row.

When I click that, there's a spot where I can put the formula.

I want to, in each row, look at the value that's in column B. So I'll type =

And we want, from every column, we want to look at column B. So we have to lock that cell. We don't want it to be relative, we want it to be absolute.

So type a $ to lock that in. And then B.

And we want, in this case, the active cell we can see is white, where the other cells are highlighted with blue.

We can see that, in the name box, A2 is showing up. So that's the active cell, so the active row is 2. So I'm going to type 2 here.

We're going to check what's in B2 and see if it's greater than 75. So that's our test.

And if it is greater than 75, we want to format it. So I'll click Format and I'll choose a fill color, maybe a blue color and click OK, and click OK again.

And now, any row where the number of units is greater than 75, all four cells in that row are colored blue.

Download a zipped sample file for
this Excel tutorial. The xipped file is in xlsx format, and does not contain any macros. This example is on the sheet named **MultiCell**.

Fix Conditional Formatting Extra Rules

Conditional Formatting Examples

Conditional Formatting Documentation

Last updated: December 28, 2021 3:41 PM