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.

**Read More:** How to Do Conditional Formatting Based on Another Cell in Excel

## 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.

**Read More: **How to Apply Conditional Formatting to Each Row Individually

### 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*”.

**Read More:** How to Change Row Color Based on Text Value in Cell in Excel

## 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**–b**lank** 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.

**Read More:** Conditional Formatting If Cell is Not Blank

## 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.

## 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.

**Related Articles**

- How to Use Conditional Formatting Based on VLOOKUP in Excel
- How to Apply Conditional Formatting with INDEX-MATCH in Excel
- Excel Conditional Formatting Formula with IF
- Excel Conditional Formatting Formula If Cell Contains Text
- Applying Conditional Formatting for Multiple Conditions in Excel
- How to Change Text Color Based on Value with Excel Formula
- Conditional Formatting Multiple Text Values in Excel
- Conditional Formatting Entire Column Based on Another Column in Excel
- Excel Highlight Cell If Value Greater Than Another Cell