How to Format Cell Based on Formula in Excel (13 Useful Examples)

Sometimes we need to format cells according to our requirements in our Excel datasheet. But formatting with the Format Cells feature takes some time which is quite inconvenient. In this article, we will show you the simple ways of Excel Format Cell based on Formula.

To illustrate, I’m going to use a dataset as an example. For instance, the following dataset represents the Devices of several Brands, Models, and Prices of those Devices.

How to Open a New Formatting Rule Window from Conditional Formatting Feature in Excel?

We can use different formulas to format cells in an Excel datasheet. But first, we have to know where we should type the formulas. We will pop up the New Formatting Rule window to format the cell based on a formula. To do that, we apply the conditional formatting feature. Let’s follow the instructions below to learn!

Select the range B5:E22.
Home > Styles > Conditional Formatting > New Rule.

A window named New Formatting Rule will pop up. Here, choose the Use a formula to determine which cells to format feature from select a Rule Type: option.
Then, in the field: Format values where this formula is true, type the formula to format cells as you want.

Notes:

The following procedure will be used to pop up the New Formatting Rule window in each of the examples below.

1. Using Excel Formula to Format Cell Depending on Another Cell

In our first method, we’ll just Compare the Sales. We will apply a formula in the following several cases to format cells. Therefore, follow the steps below to know where you should create the formula and then, format the cells.

(i) Equal To $490.00

From our dataset, we will format those cells whose value equals $490.00.

Select the range E5:E22. Hence, we will pop up the New Formatting Rule window like the above process. Then, in Format values where this formula is true box, type the following formula:

=$E5=490

After that, press Format.

The Format Cells dialog box will pop out. There, under the Fill tab, select a color >> press OK.

You’ll see the highlighted cells which are equal to E5.

(ii) Not Equal To $490.00

Type the following formula and press Format.

=$D5<>490

The Format Cells dialog box will pop out. There, under the Fill tab, select a color.
Press OK.

Lastly, you’ll see the desired changes.

(iii) Greater Than $1,000.00

Write the following formula >> press Format.

=$D5>1000

Next, select any color from the Fill tab >> Hit OK.

You’ll see the highlighted cells whose values are greater than $1,000.00.

(iv) Greater Than or Equal To $1,000.00

Write down the following formula

=$D5>=1000

Press Format.

Now the Format Cells dialog box will pop out. There, select the Fill tab >> select a color >> press OK.

You’ll be able to highlight cells whose values are greater than or equal to $1,000.00.

(v) Less Than $700.00

Insert the following formula and select Format.

=$D5<700

In the Format Cells dialog box, under the Fill tab, select a color. Press OK.

You’ll see the highlighted cells whose values are less than $700.00.

(vi) Less Than or Equal To $700.00

Now, we will use a logical formula to format cells whose values are less than or equal to $700.00.

Type the following formula, and choose Format.

=$D5<=700

Now the Format Cells dialog box will pop out, and select a color from the Fill tab.
Press OK.

You’ll see your desired highlighted cells.

(vii) Between $600.00 and $1,000.00

In this section, we will format those cells whose values are between $600.00 and $1,000.00. Let’s follow the instructions below to learn!

Write down the following formula >> Hit Format.

=AND($E5>600, $E5<1000)

Format Cells dialog box >> Fill tab >> select a color >> press OK.

It’ll return the formatted cells.

2. Applying Excel Formula to Format Rows with Text Criteria

We can apply a formula based on text criteria to format the entire row. In the below dataset, we’ll look for a product Notebook. And then, format the rows where the product is present. So, follow the below process to perform the task. For the partial match, we apply either the SEARCH function or the FIND function.

2.1 Using SEARCH Function: Case-Insensitive

From our dataset, we will apply the SEARCH function to format cells for partial matching with the case-insensitive issues. Let’s follow the steps to learn!

Select cell range C5 to C22. Hence, the conditional formatting feature pops up the New Formatting Rule window and inserts the following formula in Format values where this formula is true.

=SEARCH(“Notebook”,$C5)>0

Click on Format.

Now choose any color from the Fill tab and select OK.

Finally, the SEARCH function returns the formatted cells with the case-insensitive issue.

2.2 Applying FIND Function: Case Sensitive

Now, we will learn how to format cells for partial matching with the case-sensitive issues by applying the FIND function. Let’s follow the instructions to learn!

Select cell range C5 to C22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> Insert the formula in Format values where this formula is true box.

=FIND(“Notebook”,$C5)>0

Select Format.

The Format Cells dialog box will pop out >> go to the Fill tab >> select a color >> press OK.

You’ll see the highlighted cells containing only “Notebook” values, not “notebook”.

3. Formatting Rows with Number Criteria Based on Formula

In this example, we’ll format the entire row based on number criteria. We’ll format the rows where the price of the Desktop or Notebook exceeds $800.00. Hence, learn the process of doing the operation.

Select cell range B5 to E22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> Insert the formula in Format values where this formula is true box.

=$E5>800

Now, press Format.

Next, select any color to fill the rows >> press OK.

Lastly, it’ll return the desired rows in the specified color.

4. Formatting Odd Number Cells in Excel Based on Formula

Sometimes, we need to find the odd numbers in a range and format them. We can easily do that using the ISODD function. Let’s follow the below steps to learn the example.

Select E5:E22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> Insert a formula in Format values where this formula is true box >> Select Format.

=ISODD(E5)

Format Cells dialog box >> Select a color under the Fill tab >> press OK.

You’ll see the odd numbers in the selected color.

5. Using Excel Formula to Format Even Number Cells

In this example, we will learn how to format even number cells by applying the ISEVEN function. Let’s follow the instructions below to learn the example!

Select range E5 to E22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> Insert a formula in Format values where this formula is true box >> Select Format.

=ISEVEN(E5)

The Format Cells dialog box will pop out. There, select a color under the Fill tab >> press OK.

Finally, you’ll see the even numbers in the selected color.

6. Applying Excel Formula with AND Function to Format Cells

We can use the AND function when we need to format cells based on multiple criteria. In the following dataset, we’ll highlight the rows which contain the product Desktop and have Price below $800. So, follow and learn the steps.

Select the range B5 to E22 in your dataset. After that, from the Home tab under the conditional formatting feature open the New Formatting Rule window, and write down the following formula in Format values where this formula is true box.

=AND($C5="Desktop", $E5<800)

Select Format.

Format Cells dialog box >> Fill tab >> select a color >> press OK.

It’ll return the formatted rows.

7. Formatting Cells with OR Function in Excel

In our previous method, both of the conditions needed to be satisfied. But, in this example, we’ll format the rows for any of the conditions being true. For this reason, we’ll use the OR function. Now, learn the steps below to do the operation.

Select range B5:E22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> Insert a formula in Format values where this formula is true box.

=OR($C5="Desktop", $E5<800)

Now, press Format.

Format Cells dialog box will pop out >> Fill tab >> select a color >> OK.

Finally, the OR function will return the formatted rows.

8. Applying the ISBLANK Function to Format Blank Cells

A lot of times we have blank cells in our dataset. Highlighting the blank cells with a single formula helps us to edit them and thus saves us time. We’ll use the ISBLANK function to find the empty cells and subsequently format them. So, follow along with the procedure to Format Cell based on the Formula in Excel.

Select the range B5:D10. Now, from the Home tab under the conditional formatting feature open the New Formatting Rule window, and write down the following formula in Format values where this formula is true box.

=ISBLANK(B5)

After that, press Format.

The Format Cells dialog box will pop out. There, under the Fill tab, select a color.
And then, press OK.

Finally, the ISBLANK function will highlight the blank cells.

9. Formatting Non-Blank Cells Based on Excel Formula

Additionally, we can also highlight the Non–blank cells. For that purpose, we’ll just use the NOT function before the ISBLANK function. The NOT function simply converts TRUE to FALSE and FALSE to TRUE. Therefore, learn the steps below to learn how to format the non-blank cells.

Select the range B5:E22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> Insert the formula in Format values where this formula is true box >> Select Format.

=NOT(ISBLANK(B5))

Next, select any color to fill the cells >> press OK.

Lastly, you can format the non-blank cells.

10. Formatting Duplicate Cells Based on Formula

In this method, we’ll apply the COUNTIF function to find the duplicate cell values. Subsequently, we’ll format them. Now, learn the steps below to perform the task.

10.1 Formatting Duplicate Cells Including First Occurrences

We will apply the COUNTIF function to format the duplicate cells including the first occurrences. Let’s follow the instructions below to learn!

Select cells B5 to E22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> Write down the formula in Format values where this formula is true box >> Select Format.

=COUNTIF($B$5:$B$22,$B5)>1

Format Cells dialog box >> Fill tab >> select a color >> press OK.

At last, it’ll return the rows including the first occurrences.

10.2 Formatting Duplicate Cells Without First Occurrences

Now we will format the duplicate cells (Desktop) without the first occurrences by applying the COUNTIF function. Let’s follow the steps below to learn!

Select cells B5 to E22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> Type the formula in Format values where this formula is true box.

=COUNTIF($B$5:$B5,$B5)>1

Now press Format.

The Format Cells dialog box will pop out. There, under the Fill tab, select a color. Press OK.

It’ll return the rows without the first occurrences.

10.3 Formatting Consecutive Duplicate Cells in Excel

To format only consecutive cells or rows without the last that are duplicates, you can use the following example. You can use this example for the number, text, and date values. Let’s follow the instructions below to learn!

Select cells B5 to E22 >> Home From the conditional formatting feature pop up the New Formatting Rule window, and insert the following formula in Format values where this formula is true box.

=$B5=$B6

Press Format.

Now, select any color to fill the cells >> press OK.

At last, it’ll format only the consecutive duplicate cells.

11. Using Excel AVERAGE Function to Format Cells

We can use the AVERAGE function to compare the price of each product with the average of the total. In this section, we’ll highlight the rows with product prices greater than or less than the average. Hence, follow the procedure to Format Cells based on Formula in Excel.

11.1 Formatting Cells Greater Than Average Value

We will format the rows in which the price of the products is greater than the average.

Select the range of cells B5:E22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> Insert the formula in Format values where this formula is true box >> Select Format.

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

Next, select any color to fill the cells >> Press OK.

Lastly, you can format the rows in which the price of the products is greater than the average.

11.2 Formatting Cells Less Than Average Value

Now, we will format the rows in which the price of the products is less than the average using the AVERAGE function. Let’s follow the instructions below to learn!

Select the range of cells B5:E22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> Insert the formula in Format values where this formula is true box >> Select Format.

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

Format Cells dialog box >> Fill tab >> Select a color >> Press OK.

Finally, you’ll get the desired output.

12. Formatting Cells with Top 3 Values Based on Formula

The LARGE function returns the highest values. Here, we’ll use this function to format the rows with 3 top prices of the products.

Select the range B5:E22. Now, from the Home tab under the conditional formatting feature open the New Formatting Rule window, and type the following formula in Format values where this formula is true box.

=$E5>=LARGE($E$5:$E$22,3)

After that, press Format.

Select any color to fill the cells >> press OK.

Lastly, it’ll return the expected output.

13. Formatting Entire Row with Excel Formula When Any Cell Is Blank

In our last example, we’ll show how to format an entire row when there is a blank cell. We’ll use the COUNTBLANK function to do the operation.

First, select the range B5:E22 >> Home tab >> conditional formatting feature >> New Formatting Rule window >> insert formula in Format values where this formula is true box >> Select Format.

=COUNTBLANK($B5:$E5)

Select any color to fill the cells >> press OK.

It’ll return the dataset highlighting the rows which have blank cells.

Things to Remember

The formula you use must evaluate to either “TRUE” or “FALSE” for each cell in the range you want to format.
The formula can reference other cells or ranges in the workbook, but make sure you use the correct cell references.
When you create a new formatting rule, select the correct range of cells you want to format.
You can create multiple formatting rules for the same range of cells, but keep in mind that the rules will be applied in the order they are listed in the “Conditional Formatting Rules Manager” dialog box.
If you need to edit or delete a formatting rule, go to the “Conditional Formatting Rules Manager” dialog box, which can be accessed by clicking “Conditional Formatting” and then “Manage Rules” in the “Styles” group.
If you want to copy formatting from one range of cells to another, use the “Format Painter” tool in the “Clipboard” group of the “Home” tab.
Remember that conditional formatting is only applied to the cells in the current view of the worksheet. If you filter or sort the data, the formatting may change.

Frequently Asked Questions

1. Can I use multiple formulas for conditional formatting in the same range of cells?

Yes, you can use multiple formulas for conditional formatting. The rules will be applied in the order they are listed in the “Conditional Formatting Rules Manager” dialog box.

2. Can I use conditional formatting to apply different font styles or sizes?

Yes, you can use conditional formatting to apply different font styles or sizes. You can do this by selecting “Font” in the “Format Cells” dialog box instead of “Fill” or “Border“.

3. How do I remove conditional formatting from a range of cells?

To remove conditional formatting from a range of cells, select the cells, click on “Conditional Formatting” in the “Styles” group, and then select “Clear Rules” from the drop-down menu.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Excel Format Cell Based on Formula.xlsx

Conclusion

We have shown Excel format cells based on formulas with the above-described examples. From the above examples, you can format cells of number, text, and date values. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Conditional Formatting Formula | Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!