How Do I Change The Color Of A Cell In Excel Based On Value?

Sentry Video – Highlight Rows based on Prison cell Values in Excel

In instance y'all adopt reading written teaching instead, below is the tutorial.

Conditional Formatting allows you to format a cell (or a range of cells) based on the value in it.

Only sometimes, instead of just getting the prison cell highlighted, you may want to highlight the entire row (or cavalcade) based on the value in one cell.

To give you an example, below I have a dataset where I take highlighted all the rows where the name of the Sales Rep is Bob.

In this tutorial, I will show you lot how to highlight rows based on a jail cell value using conditional formatting using different criteria.

Click here to download the Example file and follow along.

Highlight Rows Based on a Text Criteria

Suppose you have a dataset as shown beneath and you want to highlight all the records where the Sales Rep name is Bob.

Hither are the steps to practice this:

Select the entire dataset (A2:F17 in this example).
Click the Abode tab.
In the Styles grouping, click on Conditional Formatting.
Click on 'New Rules'.
In the 'New Formatting Rule' dialog box, click on 'Use a formula to determine which cells to format'.
In the formula field, enter the following formula:=$C2="Bob"
Click the 'Format' button.
In the dialog box that opens, set the color in which you want the row to get highlighted.
Click OK.

This will highlight all the rows where the name of the Sales Rep is 'Bob'.

Click here to download the Example file and follow forth.

How does it Work?

Conditional Formatting checks each cell for the condition we have specified, which is=$C2="Bob"

So when it's analyzing each cell in row A2, it will bank check whether the cell C2 has the name Bob or not. If it does, that cell gets highlighted, else it doesn't.

Notation that the pull a fast one on here is to use a dollar sign ($) before the cavalcade alphabet ($C1). By doing this, nosotros have locked the column to always be C. So even when cell A2 is existence checked for the formula, it will check C2, and when A3 is checked for the condition, information technology volition bank check C3.

This allows us to highlight the entire row by provisional formatting.

Related: Accented, Relative, and Mixed references in Excel.

Highlight Rows Based on a Number Criteria

In the above example, nosotros saw how to check for a name and highlight the unabridged row.

We tin can apply the same method to also check for numeric values and highlight rows based on a condition.

Suppose I have the aforementioned data (as shown below), and I want to highlight all the rows where the quantity is more than 15.

Hither are the steps to exercise this:

Select the entire dataset (A2:F17 in this instance).
Click the Abode tab.
In the Styles group, click on Provisional Formatting.
Click on 'New Rules'.
In the 'New Formatting Rule' dialog box, click on 'Utilize a formula to determine which cells to format'.
In the formula field, enter the following formula: =$D2>=15
Click the 'Format' button. In the dialog box that opens, prepare the colour in which you lot desire the row to get highlighted.
Click OK.

This will highlight all the rows where the quantity is more than than or equal to 15.

Similarly, we can besides utilise this to have criteria for the date equally well.

For case, if you desire to highlight all the rows where the date is later 10 July 2018, you lot can use the below appointment formula:

=$A2>Engagement(2018,7,10)

Highlight Rows Based on a Multiple Criteria (AND/OR)

You tin also use multiple criteria to highlight rows using conditional formatting.

For case, if y'all want to highlight all the rows where the Sales Rep name is 'Bob' and the quantity is more than 10, yous can do that using the following steps:

Select the entire dataset (A2:F17 in this example).
Click the Home tab.
In the Styles group, click on Conditional Formatting.
Click on 'New Rules'.
In the 'New Formatting Rule' dialog box, click on 'Use a formula to determine which cells to format'.
In the formula field, enter the following formula: =AND($C2="Bob",$D2>x)
Click the 'Format' button. In the dialog box that opens, set the colour in which you want the row to get highlighted.
Click OK.

In this example, only those rows get highlighted where both the conditions are met (this is done using the AND formula).

