Excel Productivity Guide

Useful Excel Tips | Keyboard Shortcuts | VBA Codes

**Download this Free E-Book **to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

The best part of conditional formatting is you can use formulas in it.

It has a very simple sense to work with formulas. Your formula should be a logical formula and result should be in TRUE or FALSE.

If that formula returns TRUE, conditional formatting will apply and if it returns FALSE, conditional formatting will not apply.

The point is, by using formulas you can make best out of conditional formatting.

In the below example, we have used a formula in CF to check whether the value in the cell is smaller than 1000 or not.

And, if that value is smaller then it will apply the formatting which we have specified, otherwise not.

So today in this post, I’d like to share with you simple steps to apply conditional formatting using a formula and some of the useful examples which you can use in your daily work.

So let’s get started.

Quick Navigation

*Want to learn some awesome tips to increase your productivity? Then, you must download this free e-book.*

These steps are quite simple you just need to enter a formula and specify a format to apply if that formula returns TRUE in the result.

Below are the steps to open the CF dialog box and enter a formula into it.

**Important**: While entering a formula in CF dialog box you can’t see its result whether that formula is valid or not. So the best practice is to check that formula before using in CF by entering it in a cell.

Here we have some examples which can be useful for you to use in your daily work and will help you to get better in conditional formatting.

Yes, you can apply conditional formatting based on another cell.

If you look at the below example, we have added a simple formula which is based on another cell.

And, if the value of that linked cell will meet the condition specified, conditional formatting will apply.

When achievement will below 75%, it will highlight by the red color.

Whenever I think about conditions, the first thing comes to your mind is using IF function.

And the best part of this functions is, it fits perfectly in conditional formatting. Let me show you an example:

Here, we have used the IF to create a condition and the condition is when the count of “Done” in range B3:B9 is equal to the count of tasks in the range A3:A9, then the final status will appear.

You can create multiple checks in condition to apply formatting.

Once all the conditions or one of the conditions will meet, conditional formatting will apply to the cell.

Look at the below example where we have used the average temperature of my town. And, we have used a simply combined IF-AND to highlight the months where the temperature is pretty pleasant.

Months, where the temperature is within 15 Celsius to 35 Celsius, will get colored.

Just like this, you can also use if with or function.

To highlight every alternate row you can use following formula n CF.

=INT(MOD(ROW(),2))

By using this formula, every row whose number is odd will be highlighted. And, if you want to do vice versa you can use the following formula.

=INT(MOD(ROW()+1,2))

The same kind of formula you can use for columns (odd and even) as well.

And for even columns.

Now let’s come to another example where we will check whether a cell contains an error or not.

What we need to do is just insert a formula in conditional formatting that can check the condition and return the result in TRUE or FALSE.

You can even verify cells for numbers, text or some specific values as well.

And, let’s add some creativity in intelligence.

You have already learned how to use a formula which is based on some another cell.

Here we have linked a checkbox with B1 cell and further linked the B1 with the formula used in conditional formatting for cell A1.

Now, if you tick mark the checkbox, the value of cell B1 will turn into TRUE and cell A1 gets it conditional formatting [strikethrough].

- Your formula should be a logical formula, which leads to a result as TRUE or FALSE.
- Try not to overload your data with conditional formatting.
- Always use relative and absolute references in a proper sense.

Download this sample file from here to learn more

Conditional formatting is an awesome tool and by using a formula in it you can make best out of it.

I’m pretty sure that above-mentioned examples will inspire you to use it more frequently in your work.

Now, tell me one thing.

**Have you ever used CF in your work?**

Share your views with me in the comment section, I’d love to hear from you. And, please don’t forget to share this tip with your friends.

Pingback: In Cell Charting With REPT Function To Create A Column Chart()

Pingback: How To Use Conditional Formatting To Find New Customers From Data()

Pingback: How To Customize a Cell Comment In Excel In Different Ways()