Conditional Formatting Based on Another Cell Value

Excel Conditional Formatting Based on Another Cell Value (Table of Contents)

• Excel Conditional Formatting Based On Another Cell
• How to Use?

Excel Conditional Formatting Based on Another Cell Value

If you want to format a cell based on another cell including the text value, number value or date value etc. then this can be done by creating a rule in conditional formatting based on a simple formula.

We also can highlight the entire row or column based on the value in one cell.

How to Use Conditional Formatting Based on Another Cell Value?

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

These criteria’s can be of the following type:

• Text criteria
• Number criteria
• Multiple criteria
• Different color based on multiple conditions
• Where any cell is blank
• Based on a drop-down selection

For highlighting a row or cells based on the value in another cell, let’s take below example to understand this. We are taking Text criteria in this example.

Example – Conditional Formatting Based on Another Cell Value

Below is the list of online products which is ordered by the different-2 customer.

We have to identify the records whose delivery status is pending. We want to format the Order ID, Product, Ordered Date, Ordered By and Delivery Status based on the value in the Delivery Status column.

Here we want to format the entire row based on Delivery status whose value is equal to 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’s a very easy process to set up a formatting formula.

• First select the entire data from A3:E13 as shown below.

• Go to the HOME tab.
• Click on Conditional Formatting. Choose the New Rule option.

• It will open a dialog box for setting a new rule. Refer below screenshot.

• This dialog box has many options.

1. I want to highlight all cells based on their values
2. If it contains a value
3. Only top or bottom ranked values
4. Values that are above or below average
5. Unique or duplicate values

• Use a formula to determine which cells to format.
• Now we will select the last option “Use a formula to determine which cells to format”.
• We need to set up a formatting formula so that it returns a true or false value.
• If the value is true, it will apply the desired formatting in excel. Otherwise, the formatting is not applied.
• In the formula input box, enter the formula as shown below:

=$E3=”Pending”

Refer the below screenshot.

• Click on Format button then a format dialog box will appear, set the color in which you want the row gets highlighted. Refer below screenshot.

• Click on FILL tab and choose a color as per your requirement and click on Ok.

This will highlight all the rows whose Delivery Status is “Pending”.

How Does this Conditional Formatting Based on Another Cell Value Works?

• Conditional formatting option checks each cell in the selected range for the condition or formula specified by us.
• Our formula is =$E3=”Pending”
• It will analyze each cell in a row no.4. Starts from Cell A4 will check whether the cell E4 has the delivery Status Pending or not. If it does, that rows get highlighted else it doesn’t.
• We have used $ sign before the column alphabet ($E3).
• We have locked the column E, Delivery Status, which we are looking for.
• When Cell A4 is being checked for the condition, it will check Cell E4.
• When Cell A5 is checked for the condition, it will check Cell E5 and so on.
• This process will highlight the entire rows having Pending status.

Things to Remember

• In this article, we have used text criteria for highlighting the row or cells based on another cell value.
• Similarly, we can use the other criteria by entering different conditions under Formula text box depending on your requirement.

You can download this Conditional Formatting based on Another Cell Excel template here – Conditional Formatting Based on Another Cell Excel Template

Recommended Articles

This has been 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 downloadable excel template. You may learn more about excel from the following articles –

• VBA Text Box
• Conditional Formatting with Formulas
• Conditional Formatting for Blank Cells
• How to Add and Use Excel Filter Column?
• Excel Filter Keyboard Shortcut
• Using Auto Filter In Excel
• How to Sort by Color in Excel?
• Using Filters in Excel
• Using Auto Format Excel