How to Do Conditional Formatting Based On Another Cell in Excel

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 workload and it can improve your efficiency. Today in this article, we will demonstrate how to perform conditional formatting based on another cell in Excel.

How to Do Conditional Formatting Based on Another Cell in Excel: 6 Easy Ways

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 apply conditional formatting to highlight 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 to.

• 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.

2. Use OR Function to Perform Conditional Formatting

You can use the OR function to apply conditional formatting based on multiple independent conditions. If any of the set conditions are met, cells will be highlighted.

For example, 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

Read More: Conditional Formatting Based on Multiple Conditions in Excel

3. Apply AND Function to Perform Conditional Formatting

The AND function also helps you to perform conditional formatting when you are trying to highlight cells that meet all conditions.

Let’s see the following example. 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 following formula.

• 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.

4. Insert SEARCH Function to Perform Conditional Formatting

You can use the SEARCH function to find and format rows that contain any specific names 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 may want to highlight empty cells in your database. 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

Download Practice Workbook

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

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.

You May Also Like to Read

• Conditional Formatting Based on Multiple Values of Another Cell
• Conditional Formatting Based on Another Cell Range
• Conditional Formatting Entire Column Based on Another Column
  
• Highlight Cell If Value Is Greater Than Another Cell
• VBA Conditional Formatting Based on Another Cell Value