The best part of conditional formatting is you can use formulas in it. And, it has a very simple sense to work with formulas. Show
Your formula should be a logical formula and the result should be TRUE or FALSE. If the formula returns TRUE, you’ll get the formatting, and if FALSE then nothing. The point is, by using formulas you can make the 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 that you can use in your daily work. Steps to Apply CF with FormulasThe steps to apply CF with formulas are quite simple:
To learn this in a proper way make sure to download this sample file from here and follow the below-detailed steps.
=E5<1000
Related: Top 100+ Excel Tips and Tricks which will Make You a PRO in 2018 1. Use a Formula that is Based on Another CellYes, you can apply conditional formatting based on another cell’s value. If you look at the below example, we have added a simple formula that 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 in red color. 2. Conditional Formatting using IFWhenever I think about conditions, the first thing that comes to my mind is using the IF function. And the best part of this function is, that 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 with Multiple ConditionsYou can create multiple checks in conditions to apply to the format. 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 when the temperature is pretty pleasant. Months where the temperature is between 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 FormattingTo highlight every alternate row you can use the 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 can use for columns (odd and even) as well. =INT(MOD(COLUMN(),2)) And for even columns. =INT(MOD(COLUMN()+1,2)) 5. Highlight Cells with Errors using CFNow 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 FormattingNow let’s add some creativity to intelligence. You have already learned how to use a formula that is based on another cell. Here we have linked a checkbox with the 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]. How to do an if formula in conditional formatting?How to create a conditional formatting rule based on formula. Select the cells you want to format. ... . On the Home tab, in the Styles group, click Conditional formatting > New Rule…. In the New Formatting Rule window, select Use a formula to determine which cells to format.. Enter the formula in the corresponding box.. Can you use an if statement to color a cell?Apply an If-Then rule to all cells by pressing “Ctrl-A” before assigning your rule. If you copy values from colored cells and paste them into new it new cells, the new cells acquire the color of the copied cell. You may find the “Text Contains” rule useful when working with alphanumeric data.
Can you use an IF statement to format a cell?A cell can be formatted by conditional formatting based on the value returned by an IF statement on your Excel worksheet.
|