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
Below is the list of online products which is ordered by the different-2 customer.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
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.
- I want to highlight all cells based on their values.
- If it contains a value
- Only top or bottom ranked values.
- Values that are above or below average
- 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 to the below screenshot.
- 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.
- Click on the 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 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 –
- Use Conditional Formatting for Dates
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- VBA Conditional Formatting
- Formatting Time in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
