There’s barely an Excel user who is not aware of the Conditional Formatting feature of Excel. For some quick reiteration, it allows users to apply specific formatting to a cell from a specified range that meets a specified criterion.
How is it different from conditional formatting based on another cell? Simple conditional formatting checks a specified range against the supplied criterion and formats the same range. However, if you want to format individual cells or a range of cells based on another cell’s value, this is known as conditional formatting based on another cell.
Read through the article below to learn all about conditional formatting based on another cell in Excel.
Table of Contents
How to Apply Conditional Formatting Based on Another Cell in Excel?
Before we delve into further details, it is to be noted that conditional formatting based on another cell’s value is not a function different from simple conditional formatting in Excel. In fact, it is only a smart way to work out the basic conditional formatting function of Excel.
Under simple conditional formatting, cells are formatted based on their values or a value that you specify. To format cells based on the value of another cell, a formula needs to be devised. Let’s see how.
Using a formula
Conditional formatting in Excel through a formula is pretty simple as it is logically constructed. Following are the basic steps to be followed for conditionally formatting a cell based on another cell’s value.
Step 1:
Select the cell or range of cells that you want to format.
Step 2:
On the Home Tab, follow the path below to set up a new rule.
Home Tab > Styles > Conditional Formatting > New Rule
This will open up a ‘New Formatting Rule’ window as shown below.
Step 3:
From the option ‘Select a Rule Type’, select the option ‘Use a formula to determine which cells to format’ as shown below.
As highlighted in the image above, the second box headed ‘Edit the Rule Description’ is where the rule is to be set up.
For instance, if you want to format cells based on the value of cell A10, i.e. highlight all cells greater than the value contained in cell A10, you may compose a rule as follows.
= [Cell Range] > $A$10
Step 4:
Next is to choose the Format style that you want to be applied to the cells that meet the specified criterion. To do so, click on the Format button appearing at the bottom right of the window as highlighted below.
This would take you to the ‘Format Cells’ window.
Here, you can toggle between different formatting options ranging from font style to border style to color fill and so on.
Once you have set up the desired formatting settings, click ‘okay’ to preview how the cells would look when formatted.
If everything looks good, go ahead to hit ‘Ok’, and Excel would format the cells that meet the specified conditions.
A Simple Example:
Up till now, we have learned the process to be followed to conditionally format cells based on the values of other cells. Now it’s time we demonstrate the same through a short and basic example.
The data above shows the inventory levels of different products. The minimum inventory level maintained by the Company is 100. How can we find out the products for which the inventory level is below the minimum inventory level threshold?
Here, we want to highlight the Product Items based on the level of inventory maintained for each of them. We can apply conditional formatting to product items by setting the minimum inventory level as the criterion. How? Learn below.
Step 1:
To do so, first, select the row containing the Product items. If you also want the inventory levels to be highlighted, extend the selection to column B too.
Step 2:
Access conditional formatting from the Home Tab as follows.
Home Tab > Style Group > Conditional Formatting > New Rule
Step 3:
From the ‘New Formatting Rule’ window select the option ‘Use a formula to determine which cells to format’ and compose the rule as follows.
=$B2<$B$10
The formula is set up only for the first cell of the column where the formatting is applied. Excel would automatically apply the formula to the entire cell range selected in Step 1.
Logically, this tells Excel to highlight all those cells from the selected range where corresponding cell values of Column B are lesser than the value populated in B10 i.e. 100.
To quickly take a look into the range where the formula applies, the formula, and the formatting style, go to:
Home Tab > Style Group > Conditional Formatting > Manage Rules > Current Selection
This will show a list of all the rules applied to the current selection of cells.
Step 4:
Once the formula is set up, next you need to go to the Format button to specify the formatting you want to be applied to the cells.
We have set a yellow fill from the Format Tab, and the ‘Preview Pane’ shows as follows.
Step 5:
You are all set. Hit ‘Enter’ to see the results as below.
A Quick Brainteaser!
It is often the case that the source data upon which conditional formatting is to be applied is constantly changing. For example, an Excel sheet where a business records all the sales’ transactions might have new sale transactions added every minute.
If you simultaneously want to highlight all the sales that exceed a certain amount, how can you set up a formula that applies to all existing and new entries automatically?
This can easily be done through the conversion of the source data into a table before conditional formatting is applied to it. For instance, the source data in the above example can be converted into a table as follows.
Select the data > Insert Tab > Table
The conditional formatting applied to the table will automatically cover all additions, changes, or deletions made to the table.
Can we specify more than one condition for conditional formatting?
We can specify more than one condition under the conditional formatting function of Excel. However, this requires using the AND / OR operators.
- The AND operator is used when you want Excel to apply conditional formatting to cells only when all the specified conditions are met.
- The OR operator is used when you want Excel to apply conditional formatting to cells when either of the specified conditions are met.
Let’s see an example of this. The image below represents sales of different products in different regions.
From the above series, we want to highlight products that meet the following two conditions:
- Sales under $100; and
- Sales are made in California
In other words, we want to apply conditional formatting to products based on the values of other cells (sales and region).
Step 1:
This can be done by setting up a formula with the AND operator as follows.
= AND ($B2<100, $C2=”California”)
The formula is set to cover two conditions through the AND operator.
- The first condition $B2<$100 specifies that only those sales are to be highlighted that are under $100.
- The second condition $C2=”California” specifies that only those sales are to be highlighted where the corresponding region is California.
Step 2:
After the formula is set up, choose the format you want to be applied to the cells and hit enter.
Excel highlights those products where sales are under $100, and the sale region is California.
It is only this easy to apply conditional formatting based on another cell. Using the AND / OR logical operators, you can compose many formulas with different conditions.
Conclusion:
Conditional formatting based on another cell is a smart application of simple conditional formatting. By setting formulas, there are endless possibilities for the conditions that you can specify to sort your data. Practicing it with a few sets of varied data will help you master the same in no time. Sometimes, copying the formatting can be enough to do the task.