Search results

This formula uses the MIN function to make a decision that might otherwise be handled with the IF function. Although MIN is usually used to return the minimum value in a data set with many numbers, it also works fine for...Read more

This example uses the UNIQUE function. With default settings, UNIQUE will output a list of unique values, i.e. values that appear one or more times in the source data. However, UNIQUE has an optional third argument, called occurs_once that,...Read more

In this example, we have comma-separated text in column B. The goal is to split the text in column B into columns D through G while at the same time converting the numbers to true numeric values. The challenge is that TEXTSPLIT always returns text, so we need a way to convert the numbers...Read more

In this example, the goal is to count test scores in column C that are less than 75. The simplest way to do this is with the COUNTIF function, which takes two arguments, range and criteria...Read more

The example shown contains almost 10,000 rows of data. The data represents temperature readings taken every 2 minutes over a period of days. For any given date (provided in cell H7), we want to get the maximum temperature on that date.

Inside the IF...Read more

At the core, the OFFSET function delivers a range of 3 cells to SUM, which returns a summed result.

The arguments for OFFSET are provided as follows:

For reference we use the first cell in the data range, B5, entered as a ...Read more

Dates in Excel are just serial numbers, formatted to display as dates. This means you can perform math operations on dates to calculate days in the future or past.

In the example shown, the date in the named range "start" is provided by the TODAY...Read more

The goal is to mark records with an "x" when the color is "Red" and the size is "Small". To perform this task, you can use the IF function in combination with the AND function.

IF function

The...Read more

This formula is an interesting example of a "brute force" approach that takes advantage of the fact that TRIM will remove any number of leading spaces.

Working from the inside out, we use the SUBSTITUTE function to find all spaces in the text, and replace each space with 100...Read more

The goal is to create a formula that returns "Done" in column E when a cell in column D is not blank (i.e., contains a value). In the worksheet shown, column D records the date a task is completed. If column D contains a date (i.e. is not empty), we can assume the task is complete...Read more

This formula concatenates the original value in column B to a string of asterisks (*) assembled with the REPT function so that the final result is always 12 characters:

REPT("*",12-LEN(B5))

Inside the REPT function, the text to repeat is...Read more

The COUNTIF function 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...Read more

The goal is to verify the input of all required values before calculating a result. In the worksheet shown, the SUM function is used as an example only. You can use the same approach with any formula to prevent calculation until all required values are available. The logic can be...Read more

In this example, the goal is to demonstrate how an INDEX and (X)MATCH formula can be set up so that the columns returned are variable. This approach illustrates one benefit of the 2-step process used by INDEX and MATCH: Because INDEX expects a numeric index for row and column numbers, it is easy...Read more

This example uses the UNIQUE function, which is fully automatic. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11.

UNIQUE is a dynamic function. If any data in B5:B16 changes, the output from UNIQUE will update...Read more

In this example, the goal is to sum the last n columns in a set of data, where n is a variable that can be changed at any time. In the latest version of Excel, the easiest way to solve this problem is with the TAKE...Read more

This formula uses the MIN function as an alternative to the IF function.

Although MIN is frequently used to find the minimum value in a larger set of numbers, it also works fine with just two...Read more

In this example, the goal is to create a clickable link that will result in a ready-to-send email.

The mailto link protocol

The mailto link protocol allows five variables as shown in the table below:

The goal in this example is to sum Prices in column C when the Items in column B contain an asterisk (*). The challenge is that the asterisk (*) is reserved as a wildcard in functions like the SUMIFS function, so you can't match a literal occurrence of this character without using a...Read more

The FILTER function is designed to extract data that matches one or more criteria. In this case, we want to apply criteria that requires all three columns in the source data (Name, Group, and Room) to have data. In other words, if a row is missing any of...Read more

The gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess.

Working from the inside out, the original text in B5 is flooded with spaces using...Read more

The SUMPRODUCT function multiplies arrays together and returns the sum of products. The trick is to use simple array expressions to "cancel out" the irrelevant rates in the table. SUMPRODUCT then simply sums the rates that remain.

Working from...Read more

When no argument is provided, the ROW function returns the "current row", that is, the row number of the cell that contains it. When a cell reference is provided, ROW returns the row number of the cell. When a range is provided, ROW returns the first row number in the range.

In the...Read more

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:

1. Select the
...Read more

In this example, the goal is to look up and retrieve all names for a given team and return them in a single cell as a comma‑separated list. At the core, this is a lookup problem, but the twist is that we want to return multiple matches for each team, not just one. That means...Read more