How to Fill Color in Cell Using Formula in Excel (5 Easy Ways)

In an Excel worksheet, you may need to fill color in different cells depending on the formula used in the sheets based on certain conditions. I’m going to explain 5 easy ways how to fill color in cell using formula in Excel.

Filling Color in Cell Using Formula in Excel


Download Practice Workbook

Get this sample file and practice the methods by yourself.


5 Easy Ways to Fill Color in Cell Using Formula in Excel

Here, I’m using a sample datasheet to show you the steps of the explained ways. It’s a Pay Sheet of several employees. There are 4 columns that represent the weekly, monthly, and yearly pay of an employee. These columns’ names are Name, Weekly Pay, Monthly Pay, and Yearly Pay.

Dataset to Fill Color in Cell Using Formula


Now, let us follow the methods below to fill color in cells using several formulas.

1. Use AVERAGE Function to Fill Cell Color in Excel

If you have numeric values, you can use the AVERAGE function to fill color in a cell using a formula. To use this function, follow the steps below.

  • First, select the cell or the cell range where you want to apply this function to fill color.
  • Then, open the Home tab > Go to Conditional Formatting > Finally select New Rule.

Choosing New Rule from Conditional Formatting to Use AVERAGE Function

  • Now, a dialog box will pop up.
  • Here, you need to select Use a formula to determine which cells to format from Select a Rule Type.
  • Then, in Edit the Rule Description type the following formula.
    =$C5:$C15<=AVERAGE($C$5:$C$15)

Using AVERAGE Function to Fill Cell Color in Excel

  • Now, from Format select the color of your choice to use in a cell.

Formatting Fill Color Using AVERAGE Function in Excel

Here, the AVERAGE function will calculate the average of the selected cell range C5:C15 and then will compare the average value with values of cell range C5:C15. Next, it will fill color where the cell value is less than average.

  • Finally, click OK.
  • Thus, it will show the cell filled with a selected format where the selected cell range is less than average.

How to Fill Color in Excel Cell Using Formula

Read More: Excel Formula Based on Cell Color (5 Examples)


2. Apply ISFORMULA Function Using Formula in Cell to Fill Color

You can use the ISFORMULA function to fill color in a cell if you have a formula in your cells.

  • First, select the cell range where you want to fill color using the formula.
  • Second, open the Home tab > go to Conditional Formatting > select the New Rule.

Choosing New Rule from Conditional Formatting to Use ISFORMULA Function

  • Third, a dialog box will pop up.
  • Here, choose Use a formula to determine which cells to format as Select a Rule Type.
  • After that, in the Edit the Rule Description type the following formula.
    =ISFORMULA($D$5:$E$15)
  • Along with this, from the Format choose the color of your choice to fill color in a cell.

Applying ISFORMULA Function Using Formula in Cell to Fill Color in Excel

I selected the cell range D5:E15 as the reference of the ISFORMULA function to fill color.
  • In the end, click OK.
  • As a result, it will show the cell filled with the selected format where the formula is used.

Result of Applying ISFORMULA Function

This method will not work on those cells where a formula is not applied.

Read More: How to Highlight Cell Using the If Statement in Excel (7 Ways)


3. Fill Color Using Formula in Excel Cell with OR Function

You can use the OR function to fill color in a cell using a formula. To get the output, go through the steps below.

  • First, select the cell range where you want to apply this function to fill color.
  • Then, open the Home tab > go to Conditional Formatting > select New Rule.

Choosing New Rule from Conditional Formatting to Use OR Function

  • After that, a dialog box will pop up.
  • Now, from Select a Rule Type: select Use a formula to determine which cells to format rules.
  • There, in Edit the Rule Description type the following formula.
    =OR($C5<30, $D5=180)
  • Afterward, select the Format of your choice to fill color in a cell.

Fill Color Using Formula in Excel Cell with OR Function

To use the OR function, I have taken two conditions. The 1st one is C5<30 and the 2nd one is D5=180. In the selected cell range B5:E15 if any of the conditions are fulfilled in any cell then it will fill color of that cell.
  • Finally, click OK.
  • Hence, it will show the cell filled with the selected fill color where one of the conditions is fulfilled.

Output of Using OR Function

Read More: How to Fill Cell with Color Based on Percentage in Excel (6 Methods)


Similar Readings:


4. Insert AND Function to Fill Cell Color in Excel

You also can use the AND function to fill color in a cell using a formula.

  • First, select the cell range to apply the AND function to fill color.
  • Next, open the Home tab > go to Conditional Formatting > select New Rule.

Choosing New Rule from Conditional Formatting to Use AND Function in Excel

  • Now, a dialog box will pop up.
  • From Select a Rule Type window, select Use a formula to determine which cells to format rules.
  • Then, in Edit the Rule Description type the following formula.
    =AND($C5<50, $D5=180)
  • Also, you can select the Format of your choice to fill color in a cell.

Inserting AND Function to Fill Cell Color in Excel

