Search results

The SUBSTITUTE function is full automatic. All you need to do is supply "old text" and "new text". SUBSTITUTE will replace every instance of the old text with the new text.

If you need to perform more than one replacement at the same time, you'll need to nest multiple SUBSTITUTE...Read more

In this example the goal is to return rows in the range B5:E15 that have a specific state value in column E. To make the example dynamic, the state is a variable entered in cell H4. When the state in H4 is changed, the formula should return a new set of records. This is a perfect...Read more

Note: this example was created before the VSTACK function and HSTACK function were introduced to Excel. VSTACK combines ranges vertically and HSTACK combines ranges horizontally. These functions are a much...Read more

In this example, the goal is to sum the amounts in the table using the "Start" and "End" values in columns F and G. For convenience, all data is in an Excel Table called data, which means we can use the...Read more

In this example, the goal is to lookup the first negative value in a set of data. In addition, we also want to get the corresponding date. All data is in an Excel Table called data, in the range B5:C16. This information...Read more

In this example, the goal is to output the time between a start date and an end date as a text string that lists years, months, and days separately. For example, given a start date of 1-Jan-2018 and an end date of 1-Jul-2018, the result should be a string...Read more

In this example, the goal is to sum the amounts in column C by quarter in column G. Column D is a helper column, and the formula to calculate quarters from the dates in column B is explained below. All data is in an Excel...Read more

The goal is to filter the horizontal data in the range C4:L6 to extract members of the group "fox" and display results with data transposed to a vertical format. For convenience and readability, we have two named ranges to work with: data (C4:L6) and group...Read more

In this example, the goal is to return a count for each color that appears in column C, using the color values already in column E as criteria. When working with data, a common need is to perform summary calculations that show total counts in different ways. For example, total counts by...Read more

This formula relies on a technique called "nested IFs" to handle a series of options and results. With nested IFs, one IF function is nested inside another, a process that is explained in some detail here.

The formula in this example is purposely more...Read more

In this example, the goal is to calculate a count for the longest winning streak in a set of data. In the worksheet shown, wins ("W") and losses ("L") are recorded in column C, so this means we want to count the longest consecutive series of W's. Although we are specifically counting the...Read more

The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. Technically, the space character is the "range intersect" operator and the #NULL! error is reporting that the...Read more

This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and "flood" the text with spaces where we find one. Then we come back and grab text from the right.

Working from the inside out, we use the SUBSTITUTE function to find all...Read more

In this example, the goal is to list values in a given group that are within a given tolerance. The group is set in cell G4, and the target value is entered in cell G5. The allowed tolerance is entered in cell G6. The data comes from an Excel Table called...Read more

In this example, the goal is to count unique values that meet one or more specific conditions. In the example shown, the formula used in cell H7 is:

=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))

At the core, this formula...Read more

In this example, the goal is to calculate a total quantity for each color across the two ranges shown in the worksheet. The two ranges are "non-contiguous", which means they are not connected or touching. Both ranges contain a list of colors in the first column and quantities in the second...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:

1. Select the range B2:C11, starting at cell B2.
...Read more

In this example, the goal is to return a random group ("A", "B", or "C") at each new row. The simplest way to do this is to use the RANDBETWEEN function with the CHOOSE function. In the current version of Excel, it is also possible to generate all random groups in one step with the RANDARRAY...Read more

This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D14, which contains the full set of data without headers. The include argument is based on a...Read more

If you are new to the SUMIFS function, you can find a basic overview with many examples here.

The SUMIFS function is designed to sum numeric values based on one or more criteria. In specific cases however, you may be able to use SUMIFS to "look up...Read more

This formula relies on the FILTER function to retrieve data based on a logical test built with simple expressions and boolean logic:

(D5:D14="red")+(D5:D14="blue")
...Read more