Conditional Formatting Based on Another Cell Value

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 criteria
  • Criteria #2 – Number criteria
  • Criteria #3 – Multiple criteria
  • Criteria #4 – Different color based on multiple conditions
  • Criteria #5 – Where any cell is blank
  • Criteria #6 – Based on a drop-down selection

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

Example

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

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

Conditional Formatting Another cell Example 1

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.

Conditional Formatting Another cell Example 1-1

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.

Conditional Formatting Another cell Example 1-2

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

Conditional Formatting Another cell Example 1-3

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

Conditional Formatting Another cell Example 1-4

  • 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

=$E3=”Pending”

Refer to the below screenshot.

Example 1-5

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

Example 1-6

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

Example 1-7

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

Example 1-8

How Does it Work?

  • The 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 the $ 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 the Formula text box, depending on your requirement.

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

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

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