How to Format Cell Based on Formula in Excel (13 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 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.

Format Cell Based on Formula in Excel


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.

Format Cell Based on Another Cell with Formula in Excel

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

Format Cell Based on Another Cell with Formula in Excel

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

Format Cell Based on Another Cell with Formula in Excel

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

Apply Formula to Format Rows Based on a Text Criteria

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.

Apply Formula to Format Rows Based on a Text Criteria

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

Formatting Rows with Formula Based on a Number of Criteria

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.

Formatting Rows with Formula Based on a Number of Criteria

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

Format Odd Number Cells in Excel Based on Formula

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.

Format Odd Number Cells in Excel Based on Formula

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

Use Excel AND Function to Format Cells

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.

Format Cells with OR Function in Excel

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

Apply Formula to Format Blank Cells

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.

Apply Formula to Format Blank Cells

  • 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


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

Additionally, we can also highlight the NonBlank 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.

Format Non-Blank Cells Based on Formula

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.

Format Non-Blank Cells Based on Formula

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

Excel SEARCH Function to Format Cells

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.

Excel SEARCH Function to Format Cells

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

Format Duplicate Cells Based on Formula in Excel

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.

Format Duplicate Cells Based on Formula in Excel

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

Format Cells with Excel AVERAGE Function

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.

Format Cells with Excel AVERAGE Function

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

Format Cells with Top 3 Values Based on Formula

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.

Format Cells with Top 3 Values Based on Formula

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

Format Entire Row with Formula When Any Cell is Blank

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

Format Entire Row with Formula When Any Cell is Blank

  • 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

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo