Pinterest Pixel

How to Use Conditional Formatting Based on Another Cell

One of the most common questions in Microsoft Excel is how do you use conditional formatting based... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Conditional Formatting Based on Another Cell

One of the most common questions in Microsoft Excel is how do you use conditional formatting based on another cell’s value?

Say you want a cell to be highlighted once its’ values hit a certain threshold. If you can format the appearance of cells, your data will be easier to understand. You will also be able to focus on the formatted cells leading to streamlined decision-making. In this article, we will explore the steps on how to use conditional formatting based on another cell.

Our final product will look like this:

Conditional Formatting Based on Another Cell

In this example, we want to highlight the name of the students in column A who have a mark above 80 on column C, indicating their passing status.

Let’s go through these steps thoroughly!

Download the Excel Workbook below to follow along How to Use Conditional Formatting Based on Another Cell! –

download excel workbook ConditionalFormattingBasedonAnotherCell.xlsx

Based on One Cell


The first step is to create your new conditional formatting rule.

Highlight the range of names from A2 to A13.

Conditional Formatting Based on Another Cell

Go to the Home tab and click on Conditional Formatting.

Select New Rule.

Conditional Formatting Based on Another Cell

Click on Use a formula to determine which cells to format.

Conditional Formatting Based on Another Cell

Under the Format values where this formula is true, type in the = sign and click on C2.

Conditional Formatting Based on Another Cell

As you can see, Excel will automatically consider cell C2 as an absolute reference as it put the $ sign in front of both column C and row 2. To allow this formula to copy down to the other rows, it is important for us to delete the $ sign in front of row 2 to make it a relative reference.

Type in the final formula as =$C2>80.

Conditional Formatting Based on Another Cell

Click on Format to decide on how you want the names that meet the condition to look. You can modify the cells based on font color, background color, borders, and other formatting choices. In our example, we will select bold and the color green. Click OK.

Conditional Formatting Based on Another Cell

Conditional Formatting Based on Another Cell

As you can see, the names of the students in column A are highlighted based on their passing score on column C.

Bonus: Formatting Based on Multiple Cells


What if we wanted to highlight cells based on multiple conditions from multiple cells?

Let’s say we only want to highlight the student names in Column A if their status in Column B is Active AND their score in Column C is higher than 80.

Select the range.

Conditional Formatting Based on Another Cell

Go to the Home tab and click on Conditional Formatting.

Select New Rule.

Conditional Formatting Based on Another Cell

Click on Use a formula to determine which cells to format.

Conditional Formatting Based on Another Cell

Enter the formula =AND($C2>80, $B2=“Active”). This formula is basically saying we want to format values where this formula is true if the cell in column C is greater than 80 AND if the cell in column B is active.

Conditional Formatting Based on Another Cell

Choose the Format you would like the cells to be in and click OK.

Conditional Formatting Based on Another Cell

Conditional Formatting Based on Another Cell

As you can see, only the names of the students in column A are highlighted based on their passing score above 80 in column C and their active status in column B.

Conclusion:
There you have it! Conditional formatting based on another cell is one of the most useful ways to bring clarity to your data. It works perfectly well with formulas, as well. Make sure you use these skills the next time you work on a spreadsheet!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!