EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Excel Conditional Formatting Based on Another Cell Value
 

Excel Conditional Formatting Based on Another Cell Value

Steffi Madari
Article bySteffi Madari
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 19, 2023

Conditional Formatting Based on Another Cell

 

 

Conditional Formatting Based on Another Cell

Conditional Formatting can be a lifesaver when you have a spreadsheet full of data and want to highlight only the necessary information. However, conditional formatting is not just limited to highlighting specific cells. It also helps format a cell based on the value of another cell. This type of formatting is known as conditional formatting based on another cell value.

Let’s say you’re a teacher and keep track of your student’s grades using Excel. You can use conditional formatting based on another cell to highlight grades equal to or greater than 90. This way, you can quickly identify students excelling in your class. You can even highlight those grades in green, making them easier to identify.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

In this article, we will understand the basics of conditional formatting based on another cell and provide examples of how it can enhance the presentation and analysis of spreadsheet data. Before moving to the complex examples, let’s first understand the location of the conditional formatting option in Excel.

Where is Conditional Formatting in Excel?

The Conditional Formatting option is under the Home tab in the Styles group section. By clicking on conditional formatting, you will find many options like Highlight Cells Rules, Data Bars, Manage rules, etc. 

Where is Conditional Formatting in Excel

How to Apply Excel Conditional Formatting Based On Another Cell Value?

You can download this Conditional Formatting Based on Another Cell Excel Template here – Conditional Formatting Based on Another Cell Excel Template

Example #1

Method 1: Highlight Single Cell Value

Below is simple data on employees’ current status as “Active” and “Left”. Here, “Active” means employees still in service, whereas “Left” denotes employees who have left the organization.

In this method, you will learn how to highlight all the employees with the status “Left” in the below data using the single-cell value formatting technique. It will highlight only the individual cell which contains the text “Left”.
Example 1

Please follow the below steps to learn this technique.

Step 1: Select the entire data.
Conditional Formatting Example 1-1-1

Step 2: Go Home, select Conditional Formatting > Highlight Cells Rules > Text That Contains.
Conditional Formatting Example 1-1-2

Step 3: When you click on the Text that contains, a new window will open as shown below. Enter the text value you want to highlight, i.e., Left, and then select the color format as Light Red Fill with Dark Red Text. Click on the OK option to complete this task.

Note: You can change color according to your preference.

Conditional Formatting Example 1-1-3

You can also see the preview of this task in the data. The output is displayed below:
Result of Example 1-1

Method 2: Highlight the Entire Row Based on One Single Cell Value

In the previous example, we have seen how to highlight a single cell based on the cell value. In this example, you will learn how to highlight an entire column based on the single-cell value. Please follow the below steps to accomplish this task.

Step 1: Enter the “Left” word in cell C1.
Conditional Formatting Example 1-2-1

Step 2: Select the entire data. Now, go to Home, click Conditional Formatting > New Rule.
Conditional Formatting Example 1-2-2

Step 3: The New Formatting Rule window will open; select Use a formula to determine which cells to format.
Conditional Formatting Example 1-2-3

Step 4: In the formula section, enter the formula =$B2=$C$1 (as shown in the image below) and click Format.
Conditional Formatting Example 1-2-4

The formula states that If the B2 cell is (this is not an absolute reference, but the only column is locked) equal to the value in the C1 cell (this is an absolute reference), then do the formatting.

Step 5: In the Format Cells window, click the Fill option, select the formatting color, and click the OK button. The preview section will display the color you choose.
Conditional Formatting Example 1-2-5

Step 6: After opening the New Formatting Rule window, click OK again to format the rows if the cell value equals the left text.
Conditional Formatting Example 1-2-6

The outcome is as follows:
Conditional Formatting Example 1-2-7

Now try changing the cell value in C1 from Left to Active. It will highlight all the Active row employees.
Result of Example 1-2

Example #2

We have an employee database with names, salaries, and departments. Here we want to highlight the departments of Marketing and IT. We must apply the formula in the conditional formatting tab to do this task.

Step 1: Select the entire data.
Example 2

Step 2: Click on Conditional formatting > New Rule.
Conditional Formatting Example 2-2

Step 3: Select Use a formula to determine which cells to format.
Conditional Formatting Example 2-3