Similarly, you tin too use the OR condition. For example, if you want to highlight rows where either the sales rep is Bob or the quantity is more than xv, you can use the below formula:

          =OR($C2="Bob",$D2>15)

Click hither to download the Case file and follow along.

Highlight Rows in Different Color Based on Multiple Conditions

Sometimes, y'all may want to highlight rows in a color based on the condition.

For case, you may want to highlight all the rows where the quantity is more than than 20 in green and where the quantity is more than than fifteen (but less than 20) in orange.

To do this, yous need to create 2 provisional formatting rules and set the priority.

Here are the steps to exercise this:

Select the unabridged dataset (A2:F17 in this example).
Click the Home tab.
In the Styles grouping, click on Conditional Formatting.
Click on 'New Rules'.
In the 'New Formatting Rule' dialog box, click on 'Utilise a formula to make up one's mind which cells to format'.
In the formula field, enter the following formula: =$D2>15
Click the 'Format' button. In the dialog box that opens, prepare the color to Orangish.
Click OK.
In the 'Conditional Formatting Rules Manager' dialog box, click on 'New Rule'.
In the 'New Formatting Rule' dialog box, click on 'Use a formula to decide which cells to format'.
In the formula field, enter the post-obit formula: =$D2>20
Click the 'Format' button. In the dialog box that opens, fix the colour to Greenish.
Click OK.
Click Apply (or OK).

The above steps would make all the rows with quantity more than 20 in light-green and those with more than 15 (only less than equal to 20 in orange).

Agreement the Order of Rules:

When using multiple conditions, it important to make certain the order of the weather is correct.

In the above example, the Green color condition is above the Orange colour condition.

If it's the other fashion round, all the rows would be colored in orange merely.

Why?

Because a row where quantity is more than than 20 (say 23) satisfies both our atmospheric condition (=$D2>15 and =$D2>20). And since Orangish condition is at the height, it gets preference.

You can change the order of the conditions by using the Move Up/Down buttons.

Click here to download the Example file and follow along.

Highlight Rows Where Whatsoever Cell is Blank

If you desire to highlight all rows where any of the cells in it is bare, you need to check for each prison cell using conditional formatting.

Here are the steps to do this:

Select the entire dataset (A2:F17 in this example).
Click the Home tab.
In the Styles group, click on Conditional Formatting.
Click on 'New Rules'.
In the 'New Formatting Rule' dialog box, click on 'Use a formula to determine which cells to format'.
In the formula field, enter the post-obit formula: =COUNTIF($A2:$F2,"")>0
Click the 'Format' button. In the dialog box that opens, ready the color to Orangish.
Click OK.

The above formula counts the number of blank cells. If the result is more than 0, it ways there are blank cells in that row.

If whatsoever of the cells are empty, information technology highlights the unabridged row.

Related: Read this tutorial if you merely want to highlight the bare cells.

Highlight Rows Based on Drop Downwardly Selection

In the examples covered so far, all the weather were specified with the conditional formatting dialog box.

In this part of the tutorial, I volition show yous how to make it dynamic (and then that you tin can enter the condition within a cell in Excel and it will automatically highlight the rows based on it).

Below is an example, where I select a name from the driblet-down, and all the rows with that proper noun get highlighted:

Hither are the steps to create this:

Create adrop-down list in jail cell A2. Hither I take used the names of the sales rep to create the drib downward listing. Here is a detailed guide on how to create a drop-downwards list in Excel.
Select the entire dataset (C2:H17 in this example).
Click the Home tab.
In the Styles group, click on Conditional Formatting.
Click on 'New Rules'.
In the 'New Formatting Rule' dialog box, click on 'Apply a formula to determine which cells to format'.
In the formula field, enter the post-obit formula: =$E2=$A$2
Click the 'Format' push. In the dialog box that opens, set up the colour to Orange.
Click OK.

Now when you select whatsoever name from the drop-down, it volition automatically highlight the rows where the name is the aforementioned that you have selected from the drop-downwardly.

Interested in learning more on how to search and highlight in Excel? Check the below videos.