Conditional Formatting Based on Another Cell Value in Excel: Explained
In this article, you will learn how to apply Conditional Formatting to cells based on another cell value in Excel. Please see Basic Conditional Formatting rules article for steps to perform standard conditional formatting in Excel.
When applying conditional formatting in Excel, there are several options available to base the formatting on other cells. These options allow you to create formatting rules that dynamically respond to the values in other cells. Some of the commonly available options are shown in the image below:
These are some of the common options available for conditional formatting based on other cells in Excel. By utilizing these options creatively, you can visually highlight and analyze data based on specific conditions and comparisons with other cell values.
How to do Conditional Formatting based on another cell ?
You can compare a cell's value to the value in another cell using comparison operators such as greater than (>), less than (<), equal to (=), greater than or equal to (>=), or less than or equal to (<=). This allows you to apply formatting based on the relationship between two cells' values. To perform this follow the steps mentioned below:
Step 1: Select the cells on which you wish to apply the conditional formatting
Step 2: Go to the “Home” menu bar in Excel and select “Conditional Formatting” and select “New Rule”
Step 3: From the first and second drop-down lists under “Rule Type”, choose "Highlight Cells with" and “Cell value”, respectively, and then select a specific rule such as "Greater Than," and then specify the cell based on which you wish to apply the conditional formatting rule - D12 in this case
Step 4: Define how you want to format the cells once the above-defined rule is met, for example in the below case for all projects that have more than $50,000 profit generated will be highlighted in Blue Text & Light Blue Background. Once defined press “Done”
The selected cells will now have conditional formatting as per the rules you just defined.
You can also check if a cell's value contains specific text or numbers by using functions like "Specific Text”. This allows you to format cells based on the presence or absence of certain text.
The selected cells will now have conditional formatting as per the project status shown below.
