Conditional Formatting with Formulas in Excel

Excel, by default, has already provided us with many types of conditional formatting to perform with our data. Still, we want to do the formatting on specific criteria or formulas as a user. We can do that by choosing the “Home” tab of the “Conditional Formatting” section. Then, when we click on the “New Rule,” we can find an option for conditional formatting with formulas where we can insert formulas that will define the cells to format.

The conditional formatting with formula is changing the format of the cells based on the condition or criteria given by the user. For example, you might have used conditional formattingConditional FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more to highlight the top value in the range and duplicate values.

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 passes, we will get conditional formatting. If the excel logical testExcel Logical TestA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more fails, we will get nothing.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Conditional Formatting with Formulas (wallstreetmojo.com)

Overview

Let me introduce you to conditional formatting with the 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

#2 – Highlight One Cells Based on Another Cell

We can highlight one cell based on another cell’s value. For example, assume we have “Product” and “Sales Price” data in the first two columns.

  • Below is the sales price per unit. Then, we must go to “Conditional Formatting” and click on “New Rule.” Now, select “Use a formula to determine which cells to format.” Now, under “Format values where a formula is true,” apply the formula as A2<500 and click on “Format” to use the excel formatting. Then, we must select the format as per our choice. Now, we can see the preview of the format in the preview box. Now, click on “OK” to complete the formatting. As a result, it has highlighted all the cells with a number <500.

We need to highlight the products from the above table if the sale price is >220.

  • Step 1: First, we must select the “Product” range, go to “Conditional Formatting,” and click on “New Rule.”

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

  • Step 3: Then, click on the “Format” key and apply the format as per choice.

  • Step 4: Then click “OK.” Now, we can see that formatting is ready.

#3 – Highlight All the Empty Cells in the Range

Assume we have the below-given data.

We must highlight all the blank or empty cells in the above data because we need to use the ISBLANK formulaISBLANK FormulaISBLANK in Excel is a logical function that checks if a target cell is blank or not. It returns the output “true” if the cell is empty (blank) or “false” if the cell is not empty. It is also known as referencing worksheet function and is grouped under the information function of Excel read more in the Excel conditional formatting.

Apply the below formula in the formulas section.

Now, first, we must select the formatting as per our 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: We must select the entire data first.

  • Step 2: Next, we need to use the AND excel functionUse The AND Excel FunctionThe AND function in Excel is classified as a logical function; it returns TRUE if the specified conditions are met, otherwise it returns FALSE.read more to test two conditions here. Both the conditions should be “TRUE.” Then, we must apply the below formula.

  • Step 3: Then, click on “Format.”

  • Step 4: We must go to “FILL” and select the required color and effects.

  • Step 5: Now, click on “OK.” As a result, we can see the rows highlighted if both the conditions are “TRUE.”

#5- Use OR Function to Highlight Cells

For example, consider the below data.

We want to highlight the city names “Bangalore” and “Mysore” in the above data range.

  • Step 1: We must select the data first, go to “Conditional Formatting,” and click on “New Rule.”

  • Step 2: Since we need to highlight either of the two value cells, we must apply the OR excel functionApply The OR Excel FunctionThe OR function in Excel is used to test various conditions, allowing you to compare two values or statements in Excel. If at least one of the arguments or conditions evaluates to TRUE, it will return TRUE. Similarly, if all of the arguments or conditions are FALSE, it will return FASLE.read more.

  • Step 3: Then, click on the “Format” and select the required format.

  • Step 4: Once the formatting is applied, click on “OK” to complete the task. As a result, a formula would highlight all the cells with values “Bangalore” and “Mysore.”

#6 – Use COUNTIF Function to Highlight Cells

Assume you are working with the customer database. First, you need to identify all the clients whose names appear more than five times on the list. Below is the data.

First, we must select the data and apply the COUNTIF formula in the formula section.

Then, click on the “Format” and apply the required formatting.

Consequently, the formula will highlight all the names if the count is more than 5.

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

#7 – Highlight Every Alternative Row

We can also have the formula to highlight every alternative row in excelHighlight Every Alternative Row In ExcelIn Excel, we can highlight every other row in one of three ways: using an Excel table, conditional formatting, or custom formatting.read more of the data. For example, assume below is the data we are working on.

We will select the data first and apply the below formula.

=MOD(ROW (), 2)

We will apply the format as per our wish and click on “OK.” As a result, we have every alternate 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. Again, consider the same data for the example.

And apply the below formula:

=MOD(COLUMN(), 2)

Now, copy the above formula and apply it in the formula section of the conditional formatting.

Click on “OK.” We can see now that it has highlighted the alternative row as shown below.

If we wish to leave out the first column and highlight the second column, we can use the below formula.

Therefore, this formula will 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.”A preview of conditional formatting is just an indication of how formatting looks.We must never use absolute reference as in the formula. When applying the formula, if we select the cell directly, it would make it an absolute reference. We need to make it to a relative reference in excelRelative Reference In ExcelIn Excel, relative references are a type of cell reference that changes when the same formula is copied to different cells or worksheets. Let’s say we have =B1+C1 in cell A1, and we copy this formula to cell B2 and it becomes C2+D2.read more.

This article has been a guide to Excel Conditional Formatting with Formulas. We discuss using conditional formatting using formulas like COUNTIF, AND, OR, MOD, etc., practical examples, and a 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 VBAVBA COUNTIF