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

Below is a database containing the ID, Name, Section, and Total Sales of some Sales Representatives. We will format some cells using conditional formatting based on their names, sections, or total sales.

Highlight Entire Row Based On Another Cell Value


Method 1 – Highlight the Entire Row Based on Another Cell Value

Steps:

  • Select the entire dataset.
  • In your Home Tab, go to Conditional Formatting in Style Ribbon.
  • Click on it to open the available options
  • 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.

Highlight Entire Row Based On Another Cell Value

  • 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

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

Highlight Entire Row Based On Another Cell Value

  • The Fill cells option helps you highlight the cells with different colors. Choose any color you like.

Highlight Entire Row Based On Another Cell Value

  • 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


Method 2 – Use the OR Function to Perform Conditional Formatting

Steps:

  • Go to the New Formatting Window: Home → Conditional Formatting → New Rule
  • Select Use a Formula to Determine the Cells to Format.

Use OR Function to Perform Conditional Formatting

  • Enter the OR Formula:
=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 highlight the values that match the conditions.

Use OR Function to Perform Conditional Formatting

  • 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


Method 3 – Apply the AND Function to Perform Conditional Formatting

Steps:

  • 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


Method 4 – Insert the SEARCH Function to Perform Conditional Formatting

Steps:

  • 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

We have highlighted the cells that contain the name Alex.

Insert SEARCH Function to Perform Conditional Formatting


Method 5 – Identify Empty and Non-Empty Cells Using Conditional Formatting

Steps:

  • 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


Method 6 – Find the Above or Below Average Values Applying Conditional Formatting

Steps:

  • 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

  • Click 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 for the perfect result.
  • When you want to find a sensitive name, you can use the FIND function instead of the SEARCH function.

Download the Practice Workbook

Download this book to practice the task.


You May Also Like to Read

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

Get FREE Advanced Excel Exercises with Solutions!
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