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.

Highlight Entire Row Based On Another Cell Value


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.

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.

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. 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. 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 AND The formula is,
=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 any specific names in your dataset 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 have empty cells in your database that you want to highlight. 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


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.


Similar Articles to Explore

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo