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

In a worksheet, you may need to fill color in different cells depending on the formula used in the sheets. I’m going to explain different ways of how to fill color in Excel using formula.

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.

Sample Dataset

 

Download to Practice

5 Ways to Fill Color in Excel Cell Using Formula

1. Using AVERAGE 

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

To use this function, 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

Using Average

Now, a dialog box will pop up. From there 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.

=$C4:$C14<=AVERAGE($C$4:$C$14)

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

Using Average

Here, the AVERAGE function will calculate the average of the selected cell range C4:C14 then will compare the average value with values of cell range C4:C14. 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.

Using Average

2. Using ISFORMULA 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 formula.
Second, open the Home tab >> go to Conditional Formatting >> then select the New Rule

Using ISFORMULA

Third, a dialog box will pop up. From the Select a Rule Type select Use a formula to determine which cells to format.

After that, in the Edit the Rule Description type the following formula.

=ISFORMULA($D$4:$E$14)

Now, from the Format choose the color of your choice to fill color in a cell.

Using ISFORMULA to Fill Color

I selected the cell range D4:E14 as the reference of the ISFORMULA function to fill color.

It won’t work on those cells where a formula is not applied.

In the end, click OK.
As a result, it will show the cell filled with the selected format where the formula is used.

Using ISFORMULA to Fill Color

3. Fill Color Using OR

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

To begin with, first, select the cell range where you want to apply this function to fill color.
After that, open the Home tab >> go to Conditional Formatting >> now select New Rule

Fill Color Using OR

 

After selecting New Rule, 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($C4<30, $D4=180)

Then select the Format of your choice to fill color in a cell.

Fill Color Using OR

To use the OR function, I have taken two conditions. The 1st one is C4<30 and the 2nd one is D4=180. In the selected cell range B4:E14 if any of the conditions are fulfilled in any cell then it will fill color to that cell.

Finally, click OK.
Hence, it will show the cell filled with the selected fill color where one of the conditions is fulfilled.

Fill Color Using OR

4. Fill Color Using AND

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 >> finally select New Rule

Fill Color Using AND

Now, a dialog box will pop up. From Select a Rule Type you need to select Use a formula to determine which cells to format rules.

Then, in Edit the Rule Description type the following formula.

=AND($C4<50, $D4=180)

Now you can select the Format of your choice to fill color in a cell.

Fill Color Using AND

Here, I also used two conditions within the AND function. The 1st one is C4<50 and the 2nd one is D4=180. In the selected cell range B4:E14 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.

Fill Color Using AND

5. Using Different Operators

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

I. Greater Than (>)

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

To begin with first select the cell or cell range to apply the formula.
Now, open the Home tab >> Go to Conditional Formatting >>Then select New Rule

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.

=D4 *12 > 1800

Next, select the Format of your choice to fill color in a cell.

Greater Than (>) operator

Here, I used the (>) greater than operator to check where D4*12 is greater than 1800. Now, in the selected cell range B4:B14 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 D4*12 is greater than 1800.

Greater Than (>) operator

II. Not Equal (<>)

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

Now 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 >> now select New Rule

Not Equal (<>)

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.

=C4*4 <> 180

From the Format option, you can select the format of your choice to fill color in a cell.

Using Not Equal (<>) operator to Fill color using Formula

Here, I used the formula C4*4 and applied the condition where it is not equal (<>) 180. Now, the operator will check where C4*4 are not equal to 180 in the selected cell range B4:B14. 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 C4*4 are not equal to 180.

Using Not Equal (<>) operator to Fill color using Formula

III. Equal (=)

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

You can also use the Equal (=) operator for that, first select the cell or cell range to apply the formula.
Then, open the Home tab >> go to Conditional Formatting >> then select New Rule

Equal (=)

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.

=C4*4 = 180

Next, from the Format options select the format of your choice to fill color in a cell.

Using Equal (=) operator to Fill color using Formula

On the spot, I used the (=) equal operator to check where C4*4=180. Now, in the selected cell range C4: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 C4*4 are equal to 180.

Using Equal (=) operator

IV. 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 >>now select New Rule

Using Less Than (<) operator to Fill color using Formula

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.

=$D4:$D14<180

Along with, select the fill color of your choice from the Format options.

Using Less Than (<) operator

Now, I used the (<) less than operator to check which values of range D4:D14 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, as I applied this formula in the range B4:E14 so it will fill color in those rows where the value of Monthly Pay is less than 180.

Using Less Than (<) operator to Fill color using Formula

Practice Section

I’ve provided an extra practice sheet in the worksheet so that you can practice these explained methods to fill color in the cell using formula.

Practice Sheet to Fill color using Formula

Conclusion

In this article, you will find 5 ways 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 formula. In case you have any confusion or question regarding these methods you may comment down below.

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo