WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Conditional Formatting Based on Another Cell Value

Conditional Formatting Based on Another Cell Value

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

How to use Conditional Formatting Based On Another Cell Value?

We can highlight an excel row based on cell values using conditional formatting using different criteria.

  • Criteria #1 – Text criteria
  • Criteria #2 – Number criteria
  • Criteria #3 – Multiple criteria
  • Criteria #4 – Different color based on multiple conditions
  • Criteria #5 – Where any cell is blank
  • Criteria #6 – Based on a drop-down selection

For highlighting a row or cells based on the value in another cell, let’s take the below example to understand this.

Example

You can download this Conditional Formatting based on Another Cell Excel template here – Conditional Formatting based on Another Cell Excel template

Below is the list of online products which is ordered by the different-2 customer.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Conditional Formatting Another cell Example 1

We have to identify the records whose delivery status is pending. We want to format the Order ID, Product, Ordered Date, Ordered By, and Delivery Status based on the value in the Delivery Status column.

Conditional Formatting Another cell Example 1-1

Here we want to format the entire row based on Delivery status, whose value is equal to Pending.

When we want to format a cell based on the value in a different cell, we will use a formula to define the conditional formatting rule. It’s a very easy process to set up a formatting formula.

  • First, select the entire data from A3:E13, as shown below.

Conditional Formatting Another cell Example 1-2

  • Go to the HOME tab.
  • Click on Conditional Formatting. Choose the New Rule option.

Conditional Formatting Another cell Example 1-3

  • It will open a dialog box for setting a new rule. Refer below screenshot.

Conditional Formatting Another cell Example 1-4

  • This dialog box has many options.
  1. I want to highlight all cells based on their values.
  2. If it contains a value
  3. Only top or bottom ranked values.
  4. Values that are above or below average
  5. Unique or duplicate values
  • Use a formula to determine which cells to format.
  • Now we will select the last option, “Use a formula to determine which cells to format.”
  • We need to set up a formatting formula so that it returns a true or false value.
  • If the value is true, it will apply the desired formatting in excel. Otherwise, the formatting is not applied.
  • In the formula input box, enter the formula as shown below:

=$E3=”Pending”

Refer to the below screenshot.

Example 1-5

  • Click on the Format button; then, a format dialog box will appear; set the color in which you want the row gets highlighted. Refer below screenshot.

Example 1-6

  • Click on the FILL tab and choose a color as per your requirement and click on Ok.

Example 1-7

This will highlight all the rows whose Delivery Status is “Pending.”

Example 1-8

How Does it Work?

  • The conditional formatting option checks each cell in the selected range for the condition or formula specified by us.
  • Our formula is =$E3=”Pending”
  • It will analyze each cell in a row no.4. Starts from Cell A4 will check whether the cell E4 has the delivery Status Pending or not. If it does, that rows get highlighted else, it doesn’t.
  • We have used the $ sign before the column alphabet ($E3).
  • We have locked the column E, Delivery Status, which we are looking for.
  • When Cell A4 is being checked for the condition, it will check Cell E4.
  • When Cell A5 is checked for the condition, it will check Cell E5 and so on.
  • This process will highlight the entire rows having Pending status.

Things to Remember

  • In this article, we have used text criteria for highlighting the row or cells based on another cell value.
  • Similarly, we can use the other criteria by entering different conditions under the Formula text box, depending on your requirement.

Recommended Articles

This has been a guide for Conditional Formatting based on Another Cell Value in Excel. Here we discuss how to Use Conditional Formatting based on Another Cell Value along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Use Conditional Formatting for Dates
  • Conditional Formatting with Formulas
  • Conditional Formatting for Blank Cells
  • VBA Conditional Formatting
  • Formatting Time in Excel
4 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download Conditional Formatting based on Another Cell Excel template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More