The best part of conditional formatting is you can use formulas in it.
And, it has a very simple sense to work with formulas.
Your formula should be a logical formula and the result should be in TRUE or FALSE.
If formula returns TRUE, youβll get the formatting and if FALSE then nothing.
The point is, by using formulas you can make best out of conditional formatting.
Yes, thatβs right.
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 than 1000 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.
Steps to Apply CF with Formulas
The steps to apply CF with formulas are quite simple:
- Select the range to apply CF
- Add a formula to text a condition
- Specify a format to apply when the condition is met
To learn this in proper way make sure to download this sample file from here and follow the below detailed steps.
- First of all, select the range where you want to apply conditional formatting.
- After that, go to Home Tab β Styles β Conditional Formatting β New Rule β Use a formula to determine which cell to format.
- Now, in the βFormat values where formula is trueβ enter below formula.
=E5<1000
- The next thing is to specify format to apply and for this, click on the format button and select the format.
- In the end, click OK.
PRO Tip: 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.
Top 6 Example to use Formulas in Conditional Formatting
Here are some of the most useful examples which you can learn and use in the real world.
Check this out β Top 100+ Excel Tips and Tricks which will Make You a PRO in 2018
1. Use a Formula which is Based on Another Cell
Yes, you can apply conditional formatting based on another cell's value.
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 meets the condition specified, you'll get conditional formatting.
When achievement will be below 75%, it will highlight by the red color.
2. Conditional Formatting using IF
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.
2. Conditional Formatting by using Multiple Conditions
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.
4. Highlight Alternate Rows with Conditional Formatting
To highlight every alternate row you can use following formula n CF.
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.
The same kind of formula you can use for columns (odd and even) as well.
And for even columns.
5. Highlight Cells with Error using CF
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.
6. Create a Checklist with Conditional Formatting
Now 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].
Points to Remember
- 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.
Sample File
Download this sample file from here to learn more
Conclusion
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 formula in CF?
Please share your views with me in the comment section. I'd love to hear from you, and please, donβt forget to share this post with your friends, I am sure they will appreciate it.
You must Read these Next
- Highlight Top Bottom N Values: Whenever my boss called me with some sort of reports, I do it for sure to highlight top and bottom values...
- Apply Conditional Formatting to a Pivot Table: To make it more appealing to your users, you can apply conditional formatting...
- Highlight Blank Cells in Excel: Itβs recommended to highlight these cells with a color so that we can recognize them...
- VBA Code to Highlight Duplicate Values: Removing these duplicate values or just counting them never sort out...
- Format Painter: While working in Excel, it happens sometimes that you want to copy formatting from...
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ο¬nd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
Great work bro Weldon
how to change cell value ( many cells ) to comments at once??
hi, I need an example for my excel class to teach on conditional formatting and spark lines.
I have scenario
I need to use Cond formatting with formula like
If I want to show some text in the formatted cell .
Example
cell A1 value starting with “2” like 2806 text need to show : “RJB” in Cell B1.
if Cell A1 Value starting with “4” like 4739 text need to show : “HBB” in cell B1
anyone have solution please send me a email on
kelfgren@gmail.com
Thanks.
super idee PUNNET
I’m so glad you liked it.