Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles CONDITIONAL FORMATTING Using Conditional Formatting in Excel Based on Another Cell’s Value

Using Conditional Formatting in Excel Based on Another Cell’s Value

Conditional Formatting is a feature in Excel that allows us to change the format of cells based on a set of rules or conditions.  This step by step tutorial will assist all levels of Excel users in applying conditional formatting based on another cell’s value.

 Figure 1.  Final result: Conditional formatting in Excel based on another cell’s value

Setting up the Data

Our table consists of three columns:  Name (column B), Course (column C) and Score (column D).  In cell F3, we enter our criteria which is the passing score of 80.  We want to use conditional formatting to highlight names of students who have scores greater than or equal to 80.  

Figure 2.  Sample data for conditional formatting based on another cell’s value

Apply conditional formatting based on another cell’s value

We want to highlight the names through conditional formatting based on the passing score of 80 in cell F3.  Let us follow these steps:

Step 1. Select the cells we want to highlight.  In this case, select cells B3:B9.

Figure 3.  Selection of the data range for conditional formatting

Step 2. Click the Home tab, then the Conditional Formatting Menu and select “New Rule”. The New Formatting Rule dialog box will pop up.  

Figure 4.  Creation of a new rule in conditional formatting

Step 3. Select the Rule Type “Use a formula to determine which cells to format” and enter this formula in the dialog box : =D3>=$F$3

Our formula =D3>=$F$3 serves as the condition or rule that will trigger the conditional formatting.  For every name in column B, if the “Score” in column D is greater than or equal to F3 or “80”, the format will be changed.  

The dollar sign “$” fixes the cell F3 which is our basis for changing the format of the names in column B.  

Figure 5.  Entering the formula as a condition or formatting rule

To change the format, let us proceed to the next step.  

Step 4. Click “Format” and then decide on the new format to apply to the cells in column B.  We can change the font, borders or fill the cells with different colors.

Example:

Select “Fill” and choose Orange, Accent 6, Lighter 60% and click OK.  

Figure 6.  Selection of the format to use

Figure 7.  Completion of the new formatting rule with formula and selected format

This rule highlights the names that satisfy the condition of Score >= 80.     As a result, the names Belle, Diane and Emily are highlighted as shown below.

Figure 8.  Output: New conditional formatting rule reflected in cells B4, B6 and B7

With conditional formatting, we are able to change the format of the names in column B based on the scores in column D that satisfy the condition of the passing score in another cell, F3.  

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc