The trick with this approach is the calendar header (row 4), which is just a series of valid dates, formatted with the custom number format "d". With a static date in D4, you can use =D4+1 to populate the calendar. This makes it easy to set up a...Read more
In this example, the goal is to highlight differences in two ranges, B2:B11 and C2:C11, using conditional formatting. To do this, we need to create a new conditional formatting rule, triggered by a formula, like this:
In this example, the goal is to highlight empty cells in the range C5:J16 with conditional formatting. This is a quick and easy way to locate missing values in a data set. To apply a conditional formatting rule to highlight empty cells, follow these steps:
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the address of the active cell (B5) is used for the row (5) and entered as a mixed address, with column D locked and the...Read more
Conditional formatting is evaluated for each cell in the range, relative to the upper left cell in the selection. In this case, the formula uses the MOD function to check the remainder of dividing the value in each cell, with the value in cell E2, which is 9. When the remainder is zero, we know...Read more
The Excel TEXT function returns a number in a given number format, as text. You can use the TEXT function to embed formatted numbers inside text.Read more
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. So, in this case the formula =B4>100 is evaluated for each of the 40 cells in B4:G11. Because B4 is entered as a relative address, the...Read more
The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. The TODAY function returns the current date. Dates in Excel are simply large serial numbers, so you can create a new relative date by adding or subtracting days. TODAY() + 30 creates a new date 30...Read more
When you use a formula to apply conditional formatting, the formula is evaluated for every cell in the selection. In this case, there are no addresses in the formula, so, for every cell in the data, the ROW and ISEVEN functions are run. ROW returns the row number of the cell, and ISEVEN returns...Read more
When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply the rule to B4:G11, with B4 as the active cell, the rule is evaluated for...Read more
Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will dynamically return the first day of the current month.
With the layout of grid as shown, the main problem is to calculate the date in the first cell in the...Read more
When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply the rule to B4:G11, with B4 as the active cell, the rule is evaluated for...Read more
The QUARTILE function is automatic, and will calculate the 1st quartile with an input of 1, the 2nd quartile with an input of 2, and the 3rd quartile with an input of 3. With an input of 0, quartile returns the minimum value in the data.
The trick in this case is to arrange the...Read more
The Excel ISODD function returns TRUE when a value is an odd number, and FALSE when a value is an even number. ISODD will return the #VALUE error if a value is not numeric.Read more
In this example, the goal is to apply conditional formatting to cells that begin with specific text, which is entered in cell G2. The highlighting is done automatically with a conditional formatting rule applied to the range B4:G12. The rule type is "Use a formula to determine which cells...Read more
The key to this approach is the calendar header (row 4), which is just a series of valid dates, formatted with the custom number format "d". With a hardcoded date in D4, you can use =D4+1 to populate the calendar. This allows you to set up a conditional formatting rule that compares the date in...Read more
Conditional formatting is applied to all cells in the active selection at the time a rule is created.
In this case, the column references are locked to prevent columns from changing as the formula is evaluated, but the row references are relative so that row numbers are free to change....Read more
COUNTIF simply counts the number of times each value appears in the data range. By definition, each value must appear at least once, so when the count equals 1, the value is unique. When the count is 1, the formula returns TRUE and triggers the rule.
Conditional formatting is evaluated...Read more
This formula uses the TEXT function to concatenate the month and year of each date. Then, the two dates are tested for equality. TEXT is a useful function that allows you to convert a number to text in the text format of your choice. In this case the format is the custom date format "myyyy",...Read more
Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, cell B3 in this case.
To highlight matching rows, we use this logical expression:
$B4=$K$5
The reference...Read more
The MOD function returns the remainder after division. With a divisor of 1, MOD will return zero for any whole number.
We use this fact to construct a simple formula that tests the result of MOD. When the result is zero (i.e. when the number is an integer) the formula returns TRUE,...Read more
COUNTIF simply counts the number of times each value appears in the range. When the count is more than 1, the formula returns TRUE and triggers the rule.
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection...Read more
The SEARCH function returns the position of the text you are looking for as a number (if it exists). Conditional formatting automatically treats any positive number as TRUE, so the rule is triggered whenever search returns a number. When SEARCH doesn't find the text you're looking for, it...Read more
This formula uses two named ranges, "range1" (B5:B12) and "range2" (D5:D10).
The core of this formula is the COUNTIF function, which returns a count of each value in both range inside the AND function:
COUNTIF(range1,B5) // count in range1
COUNTIF(
...Read more
This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don't want to use named ranges, make sure you use absolute references for both of these ranges in the formula.
This formula is based on the...Read more
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each cell in B4:G12, and the reference to B4 will change to the address of each cell being...Read more
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 10 cells in B2:B11, and B2 will change to the address of the cell being evaluated...Read more