“Use a formula to determine which cells to format” rule is used to apply the formatting on the excel cell as our desired condition or a condition which is not available in Excel Conditional Formatting. We have to put a logical formula in the formula box. if this formula returns True then formatting will be applied.
Highlight cells by using formula:
Let’s say we need to highlight the Even number only. Below are the steps-
- Select the range on which you want to apply the conditional formatting
- As in below image select “B2:G9”
![Data sets to apply the conditional formatting](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Comditinal-Formatting-1.jpg)
- Go to Home tab>>Conditional Formatting>>New Rules
![New Rules Option in conditional Formatting](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-32.jpg)
- New formatting rule window will be opened.
- Select the “Use a formula to determine which cells to format“
- Put formula “=Iseven(b2)” in the box.
- We have taken B2 because it is active cell
![Highlight even number](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-75.jpg)
- Click on OK button to apply this conditional formatting.
- Data set with this conditional formatting will look like below image.
![Even Number highlighted](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-76.jpg)
Highlight Column by using formula:
we can highlight the column in the selected range by using the formula.
For example, we need to highlight the column which have “Location-3” in the header.
- Select the entire range including headers
- Put the formula =A$1=”Location-3″
- To highlight the column, we have put $ sign before the raw number. That is why we have taken A$1 in the formula.
- Give the format whatever you want to apply in the excel cells.
![Highlight the column](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-77.jpg)
- Click on OK button to apply this conditional formatting.
- Data set with this conditional formatting will look like below image.
![Location-3 Column highlighted](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-78.jpg)
Highlight Row by using formula:
we can highlight the row in the selected range by using the formula.
For example, we need to highlight the row for “Friday”
- Select the entire range.
- Put the formula =$A1=”Friday”
- To highlight the row, we have put $ sign before the column (before A). That is why we have taken $A1 in the formula.
- Give the format whatever you want to apply in the excel cells.
![Highlight the row](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-79.jpg)
- Click on OK button to apply this conditional formatting.
- Data set with this conditional formatting will look like below image.
![Row highlighted for Friday](https://www.pk-anexcelexpert.com/wp-content/uploads/ConditionalFormatting/Conditional-Formatting-80.jpg)