How to use Conditional Formatting Based On Another Cell Value?

We can highlight an Excel row based on cell values using conditional formatting using different criteria.

  • Criteria #1 – Text criteriaCriteria #2 – Number criteriaCriteria #3 – Multiple criteriaCriteria #4 – Different color based on multiple conditionsCriteria #5 – Where any cell is blankCriteria #6 – Based on a drop-down selection

Let us take the below example to understand highlighting a row or cells based on the value in another cell.

Example

Below is the list of online products which the different two customer orders.

We want to format the entire row based on “Delivery Status,” whose value equals “Pending.”

When we want to format a cell based on the value in a different cell, we will use a formula to define the conditional formatting rule. It is an easy process to set up a formatting formula.

  • First, we must select the whole data from A3:E13, as shown below.

  • Then, we need to go to the “HOME” tab.Then, click on “Conditional Formatting.” Finally, choose the “New Rule” option.

  • As a result, it will open a dialog box for setting a new rule. You may refer to the below screenshot.

  • This dialog box has many options.

  • Format all cells based on their valuesFormat only cells that containFormat only top or bottom ranked values.Format only values that are above or below averageFormat only unique or duplicate values

Use a formula to determine which cells to format

  • We must use a formula to determine which cells to format.We must select the last option, “Use a formula to determine which cells to format.”We need to set up a formatting formula to return a “True” or “False” value.If the value is true, it will apply the desired formatting in excelFormatting In ExcelFormatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a table.read more. Otherwise, the formatting is not used.In the formula input box, enter the formula as shown below:

For example, =$E3=”Pending.”

Refer to the below screenshot.

  • First, we must click on the “Format” button. Then, a “Format Cells” dialog box will appear. Next, set the color to highlight the row. Finally, you may refer to the below screenshot.

  • Then click on the “FILL” tab, choose a color per our requirement, and click “OK.”

As a result, it will highlight all the rows whose “Delivery Status” is “Pending.”

How Does it Work?

  • The “Conditional Formatting” option checks each cell in the selected range for the condition or formula.Our formula is =$E3=” Pending”As a result, it will analyze each cell in row no.4. It will start from cell A4 and check whether cell E4 has the “Delivery Status” as “Pending” or not. If it does, those rows will get highlighted. Else, it does not.We have used the $ sign before the column alphabet ($E3).We have locked “Column E,” “Delivery Status,” which we are looking for.While checking cell A4 for the condition, it will check cell E4.When cell A5 is checked for the condition, it will check cell E5.This process will highlight the rows having “Pending” status.

Things to Remember

  • This article used text criteria to highlight the row or cells based on another cell value.Similarly, we can use the other criteria by entering different conditions under the “Formula” text box, depending on our requirement.

This article is a guide for Conditional Formatting based on Another Cell Value in Excel. Here, we discuss how to Use Conditional Formatting based on Another Cell Value, along with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Use Conditional Formatting for DatesConditional Formatting with FormulasConditional Formatting for Blank CellsVBA Conditional FormattingFormatting Time in Excel