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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

Highlight Entire Row Based On Another Cell Value


1. Highlight the Entire Row Based on Another Cell Value

You can apply conditional formatting to highlight row based on single-cell values. 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.

Highlight Entire Row Based On Another Cell Value

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

Highlight Entire Row Based On Another Cell Value

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

Highlight Entire Row Based On Another Cell Value

Step 2:

  • In the formula section, insert this formula.
=$C4=$G$4
  • This formula will compare the dataset cells with the name Luke (G4). When the value will match, it will highlight the cell.

Highlight Entire Row Based On Another Cell Value

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.

Highlight Entire Row Based On Another Cell Value

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

Highlight Entire Row Based On Another Cell Value

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

Highlight Entire Row Based On Another Cell Value

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

Highlight Entire Row Based On Another Cell Value


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.

Use OR Function to Perform Conditional Formatting

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.

Use OR Function to Perform Conditional Formatting

Step 2:

  • Write down the OR The Formula is,
=OR($D4=$G$4,$D4=$G$5)
  • 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.

Use OR Function to Perform Conditional Formatting

Step 3:

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

Use OR Function to Perform Conditional Formatting

  • Our cells are formatted based on the reference cell values

Use OR Function to Perform Conditional Formatting


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

Apply AND Function to Perform Conditional Formatting

Step 1:

  • Following the same procedures discussed above, go to the New Formatting Rule window and apply the following formula.
=AND($D4=$G$4,$E4>$G$5)
  • Where G4 and G5 is Marketing and 50,000$
  • Set the formatting styles and click OK to format the cells.

Apply AND Function to Perform Conditional Formatting

  • The cells are now formatted according to the conditions.

Apply AND Function to Perform Conditional Formatting


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.

Insert SEARCH Function to Perform Conditional Formatting

Step 1:

  • Apply the SEARCH function to find Alex. The formula is,
=SEARCH($G$4,$C4)>0
  • Click OK to continue.

Insert SEARCH Function to Perform Conditional Formatting

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

Insert SEARCH Function to Perform Conditional Formatting


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

Sometimes you may want to highlight blank cells in your database. You can do it easily using conditional formatting.

Identify Empty and Non-Empty Cells Using Conditional Formatting

Step 1:

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

Identify Empty and Non-Empty Cells Using Conditional Formatting

  • Select Blank from the options

Identify Empty and Non-Empty Cells Using Conditional Formatting

  • Set the Formatting and click OK to continue.

Identify Empty and Non-Empty Cells Using Conditional Formatting

  • The blank cells are now identified.

Identify Empty and Non-Empty Cells Using Conditional Formatting


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,
=$E4<AVERAGE($E$4:$E$13)

Find Out Above or Below Average Values Applying Conditional Formatting

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

Find Out Above or Below Average Values Applying Conditional Formatting


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

<< Go Back to Conditional Formatting with Multiple Conditions | Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo