Search results

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

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

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: 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

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

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

The DATE function creates a proper Excel date with given year, month, and day values. Then, it's simply a matter of comparing each date in the range with the date created with DATE. The reference B4 is fully relative, so will update as the rule is applied to each cell in the range, and any...Read more

The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values. Because the reference to B4 is fully relative, it will update as the rule is applied to each cell in the range,...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

This approach uses SUMPRODUCT to count the occurrence of every value in the table, one row at a time. Only when the same value appears in the same location in all three rows is a count generated. For cell B4, the formula is solved like this:

=SUMPRODUCT(($B...Read more

In this example, the goal is to highlight the 5 bottom values in B4:G11 where the number 5 is a variable set in cell F2.

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...Read more

In this example, the goal is to remove the asterisk (*) at the end of each text city/country name in column B. As usual, there are many ways to solve a problem like this in Excel. In the article below, we'll look at two good options. The first is a traditional formula based on the LEFT...Read more

In the formula, COUNTIFS counts the number of times each value in a cell appears in its "parent" column. By definition, each value must appear at least once, so when the count > 1, the value must be a duplicate. The references are carefully locked so the formula will return true only when...Read more

In this example, the goal is to calculate the number of days a ticket/case/issue has been open. We start counting on the date a ticket was opened and stop counting on the date a ticket was closed. If there is no closed date, the ticket is still open. Because dates...Read more

The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. Dates are just serial numbers in Excel, so earlier dates are always less than later dates. In the above formula, any dates that are greater than or equal to the start date AND less than or equal to...Read more

Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted:

$B3=$E$5

The second test is more complex:...Read more

The goal is to display a checkmark (also called a "tick mark" in British English) when a task is marked complete. The easiest way to do this is with the IF function and the mark you would like to display. The article below explains several options.

IF with a plain checkmark

The...Read more

This formula is evaluated for each of the 10 cells in A1:D10. A1 will change to the address of the cell being evaluated, while C1:C10 is entered as an absolute address, so it won't change at all.

The key to this formula is the =0 at the end, which "flips" the logic of the formula. For...Read more

In this example, the goal is to highlight all values in K4:M7 (values) that appear in the range B4:I15 (data). The range K4:M7 is named "values" for readability and convenience only. If you don't want to use a named range, use an ...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 automatically return the first day of the current month.

In this example, the goal is to generate a dynamic calendar for any given month, based on a start date...Read more

Working from the inside out, this part of the formula searches each cell in B4:B11 for all values in the named range "things":

--ISNUMBER(SEARCH(things,B4)

The SEARCH function returns the position of the value if found, and the #VALUE error...Read more

In this example, the goal is to filter data to show rows where dates have expired or will be expiring soon. In the table to the left, we have equipment that needs to be replaced every x months, where x appears in the "Months" column. The "Replaced" column shows the date equipment was...Read more

In this example, the goal is to calculate the difference between two times in Excel in hours and minutes. This basic problem comes up frequently when tracking time and may be described in various ways:

• Calculate elapsed working time in hours.
...Read more