Excel Conditional Formatting Formula

How to Use Formula with Excel Conditional Formatting

In the following image, we have a very simple dataset. With Excel conditional formatting with formula, we will highlight the values that are greater than 3.

Steps:

Select the range of cells.
Go to Home, click on the Conditional Formatting drop-down, then select New Rule from the drop-down menu.

The New Formatting Rule dialog box appears.
Select Use a formula to determine which cells to format
In the Format values where this formula is true: field, we input this formula: =B3>3

Click on the Format command. Choose a Fill Color and click OK.

Click OK in the dialog box.

The range on the spreadsheet was B3:B6. It will now look like this.

Using Conditional Formatting with Formula in Excel: 21 Examples

Example 1 – Format Text Values

Let’s consider this dataset containing both numeric and string values in it.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in Format values where this formula is true.

=ISTEXT(B5)

Select your preferred format type.

Click on OK.

Example 2 – Highlight Cells That Are Equal to Another Cell

We are going to format the values that match the cell value of E5.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=B5=$E$5

Select your preferred format type.

Click on OK.

Example 3 – Conditional Formatting in Excel Based on Another Cell

We will format a dataset based on whether it is larger or smaller than another cell’s value.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=C5>$E$5

Select your preferred format type.

Click on OK.

You can repeat the same process for the lower values and end up with a dataset like this.

Example 4 – Conditional Formatting Using the IF Formula in Excel

We’ll determine whether the product turned a profit and highlight the cell.

Steps:

Select cell E5 and insert the following formula.

=IF(D5>C5,"Profit","Loss")

Hit Enter.

Click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=E5="Profit"

Select your preferred format type.

Click on OK.

Example 5 – Highlight Cells Using Multiple Conditions

Let’s go back to the first dataset. We are going to highlight all the cells that are either 5, 6, or contain the text “cat”.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Edit Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=OR(B5=5,B5=6,B5="cat")

Select your preferred format type.

Click on OK.

Example 6 – Highlight Duplicate Rows

We are going to highlight cells in the whole rows where the whole row matches with another one.

We can see the third and sixth row fully match.

Steps:

Select cell F5 and use the following formula.

=CONCATENATE(B5,C5,D5,E5)

Press Enter.

Select the cell again and click and drag the fill handle icon down to fill up the rest of the cells with the formula for their references.

Select the range B5:B10.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Edit Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=COUNTIF($F$5:$F$10,$F5)>1

Select your preferred format type.

Click on OK.

Right-click on the column header of F and select Hide from the context menu.

Here’s the sheet.

Example 7 – Highlight Cells Containing Formulas

Let’s take a look at a dataset that contains formulas to complete.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=ISFORMULA(B5)

Select your preferred format type.

Click on OK.

Example 8 – Highlight Sales from a Particular Region

We are going to highlight sales from a dataset that belongs to a particular region.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=$D5="Arizona"

Select your preferred format type.

Click on OK.

Example 9 – Highlight Column Differences

We can also highlight rows that have different columns than their adjacent ones.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=$B5<>$C5

Select your preferred format type.

Click on OK.

Example 10 – Using a Formula to Highlight Missing Values

We will use a formula to highlight missing values in conditional formatting in Excel.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=COUNTIF($D$5:$D$10,$B5)=0

Select your preferred format type.

Click on OK.

Example 11 – Creating a Simple Search Box to Highlight Cells

We will put a value in cell E4, and Excel will highlight the value in the range, all with the conditional formatting with the formula method.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=ISNUMBER(SEARCH($E$4,B5))

Select your preferred format type.

Click on OK.

The texts containing the word game will be marked.

If we change the value in cell E4, the highlights will change.

Example 12 – Highlight Values That Are Lower Than Average

Let’s revisit one of the datasets from before.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=E5<AVERAGE($E$5:$E$10)

Select your preferred format type.

Click on OK.

Example 13 – Highlight Values That Are Greater Than Average

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Edit Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=E5>AVERAGE($E$5:$E$10)

Select your preferred format type.

Click on OK.

Example 14 – Find the Nearest Value Including the Exact Match

We are going to find the value that is closest to the one in cell C13. If there is the same value in the dataset, it will highlight that cell instead of the closest value.

Steps:

We need to find the smallest difference from this value in that set of data. For that, select cell C14 and insert the following formula.

=MIN(ABS(B5:D11-(C13)))

Press Enter.

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=OR(B5=$C$13-$C$14,B5=$C$13+$C$14)

Select your preferred format type.

Click on OK.

Example 15 – Find the Top 3 Values

Let’s go back to the random set of data.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=B5>=LARGE($B$5:$D$11,3)

Select your preferred format type.

Click on OK.

Example 16 – Find the Bottom 3 Values

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=B5<=SMALL($B$5:$D$11,3)

Select your preferred format type.

Click on OK.

Example 17 – Show the Temperature with a Color Scale

We have two columns because we will use a formula to change the color based on the input in cell E5. There is a blank row at the start of the dataset. We will change the color scheme of temperature values based on the current temperature.

Steps:

Select cell C6 and insert the following formula in it.

=IF(B6=$E$5,"",IF(AND(B6<$E$5,$E$5<B5),"",B6))

Press Enter.

Select the cell again and click and drag the fill handle icon to replicate the formula for the rest of the cells in the column.

Go to Conditional Formatting from the Styles group of the Home section.
Hover over Color Scales.
Select your preferred color scale.

The temperature scale for conditional formatting based on the formula for current temperature will now be complete.

If we change the value of the current temperature in cell E5, the temperature scale will change accordingly.

Example 18 – Highlight Alternate Rows with Conditional Formatting

We will highlight alternate rows with a color.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=INT(MOD(ROW(),2))

Select your preferred format type.

Click on OK.

Example 19 – Highlight Cells with Error

Consider this simple dataset.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Edit Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=ISERROR(B5)

Select your preferred format type.

Click on OK.

Example 20 – Create a Checklist with Conditional Formatting

We will make a checklist beside a set of data. With the options checked, the original data will change its format.

Steps:

Go to the Developer tab on your ribbon.
Select Insert from the Controls group section.

Select the Check box (Form Control) from the drop-down menu.
Place the box in its appropriate place.

Right-click on the box and select Format Control from the context menu.

Go to the Control tab on the box and select cell C5 as its linked cell.

Click on OK.
Remove the alt text and place the checkbox in the middle of the cell.

Repeat the process for all of the boxes.

Once checked, there will be a TRUE/FALSE value on the cells depending on it.

Use a white font color to make them invisible.

Select the task range you want to format.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=C5=TRUE

Select your preferred format type. We chose a strikethrough font.

Click on OK.

Example 21 – Highlight Weekends in a Week

We will use the OR and WEEKDAY functions to create this formula.

Steps:

Select all the cells in the dataset excluding headers.
Go to the Home tab on your ribbon.
Select Conditional Formatting from the Styles group section.
Select New Rule from the drop-down menu.

In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type.
Insert the following formula in the Format values where this formula is true.

=OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7)