Conditional Formatting with Formulas

Conditional Formatting with Formulas in Excel

By default excel has already provided us with many types of conditional formatting available to do with our data but if any user wants the formatting done on a specific based criteria or formula we can do so, in the home tab of conditional formatting section when we click on new rule we will find an option for conditional formatting with formulas where we can write formulas which will define the cells to format.

Conditional formatting with Formula is nothing but changing the format of the cells based on the condition or criteria given by the user. You might have used conditional formatting to highlight the top value in the range, highlight duplicate values as well.

A good thing is we have formulas in conditional formatting in excel. All the formulas should be logical the result will be either TRUE or FALSE. If the logical test pass then we will get conditional formatting and if the excel logical test fails then we will get nothing.

Overview

Let me introduce you to the conditional formatting with formula window.

In the below section of the article, we will see a wide variety of examples for applying conditional formatting with formulas.

How to Use Excel Conditional Formatting with Formulas?

#1 – Highlight Cells which has Values Less than 500

Assume below are the sales numbers you are working with and below is the sales price per unit.

• Step 1: Go to Conditional Formatting and click on New Rule.

• Step 2: Now select “Use a formula to determine which cells to format”.

 

• Step 3: Now under “Format values where a formula is true” apply the formula as A2<500 and then click on Format to apply the excel formatting.

• Step 4: Select the format as per your wish.

• Step 5: Now you can see the preview of the format in the preview box.

• Step 6: Now click on OK to complete the formatting. Now it has highlighted all the cells which have a number <500.

#2 – Highlight One Cells Based on Another Cell

We can highlight the one cell based on another cell value. Assume we have Product Name and Product Price data in the first two columns.

From the above table, we need to highlight the products if the sale price is >220.

• Step 1: Select the product range and then, Go to conditional formatting and click on New Rule.

• Step 2: In the formula apply the formula as B2 > 220.

• Step 3: Click on Format key and apply the format as per your wish.

• Step 5: Click on OK, we have formatting ready.

#3 – Highlight All the Empty Cells in the Range

Assume below is the data you have.

In the above data, you have to highlight all the cell which are blank or empty. We need to use the ISBLANK formula in the Excel conditional formatting.

Apply below formula in the formulas section.

Select the formatting as per your wish.

Click on ok it will highlight all the empty cells in the selected range.

#4 – Use AND Function to Highlight Cells

Consider the below data for this formula.

If the region is Central and the sales value is >500 we need to highlight the complete row.

• Step 1: Select the entire data first.

• Step 2: Here we need to use AND excel function because we need to test two conditions here and both the conditions should be TRUE. Apply the below formula.

• Step 3: Click on Format.

• Step 4: Go to FILL and select the required color and effects.

• Step 5: Click on OK. We will see the rows highlighted if both the conditions are TRUE.

#5- Use OR Function to Highlight Cells

Consider the below data for the example.

In the above data range, I want to highlight the city names Bangalore and Mysore.

• Step 1: Select the data first and go to Conditional Formatting then, click on New Rule.

• Step 2: Since we need to highlight either of the two value cells, apply the OR excel function.

• Step 3: Click on the format and select the required format.

• Step 4: Once the formatting is applied click on OK to complete the task. A formula would highlight all the cells which have values Bangalore & Mysore.

#6 – Use COUNTIF Function to Highlight Cells

Assume you are working with the customer database. You need to identify all the clients whose names appearing more than 5 times in the list. Below is the data.

Select the data and apply the COUNTIF formula in the formula section.

Click on the format and apply the required formatting.

Now formula will highlight all the names if the count if more than 5.

Amber is the only name that appears more than 5 times on the list.

#7 – Highlight Every Alternative Row

We can also have the formula to highlight every alternative row in excel of the data. Assume below is the data you are working on.

Select the data and apply the below formula.

=MOD(ROW (), 2)

Apply to format as per your wish and click on OK, we would have every alternative row highlighted by the formula.

#8 – Highlight Every Alternative Column

Like how we have highlighted every alternative row similarly we can highlight every alternate column. Consider the same data for the example.

Apply the below formula.

=MOD(COLUMN(), 2)

Copy the above formula and apply it in the formula section of the conditional formatting.

Click on OK, it will highlight the alternate row.

If you wish to leave out the first column and highlight from the second column you need to use the below formula.

It would highlight the alternative column starting from the second column.

Things to Remember

• Conditional formatting accepts only logical formulas whose results are only either TRUE or FALSE.
• Preview of conditional formatting is just an indication of how formatting looks.
• Never use absolute reference as in the formula. When you are applying the formula and if you select the cell directly it would make it as an absolute reference, you need to make it to a relative reference in excel.

Recommended Articles

This has been a guide to Excel Conditional Formatting with Formulas. Here we discuss how to use Conditional formatting using formulas like COUNTIF, AND, OR, MOD, etc along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

• How to Use Conditional Formatting for Dates?
• How to Create Excel KPI Dashboard?
• Conditional Formatting in Excel VBA
• VBA COUNTIF