Step 4: In the formula section, enter the formula =OR($C2=”Marketing”,$C2=”IT”) and click on the Format button.
Conditional Formatting Example 2-4

Note: The OR function tests whether the values in column C are equal to Marketing or IT. If any cell value is found true, it will highlight the row for you.

Step 5: Click on Format and select the color you want to highlight. The preview section will display the color.
Conditional Formatting Example 2-5

Step 6: Click on OK to complete the task.
Conditional Formatting Example 2-6

The Conditional Formatting formula has highlighted the departments of Marketing or IT.
Result of Example 2

Example #3

In this example, we want to highlight all the Marketing department rows with a salary of more than 50,000.
Step 1: Select the entire data.
Example 3

Step 2: Click on Conditional formatting>New Rule.
Conditional Formatting Example 3-2

Step 3: In the New Formatting Rule window, select Use a Formula to determine which cells to format.
Conditional Formatting Example 3-3

Step 4: Under the formula section, enter the formula =AND($C2=”Marketing”,$B2>50000) and click on Format.
Conditional Formatting Example 3-4

Step 5: Select the color you want to use for highlighting. We have selected the below color shown in the below image.
Conditional Formatting Example 3-5

Note: The AND is a logical function that tests if the column C values are equal to the Marketing department and column B values are greater than 50,000. If both conditions are true, it will highlight that row.

Step 6: Click on the OK button.
Example 3-step 6

The above formula will highlight all the rows with Marketing and salary of more than 50,000.
Result of Example 3

In the above image, the marked yellow rows are also Marketing, but the salary is less than 50,000, so AND function excludes these rows from highlighting.

Things to Remember

  • You can use conditional formatting to spot variances, specific words, or characters in cell values.
  • You can also differentiate your data by changing the cell color, border styles, font color, etc.
  • To create conditional formatting formulas and rules, you must understand the range and condition clauses.
  • To format cells, you can use predefined features like data bars, color shades, and icons.
  • You can delete/clear the highlights after applying the formatting rule.
  • Suppose you plan to add additional data and want the conditional formatting rule to apply automatically to new data. In that case, you can convert that data into a table from the insert tab. All new rows will automatically receive conditional formatting rules.

Common Mistakes to Avoid

  • You must know what condition rules will give the desired results, like greater than, less than, or equal to.
  • You must know which is an absolute cell & relative cell to address them correctly.
  • Avoid using the symbol “$” in unnecessary places. Please remember that =D1=1, =$D$1=1, and =D$1=1 will produce different results.
  • Avoid including the column headers when selecting cells or data.

Best Practices for Sharing Spreadsheets with Conditional Formatting

  • Check all the conditional formatting rules and formulas applied to the data before sharing the spreadsheet.
  • If you do not want to share the spreadsheet with highlights, remove or delete the highlighted cells/rows using the “Clear rules” option. Click Home>Conditional formatting>Clear rules>Clear Rules from Selected cells

You can give members like edit/view(read-only) access to avoid unnecessary changes in the spreadsheet.

Frequently Asked Questions (FAQs)

Q1. What is conditional formatting in Excel?
Answer: Conditional formatting in Excel mainly highlights specific information or data in a worksheet. It changes the appearance of the cell for easy identification.

Q2. How to remove conditional formatting in Excel?
Answer: Firstly, select the data where conditional formatting rules are applied. To clear the
highlight, follow the below steps.
Select Conditional formatting>Clear rules>Clear Rules from Selected cells

Q3. How to copy conditional formatting in Excel?
Answer: Please follow the instructions to copy the conditional formatting rule using Format Painter.

  • Select the cells that have the conditional formatting rule you want to copy.
  • Go to Home> Clipboard>Format Painter.
  • Select the cells to which you want the copied formatting to apply.

Recommended Articles

This article has been a guide to “Conditional Formatting Based On Another Cell”. Here we discuss applying conditional formatting in Excel based on single and other cell values with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

  1. Excel Data Formatting
  2. Excel Data Validation
  3. AND Function in Excel
  4. OR Excel Function

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Conditional Formatting Based on Another Cell Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Conditional Formatting Based on Another Cell Excel Template

EDUCBA

डाउनलोड Conditional Formatting Based on Another Cell Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW