Search Contextures Sites

Excel Conditional Formatting Based on another cell

Download a zipped sample file for this Excel tutorial

Video: To view the steps in a short video, click here

Apply Excel Conditional Formatting to a Row

You can apply conditional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For example, you could colour the entire row in a table, if the values in column B are over a set value.

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 dates have a high number of units.

To apply conditional formatting to the cells:

1. Select the cells that you want to format (A2:D4 in this example)
2. In the menu bar, choose Format > Conditional Formatting...
3. In the Conditional Formatting dialog box, from the first drop-down list, choose Formula Is
4. In the text box, enter a formula that refers to the active cell in the selection. In this example, we selected A2:D4, and cell A2 is the active cell. So, in the formula, we'll refer to cell B2, because it's in the active row.

        In this example, the formula is: =$B2>75

In this example, we use an absolute reference to column B ($B), to ensure that the conditional formatting in all columns refers to the value in column B.

If we simply use a relative reference (B), the formula will be adjusted in each column, and won't work properly. Each cell will refer to the cell to its right, instead of refering to the cell in column B.

 

 

Check Anothr Cell

 

Set Conditions - Formula

 

5. Click the Format button.
6. In the Format Cells dialog box, select the formatting options that you want. In this example a medium blue fill colour was selected. You could also select a Font format, and a Border format.
7. Click OK to close the Format Cells dialog box.
8. If you want to add more conditions, click the Add button, and repeat steps 3-8, for that condition.
7. Click OK, to close the Conditional Formatting dialog box.

 

Check Anothr Cell
   

Download a zipped sample file for this Excel tutorial

Video: To view the steps in a short video, click here

 
   

Learn how to create Excel dashboards.

Excel Conditional Formatting Tutorials

  1. Excel Conditional Formatting -- Introduction
  2. Excel Conditional Formatting -- Multiple Cells
  3. Excel Conditional Formatting -- Examples
  4. Excel Conditional Formatting -- Documentation

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.

 

Last updated: October 13, 2009 11:46 PM