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 to **Format Cell **based on **Formula **in **Excel**.

To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the **Salesman**, **Product**, and** Net Sales **of a company.

**Table of Contents**hide

## Download Practice Workbook

To practice by yourself, download the following workbook.

## 13 Examples to Format Cell Based on Formula in Excel

### 1. Format Cell Based on Another Cell with Formula 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. In our first method, we’ll just **Compare **the **Net Sales**. Therefore, follow the steps below to know where you should create the formula and then, format the cells.

**STEPS:**

- First, select the range
**D5:D10**.

- Next, under the
**Home**tab, select**New Rule**from the**Conditional Formatting**drop-down list.

- As a result, a dialog box will pop out. Here, select Use a formula to determine which cells to format in
**Rule Type**. - Then, in
**Format values where this formula is true**box, type the formula:

`=$D5>$D$5`

- After that, press
**Format**.

- Consequently, the
**Format Cells**dialog box will pop out. There, under the**Fill**tab, select a color. - Subsequently, press
**OK**.

- At last, you’ll see the highlighted cells which are greater than
**D5**.

**Read More:** **How to Use Excel Cell Format Formula(4 Effective Methods)**

### 2. Apply Formula to Format Rows Based on a Text Criteria

We can apply a formula based on text criteria to format the entire row. In the below dataset, we’ll look for product **AC**. And then, format the rows where the product is present. So, follow the below process to perform the task.

**STEPS:**

- First of all, select the range of cells.
- Next, go to
**Home**>**Conditional Formatting**>**New Rule**. - A window will pop out. Here, choose the
**Rule Type**:**Use a formula to determine which cells to format**. - Then, in the field:
**Format values where this formula is true**, type the formula:

`=$C5="AC"`

- After that, select
**Format**.

- Another dialog box will pop out. There, under the
**Fill**tab, select any color. - Subsequently, press
**OK**.

- Lastly, you’ll see the desired changes.

**Read More: ****How to Format Text Using Excel VBA (12 Methods)**

### 3. Formatting Rows with Formula Based on a Number of Criteria

In this method, we’ll format the entire row based on number criteria. We’ll format the rows where the net sales exceed** $10,000**. Hence, learn the process to do the operation.

**STEPS:**

- Firstly, select the range in your dataset.
- Then, go to
**Home**>**Conditional Formatting**>**New Rule**. - A window will pop out. Here, select the
**Rule Type**:**Use a formula to determine which cells to format**. - Subsequently, in the field:
**Format values where this formula is true**, type the formula:

`=$D5>10000`

- After that, press
**Format**.

- Next, select any color to fill the rows.
- Afterward, press
**OK**.

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

**Read More:** **How to Custom Format Cells in Excel(17 Examples)**

### 4. Format Odd Number Cells in Excel Based on Formula

Sometimes, we need to find the odd numbers in a range and format them. Using the **ISODD** function makes this process a lot easier. Therefore, follow the below steps to learn the method.

**STEPS:**

- In the beginning, select the range
**D5:D10**. - Now, go to
**Home**>**Conditional Formatting**>**New Rule**. - A dialog box will pop out. Here, choose the
**Rule Type**:**Use a formula to determine which cells to format**. - In the field:
**Format values where this formula is true**, type the formula:

`=ISODD(D5)`

- Press
**Format**.

- As a result, the
**Format Cells**dialog box will pop out. There, select a color under the**Fill**tab. - Subsequently, press
**OK**.

- In the end, you’ll see the odd numbers in the selected color.

**Read More:** **How to Use Format Painter in Excel**

### 5. Use Excel AND Function to Format Cells

We can use **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 **Cable **and have net sales below **$10,000**. So, follow and learn the steps.

**STEPS:**

- First, select the range
**B5:D10**. - Under the
**Home**tab, select**New Rule**from the**Conditional Formatting**drop-down list. - As a result, a dialog box will pop out. Here, select
**Use a formula to determine which cells to format**in**Rule Type**. - Then, in
**Format values where this formula is true**box, type the formula:

`=AND($C5="Cable", $D5<10000)`

- After that, press
**Format**.

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

- At last, it’ll return the formatted rows.

### 6. Format 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 **Excel ****OR** function. Now, learn the steps below to do the operation.

**STEPS:**

- Select the range of cells at first.
- After that, go to
**Home**>**Conditional Formatting**>**New Rule**. - A window will pop out. Here, choose the
**Rule Type**:**Use a formula to determine which cells to format**. - Next, in the field:
**Format values where this formula is true**, type the formula:

`=OR($C5="Cable", $D5<10000)`

- Then, select
**Format**.

- As a result, another dialog box will pop out and select any color from the
**Fill**tab. - Subsequently, press
**OK**.

- Lastly, it’ll return the expected outcome.

### 7. Apply Formula 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 our time. We’ll use the **ISBLANK** function in **Excel **to find the empty cell and subsequently format them. So, follow along with the procedure to **Format** **Cell** based on **Formula** in **Excel**.

**STEPS:**

- Firstly, select the range
**B5:D10**. - Then, under the
**Home**tab, select**New Rule**from the**Conditional Formatting**drop-down list. - As a result, a dialog box will pop out. Here, select
**Use a formula to determine which cells to format**in**Rule Type**. - Next, in
**Format values where this formula is true**box, type the formula:

`=ISBLANK(B5)`

- After that, press
**Format**.

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

- Finally, it’ll highlight the blank cells.

**Similar Readings**

**Use Format Painter Shortcut in Excel (5 Ways)****How to Change Time Format in Excel (4 Ways)****Copy Formatting in Excel (3 Processes)****How to Copy Cell Format in Excel (4 Methods)****Formula to Copy Cell Value and Format in Excel (5 Uses)**

### 8. Format Non-Blank Cells Based on Formula in Excel

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 know how to format the non-blank cells.

**STEPS:**

- First, select the range in your dataset.
- Go to
**Home**>**Conditional Formatting**>**New Rule**. - A window will pop out. Here, select the
**Rule Type**:**Use a formula to determine which cells to format**. - Subsequently, in the field:
**Format values where this formula is true**, type the formula:

`=NOT(ISBLANK(B5))`

- After that, press
**Format**.

- Next, select any color to fill the cells.
- Afterward, press
**OK**.

- Lastly, you’ll see the required changes.

### 9. Excel SEARCH Function to Format Cells

Moreover, we can use the **SEARCH** function to find a particular text and format them afterward. In this dataset, we’ll search for the product **Cable **and then, format the entire row.

**STEPS:**

- In the beginning, select the range
**B5:D10**. - Now, go to
**Home**>**Conditional Formatting**>**New Rule**. - A dialog box will pop out. Here, choose the
**Rule Type**:**Use a formula to determine which cells to format**. - Next, in the field:
**Format values where this formula is true**, type the formula:

`=SEARCH("Cable",$C5)>0`

- Then, press
**Format**.

- As a result, the
**Format Cells**dialog box will pop out. There, select a color under the**Fill**tab. - Subsequently, press
**OK**.

- In the end, you’ll see the highlighted rows which contain
**Cable**.

### 10. Format Duplicate Cells Based on Formula in Excel

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.

**STEPS:**

- First, select the range
**B5:D10**. - Now, under the
**Home**tab, select**New Rule**from the**Conditional Formatting**drop-down list. - As a result, a dialog box will pop out. Here, select
**Use a formula to determine which cells to format**in**Rule Type**. - Next, in
**Format values where this formula is true**box, type the formula:

`=COUNTIF($C$5:$C$10,$C5)>1`

- After that, press
**Format**.

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

- At last, it’ll return the rows with duplicate cells.

### 11. Format Cells with Excel AVERAGE Function

We can use the **AVERAGE** function in **Excel **to compare the **Net Sales **of each salesman to the average of the total. In this example, we’ll highlight the rows which have net sales greater than the average. Hence, follow the procedure to Format Cells based on Formula in Excel.

**STEPS:**

- Firstly, select the range of cells.
- Then, go to
**Home**>**Conditional Formatting**>**New Rule**. - A window will pop out. Here, choose the
**Rule Type**:**Use a formula to determine which cells to format**. - Next, in the field:
**Format values where this formula is true**, type the formula:

`=$D5>AVERAGE($D$5:$D$10)`

- Select
**Format**after that.

- As a result, another dialog box will pop out and select any color from the
**Fill**tab. - Subsequently, press
**OK**.

- Finally, you’ll get the desired output.

### 12. Format Cells with Top 3 Values Based on Formula

The **LARGE** function in **Excel **returns the highest values. Here, we’ll use this function to format the rows with** 3** top net sales amounts.

**STEPS:**

- In the beginning, select the range
**B5:D10**. - Now, go to
**Home**>**Conditional Formatting**>**New Rule**. - A dialog box will pop out. Here, choose the
**Rule Type**:**Use a formula to determine which cells to format**. - Next, in the field:
**Format values where this formula is true**, type the formula:

`=$D5>=LARGE($D$5:$D$10,3)`

- Then, press
**Format**.

- As a result, the
**Format Cells**dialog box will pop out. There, select a color under the**Fill**tab. - After that, press
**OK**.

- In the end, it’ll return the expected output.

### 13. Format Entire Row with 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.

**STEPS:**

- First, select the range
**B5:D10**. - Then, under the
**Home**tab, select**New Rule**from the**Conditional Formatting**drop-down list. - As a result, a dialog box will pop out. Here, select
**Use a formula to determine which cells to format**in**Rule Type**. - Next, in
**Format values where this formula is true**box, type the formula:

`=COUNTBLANK($B5:$D5)`

- Press
**Format**now.

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

- Eventually, it’ll return the dataset highlighting the rows which have blank cells.

## Conclusion

Henceforth, you will be able to **Format Cell **based on **Formula **in **Excel **with the above-described methods. 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

**Uses of CELL Color A1 in Excel (3 Examples)****VBA to Format Cell in Excel (12 Ways)****Dealing with Time Format in Excel (5 Suitable Ways)****How to Repeat Formula Pattern in Excel (Easiest 8 ways)****How to Use Format Painter in Excel for Multiple Cells (6 Quick Ways)****How to Copy Formatting in Excel to Another Sheet (4 Ways)**