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 to fill color in the cell using formulas 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.

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

## 1. Using Excel AVERAGE Function with Conditional Formatting to Fill Cell Color

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 the color.
- Then, open the
**Home**tab > Go to**Conditional Formatting**> Finally select**New Rule**.

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

- Now, from
**Fill**section, select the color of your choice to use in a cell.

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.

**Read More: **Excel Formula Based on Cell Color

## 2. Applying ISFORMULA Function with Conditional Formatting to Fill Cell 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 the color using the formula.
- Second, open the
**Home**tab > go to**Conditional Formatting**> select the**New Rule**.

- 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**tab choose the color of your choice to fill color in a cell.

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

*Note:*This method will not work on those cells where a formula is not applied.## 3. Fill Cell Color in Excel Using the OR Function with Conditional Formatting

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 the color.
- Then, open the
**Home**tab > go to**Conditional Formatting**> select**New Rule**.

- 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 the color in a cell.

**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 the 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.

**Read More:** How to Fill Cell with Color Based on Percentage in Excel

## 4. Using AND Function with Conditional Formatting 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 the color. - Next, open the
**Home**tab > go to**Conditional Formatting**> select**New Rule**.

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

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

**Related Content:** How to Change Cell Color Based on a Value in Excel

## 5. Highlighting Cells Based on Conditions (Greater than, Less Than, Equal to or Not Equal to)

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 the 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**.

- 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 the color in a cell.

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

### 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**.

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

**C5*4**and applied the condition where it is Not Equal (<>) 180. Now, the operator will check where

**C5*4**is 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.

### 5.3. Equal (=)

By using the Equal operator, I will fill the 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**.

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

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

### 5.4. Less Than (<)

You also can use the Less Than operator to fill color in Excel cells using a 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**.

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

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

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

**Download Practice Workbook**

Get this sample file and practice the methods by yourself.

## Conclusion

In this article, we discussed several methods fill color in Excel cells using formulas. 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.

**Related Articles**

- Uses of CELL Color A1 in Excel
- How to Color Code Cells in Excel
- Excel Formula to Change Cell Color Based on Text
- VBA to Change Cell Color Based on Value in Excel
- Excel Formula to Color Cell If It Has Specific Value
- How to Change Text Color with Formula in Excel

**<< Go Back to Color Cell in Excel | Excel Cell Format | Learn Excel**

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!