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

Get FREE Advanced Excel Exercises with Solutions!

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.

1.01-Excel format cell based on formula


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.

1-Use Conditional formatting feature

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

2-Use a formula to determine which cells to format feature

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.

3-Apply the formula in the edit the rule description option

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

4-Selection of color from Fill tab

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

5-Highlighting cells which are equal to $490.00

(ii) Not Equal To $490.00

  • Type the following formula and press Format.
=$D5<>490

6-Use formula in the New Formatting Rule window

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

7-Selecting any color from the Fill tab

  • Lastly, you’ll see the desired changes.

8-Formatting the cells which are not equal to $490.00

(iii) Greater Than $1,000.00

  • Write the following formula >> press Format.
=$D5>1000

9-Applying formula to format cells

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

10-Choosing any color from the Fill tab

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

11-Formatting the cells which are greater than $1,000.00

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

  • Write down the following formula
=$D5>=1000
  • Press Format.

12-Using formula to format cells in Excel

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

13-Selecting any color from the Fill tab

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

14-Highlighting 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

15-Apply formula for formatting cells

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

16-Select any color under the Fill tab

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

17-Formatting 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

18-Use formula in the New Formatting Rule window

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

19-Use the Fill tab to Select any color

  • You’ll see your desired highlighted cells.

20-Highlighting cells whose values are less than or equal to $700.00

(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)

21-Use AND function to format cells

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

22-Selecting any color from the Fill tab

  • It’ll return the formatted cells.

23-Formatting those cells whose values are between $600.00 and $1,000.00

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.

24-Use SEARCH function to format cells

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

25-Selecting any color from the Fill tab

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

26-Formatted cells using the SEARCH function

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.

27-Apply the FIND function to highlight cells

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

28-From the Fill tab Selects any color

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

29-Output of the FIND function

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.

30-Apply formula to format rows

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

31-Select any color from the Fill tab

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

32-Output of the formatting rows using formula


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)

33-Use ISODD function in the New Formatting Rule window

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

34-Choose any color from the Fill tab

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

35-Output of the ISODD function


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)

36-Apply ISEVEN function to format even number cells

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

37-Selecting any color from the Fill tab

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

38-Format cells using the ISEVEN function


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.

39-Apply AND function in the New Formatting Rule window

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

40-From the Fill tab select any color

  • It’ll return the formatted rows.

41-Returns of the AND function


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.

42-Type OR function in the Edit the rule description box

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

43-Choosing any color from the Fill tab

  • Finally, the OR function will return the formatted rows.

44-Formatting cells using the OR function


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.

45-Insert ISBLANK function in the Edit the rule description box

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

46-Select any color to format cells

  • Finally, the ISBLANK function will highlight the blank cells.

47-Output of the ISBLANK function


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))

48-Write down the NOT and ISBLANK functions in the Edit the rule description box

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

49-Selecting any color from the Fill tab

  • Lastly, you can format the non-blank cells.

50-Formatting 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

51-Apply COUNTIF function in the Edit the rule description box

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

52-Choosing any color from the Fill tab

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

53-Formatting the duplicate cells 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.

54-Apply COUNTIF function to format cells

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

55-Selecting any color from the Fill tab

  • It’ll return the rows without the first occurrences.

56-Format the duplicate cells 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.

57-Apply the formula in the New Formatting Rule window

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

58-From the Fill tab selects any color

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

59-Formatting only consecutive cells or rows without the last


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)

60-Type AVERAGE function in the Edit the rule description box

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

61-Selecting any color from the Fill tab

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

62-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)

63-Write down the AVERAGE function to format cells

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

64-Choosing any color from the Fill tab

  • Finally, you’ll get the desired output.

65-Format the rows in which the price of the products is less than the average


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.

66-Apply of large function to format cells

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

67-Selecting any color from the Fill tab

  • Lastly, it’ll return the expected output.

68-Use the LARGE function to format the rows with 3 top prices of the products


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)

69-Use the COUNTBLANK function in the Edit the rule description box

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

70-Selecting any color from the Fill tab

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

71-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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Aung Shine
Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo