I. Intro
Custom formulas for conditional formatting offer more ways to present data and more precisely highlight only the values that you need. The versatility of this feature is demonstrated with several use cases in this article. For basic operation instructions, see Use Conditional Formatting in Tables.
II. Custom formula rules
- Formula results can only be TRUE (or 1) or FALSE (or 0).
- The formula entered in Rules will apply to the first cell in the specified range (that is, the cell in the upper-left corner), and change accordingly based on the cell (relative cell reference). See the following example, in which we set cells that meet the specified condition to turn green:
- For formula A1 > 3, the value in each cell is evaluated against 3 and is highlighted in green for results that are TRUE.
250px|700px|reset
- For formula C2 > 3, the range in the red box (C2:F5) is evaluated against 3 and the corresponding cells in the specified range (A1:D4) are highlighted in green for results that are TRUE. This example demonstrates that conditional formatting does not necessarily have to be based on the values in the specified range itself.
250px|700px|reset
Note: Pay attention to what type of cell reference (absolute, relative, mixed) is used as the results will vary. The example above uses relative reference: that is, the row and column numbers for each cell in the formula change with the location of the cell.
III. Use cases
Simplified Gantt chart
Formula: AND(C$2>=$A3,C$2<=$B3)
Description:
By comparing the date corresponding to the cell (Row 2) to the start date (Column A) and end date (Column B), you highlight a cell that meets the conditions in green.
Note: Mixed cell reference is used in the formula: C$2 indicates that row 2 is fixed and $A3 and $B3 are fixed columns as these are the dates to be evaluated against. For example, when conditional formatting applies to cell D3, the formula is: =AND(D$2>=$A3,D$2< =$B3).
250px|700px|reset
Advanced Gantt chart
Formula:
- To start (yellow highlighting): AND(J$5>=$D8,J$5<=$E8)
- In progress (green highlighting): AND(J$5>=$F8,J$5<=$G8)
- Delayed (red highlighting): AND($G8>$E8,J$5>$E8,J$5<=$G8)
Description:
By comparing the dates in row 5 to the estimated start time (Column D), estimated end time (Column E), actual start time (Column F), and actual end time (Column G), you can highlight cells based on their status.
250px|700px|reset
Identify inconsistencies
Formula: $A2<>$B2
Description:
"<>" indicates "not equal to". By using mixed reference, columns A and B are fixed and the formula can be applied down the rows, and rows that are not identical are highlighted.
250px|700px|reset
Highlight missing values
Formula: COUNTIF($C$3:$C$10,A3)=0
Description:
The COUNTIF function is used to search through List B for the names in List A. If the number of times that a name appears is 0, it will be highlighted as it indicates the name is missing from List B. Note that the search range C3:C10 is fixed.
250px|700px|reset
Find students with grades that meet multiple requirements
Formula: AND($B2>70,$D2>80)
Description:
The AND function is used to find students with a math grade (column B) that is greater than 70 and an English grade (column D) that is greater than 80. Note that the "$" symbol used in columns B and D means they are fixed.
250px|700px|reset
Highlight search values
Formula: ISNUMBER(SEARCH($E$2,B2))
Description:
The SEARCH function is used to search for the value in E2 in the specified range (column B). This function returns the first appearance location of the searched value in a numerical format. Since results for conditional formatting formulas must be TRUE or FALSE, the ISNUMBER formula is used to determine whether the results are numerical values.
250px|700px|reset
Show next available date
Formula: AND(B2>TODAY(),B2<=(TODAY()+45))
Description:
If you want to quickly find the next available date in the next 1.5 months, you can use the TODAY formula to obtain today's date, use "(TODAY()+45)" to find the date 45 days from now, and then you can use the AND formula to determine the date range that needs to be highlighted.
250px|700px|reset