Conditional Formatting Based On Another Cell in Excel (6 Methods)

While dealing with a large database you may want to format some specific cells based on other cells or values to identify them quickly. In such a case, you can use conditional formatting to create a formatting formula. Conditional formatting is a fascinating way to reduce your workloads and it can improve your efficiency. Today in this article, we will demonstrate how to perform conditional formatting based on another cell in Excel.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

6 Easy Ways to Perform Conditional Formatting Based On Another Cell in Excel

Consider a situation where you are handling a database containing ID, Name, Section, and Total Sales of some Sales Representatives. Now you have to format some cells based on their names, sections, or total sales using conditional formatting. In this article, we will learn 6 different ways to do it.

1. Highlight Entire Row Based On Another Cell Value

You can highlight the entire row based on single-cell value. Let’s say we have to identify Luke in the database. To do it, create another table anywhere in the worksheet and insert the name in it. Then follow the steps below.

Step 1:

• Select the entire dataset. In your Home Tab, go to Conditional Formatting in Style Ribbon. Click on it to open available options and from them click on New Rule.

Home → Conditional Formatting → New Rule

• A new window opens. Select Use a Formula to Determine the Cells to Format to continue.

Step 2:

• In the formula section, insert this formula.

• This formula will compare the dataset cells with the name Luke (G4). When the value will match, it will highlight the cell.

Step 3:

• We need to format the matched cells. The format section will help you out. We have chosen the color of the text Automatic.

• Fill cells option will help you to highlight the cells with different colors. Choose any color you like to go.

• Now that we have completed all the actions, click OK to get the result.

• Our entire rows are formatted based on the values of another cell.

Read more: How to Highlight Row Using Conditional Formatting

2. Use OR Function to Perform Conditional Formatting 

You can use The OR Function to apply conditional formatting. We want to highlight Finance and IT using the OR function. Insert those texts into your reference table.

Step 1:

• Go to the New Formatting Window following these steps.

Home → Conditional Formatting → New Rule

• Select Use a Formula to Determine the Cells to Format.

Step 2:

• Write down the OR The Formula is,

• Here, G4 is Finance and G5 is IT
• The OR formula will compare the cell values with G4 and G5 and then it will highlight the values that matched the conditions.

Step 3:

• Select a formatting style according to your preferences.
• Click OK to get the result.

• Our cells are formatted based on the reference cell values

3. Apply AND Function to Perform Conditional Formatting 

The AND Function also helps you to perform conditional formatting. Here we will apply a new condition. We will highlight the Marketing section if the total sale is greater than 50,000$.

Step 1:

• Following the same procedures discussed above, go to the New Formatting Rule window and apply the AND The formula is,

• Where G4 and G5 is Marketing and 50,000$
• Set the formatting styles and click OK to format the cells.

• The cells are now formatted according to the conditions.

Similar Readings:

• Excel Highlight Cell If Value Greater Than Another Cell (6 Ways)
• How to Do Conditional Formatting Based On Another Cell Range in Excel
• Conditional Formatting on Multiple Rows Independently in Excel
• How to Change a Row Color Based on a Text Value in a Cell in Excel

4. Insert SEARCH Function to Perform Conditional Formatting 

You can Use The SEARCH Function to find and format any specific names in your dataset using conditional formatting. To do this, insert a name that you want to find in the database.

Step 1:

• Apply the SEARCH function to find Alex. The formula is,

• Click OK to continue.

• See, we have highlighted the cells that contain the name, Alex.

5. Identify Empty and Non-Empty Cells Using Conditional Formatting 

Sometimes you have empty cells in your database that you want to highlight. You can do it easily using conditional formatting.

Step 1:

• Open the New Formatting Rule window and select Format Only the Cells that Contain

• Select Blank from the options

• Set the Formatting and click OK to continue.

• The blank cells are now identified.

6. Find Out Above or Below Average Values Applying Conditional Formatting 

Step 1:

• In order to find above or below average values, apply this formula,

• OK to get the result. That’s how you can find below or above-average values.

Quick Notes

•  Once the formatting is applied you can clear the rules.
•  We used the Absolute Cell references ($) to block the cells to get the perfect result.
• When you want to find out case sensitive name, you can use the FIND function instead of the SEARCH function

Conclusion

Six different ways to perform conditional formatting based on another cell are discussed in this article. Do comment if you have any questions or queries. If you have any thoughts regarding this article, you are most welcome to share your thoughts in the comment section.

Further Readings

• Conditional formatting based on another cell text [5 ways]
• Apply Conditional Formatting Based on Date in Another Cell
• How to Use Conditional Formatting in Excel [Ultimate Guide]
• Excel Conditional Formatting Formula