Here, I also used two conditions within the AND function. The 1st one is C5<50 and the 2nd one is D5=180. In the selected cell range B5:E15 if both of the conditions are fulfilled in any cell that cell will be filled with color otherwise not.
  • Finally, click OK.
  • Therefore, it will show the cell filled with the selected format where two of the conditions are fulfilled.

Final Output of Inserting AND Function

Related Content: How to Change Cell Color Based on a Value in Excel (5 Ways)


5. Use Excel Operators in Cell to Fill Color

You also can use the various Operators in Excel to fill color in any cell using a formula.

5.1. Greater Than (>)

Here, I am going to use the Greater Than operator to fill color in the cell using the formula.

  • First, select the cell or cell range to apply the formula.
  • Now, open the Home tab > go to Conditional Formatting > select New Rule.

Choosing New Rule from Conditional Formatting to Use Greater Than Operator

  • Then, a dialog box will pop up. From Select a Rule Type you can select Use a formula to determine which cells to format rules.
  • Now, in Edit the Rule Description type the following formula.
    =D5 *12 > 1800
  • Next, select the Format of your choice to fill color in a cell.

Formula with Greater Than Operator in New Formatting Rule Window

Here, I used the (>) Greater Than operator to check where D5*12 is greater than 1800. Now, in the selected cell range B5:B15 it will fill the rows where the conditions are fulfilled.
  • In the end, click OK.
  • Then, it will show the cell filled with the selected format where D5*12 is greater than 1800.

Final Result of Greater Than Operator


5.2. Not Equal (<>)

To fill color in the cell using the formula here I am going to use the Not Equal operator.

  • First, select the cell or cell range to apply the formula.
  • Then, open the Home tab > go to Conditional Formatting > select New Rule.

Choosing New Rule from Conditional Formatting to Use Greater Than Operator

  • Then, a dialog box will pop up.
  • Now, from Select a Rule Type you need to select the Use a formula to determine which cells to format.
  • Next, in Edit the Rule Description type the following formula.
    =C5*4 <> 180
  • From the Format option, you can select the format of your choice to fill color in a cell.

Formula with Not Equal Operator in New Formatting Rule Window

Here, I used the formula C5*4 and applied the condition where it is Not Equal (<>) 180. Now, the operator will check where C5*4 are not equal to 180 in the selected cell range B5:B15. Then, it will fill the rows where the conditions are fulfilled.
  • Finally, click OK.
  • Here, it will show the cell filled with the selected format color where C5*4 is not equal to 180.

Final Result of Not Equal Operator


5.3. Equal (=)

By using the Equal operator, I will fill color in the cell.

  • First, select the cell or cell range to apply the formula.
  • Then, open the Home tab > go to Conditional Formatting >select New Rule.

Choosing New Rule from Conditional Formatting to Use Equal Operator

  • Now, a dialog box will pop up.
  • In the dialog box from Select a Rule Type, you can select Use a formula to determine which cells to format rules.
  • Then, in Edit the Rule Description type the following formula.
    =C5*4 = 180
  • Next, from the Format options select the format of your choice to fill color in a cell.

Formula with Equal Operator in New Formatting Rule Window

On the spot, I used the (=) Equal operator to check where C5*4=180. Now, in the selected cell range C5:C14 it will fill the rows where the conditions are fulfilled.
  • Finally, click OK.
  • On the whole, it will show the cell filled with the selected format color where C5*4 is equal to 180.

Final Result of Equal Operator


5.4. Less Than (<)

You also can use the Less Than operator to fill color in Excel cells using formula.

  • First and foremost, select the cell or cell range to apply the Less than operator.
  • After that, open the Home tab > go to Conditional Formatting > select New Rule.

Choosing New Rule from Conditional Formatting to Use Less Than Operator

  • Then, a dialog box will pop up.
  • In the dialog box, from the Select a Rule Type you need to select Use a formula to determine which cells to format rules.
  • Next, in Edit the Rule Description type the following formula.
    =$D5:$D15<180
  • Along with this, select the fill color of your choice from the Format options.

Formula with Less Than Operator in New Formatting Rule Window

Now, I used the (<) Less Than operator to check which values of range D5:D15 are less than 180 of the Monthly Pay columns. Now, the color will be applied to the cells where conditions are fulfilled.
  • Finally, click OK.
  • Hence, I applied this formula in the range B5:E15 so it will fill color in those rows where the value of Monthly Pay is less than 180.

Final Result of Less Than Operator

Related Content: How to Highlight Selected Cells in Excel (5 Easy Ways)


Things to Remember

  • It is better to avoid using keyboard buttons to prevent cell references into any other value.
  • You must carefully select the cells where you want to apply Fill Color.

Conclusion

In this article, you will find 5 easy ways how to fill color in Excel cell using formula. These methods will be useful for you whenever you want to fill color in a cell using a formula. In case you have any confusion or question regarding these methods you may comment down below. Follow ExcelDemy for more tutorials.


Related Articles

Shamima Sultana

Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

2 Comments
  1. I have Purchased spread sheet templates and cant find where or if they have been downloaded to access

    Best wishes,

    Barry

    • Hello, Barry! You can check your default path folder for browser downloads. And if you need any assistance with the Excel problems you can mail us!

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo