Conditional Formatting Using Formulas in Excel
A tutorial on using Conditional Formatting Formulas in Excel.
There are significant limitations with the preconfigured options for Conditional Formatting. They typically only apply so a single cell without consideration for multiple factors. For example, in a previous tutorial I showed how to highlight a cell for an overdue payment based upon the date that it was due and the date that you specify for comparison. That formatting remains even after the payment has been made.
In order to correct that problem we need to use statements to examine the contents of other cells.
I will continue to use the previous example of overdue bills but will now add IF Statements to check if the bill has been paid and will also apply that format to the entire row of cells in my table instead of just the cells with the dates.
IF statements are usually written in the format of =IF(formula,then,else)
If you need to meet more than one criteria you would also use AND statements.
When using IF Statements with conditional formatting the IF is not typed out. Just type a formula like =$A2<$A$12 and choose the formatting you want to apply.
The formula =$A2<$A$12 when used with conditional formatting is equal to a standard Excel formal that says =IF($A2<$A$12, FormatWithColorRed,DoNotFormat)
That formula says that if the cell in A2 has a value which is less than the value in cell A12 then the formatting will apply otherwise no formatting will apply.
When using AND statements with conditional formatting the AND doesn’t need to be typed out. You just type the first formula in parentheses followed by * and then the second formula enclosed in brackets like =($A2<$A$12)*(E2<>0).
That formula says that if the cell in A2 has a value which is less than the value in cell A12 & the value in cell E2 is not equal to 0 then the formatting will apply.
Let’s start with the simplest example of using a single condition.
A spreadsheet I have a contains a list of dates in which payment is due. I have typed a date to use as a reference cell when comparing my list of dates to determine if they are past due.
I will select the all of the cells in my table that I want to format and select the drop down arrow on the Conditional Formatting menu item on the Home tab and choose New Rule. The option to ‘Use A Formula To Determine Which Cells To Format’ should be chosen.
In the ‘Format Values Where This Formula In True’ type in the Cell reference where the date is located (A2) with an absolute reference for column A ($A) and a floating reference for 2 as we want to copy this formatting to other cells. This should be followed by the Less Than < sign (or other options you wish to choose) and the absolute reference to the Cell where the reference date is located ($A$12). The formula should read $A2<$A$12 without an equals sign or IF statement. Then choose to format the cells in red when it matches that criteria.
*By default Excel will use absolute references for your cells and that creates problems when copying the formatting to other cells. Remove the $ from the cell number if that number will need to increment to apply the formatting to other cells.
If the date is less than the date in the reference cell the entire row of selected cells should turn red.
Create a New Rule and repeat the steps using the Greater Than > sign to format in Green and repeat once more using equal = to format in yellow.
Out next example will use the Conditional Formatting equivalent of an AND statement so the formatting only applies if the cell for outstanding payments does not have a zero dollar value.
The formula is basically the same as the previous example but we have to enclose the formula that checks the dates (located in Column A) in brackets then add an asterisk followed by another formula enclosed in brackets to check the cell containing outstanding payments (located in Column E)
IF A2 is less than A12 & E2 is not equal to 0 then apply formatting.
See the animation below to see this in action: