Automatically Set Conditional Formatting Based on Another Cell

The ability to automatically adjust conditional formatting based on another cell is a valuable skill to learn because it will allow you to take advantage of the full power of conditional formatting. So, if you have a situation where you want to highlight an entire row (or column) of data based on the value of a cell (or cells) within that row, you can use conditional formatting to accomplish this. This makes it easy to quickly identify rows that contain blank cells or whatever value you specify. Once you know how to adjust the conditional formatting based on another cell, you can make your formatting more dynamic, more flexible, and more powerful.

Automatically Set Conditional Formatting Based on Another Cell

To start, simply highlight the rows you would like to apply the conditional formatting to.

Conditional Formatting Based on Another Cell Step 1

Then go into the Home Tab, select “Conditional Formatting”, and then select “New Rule”. You’ll get a screen that looks like this:

Conditional Formatting Based on Another Cell Step 2

In the box enter the following forumla:
=$D2="Yes"

Note that you need to include the dollar sign before the cell reference. This ensures that as Excel evaluates each row, it only evaluates the values contained in Column D. If you wanted to set the conditional formatting based on another cell (the cells in Column C, for example), you would change the cell reference in the formula to $C2. Once you’ve written your formula, set the cell background color to green. This formula will color the entire row green if the employee is bonus eligible. Your entries should look like this:

Conditional Formatting Based on Another Cell Step 3

When you click “OK”, Excel automatically colors the entire first two rows green because they are deemed bonus eligible.

Conditional Formatting Based on Another Cell Step 4

This is a very simple example of how to automatically set conditional formatting based on another cell. You can make the reference formula much more complicated if you want to evaluate several different columns or even set the color based on whether each employee’s score is above or below a certain level. The variations on this technique are almost endless and limited only by your imagination.

Don’t Stop Here

Take your education to the next level! Sign up for our free email newsletter, and you’ll receive free Excel tips and tricks directly in your inbox. You’ll also be the first to receive exclusive discounts on our training classes.

Posted in Excel and tagged .

Leave a Reply

Your email address will not be published. Required fields are marked *