Search results

In this example, the goal is to return a list of the locations for a specific value in a 2D array of values (i.e., a table). The target value is entered in cell N5, and the table being tested is in the range C4:L16. The coordinates are supplied from row 4 and column B, as seen in the...Read more

In this example, the goal is to calculate the maximum value of every "nth" column in a row of data, where n is a variable entered in the named range M2. This problem can be solved in several ways, as explained below. The explanation below also includes a formula...Read more

The YEAR function extracts the year from a valid Excel date. For example:

=YEAR("15-Jun-2021") // returns 2021

In this case, we are giving YEAR and array of dates in...Read more

In this example, the goal is to return the total for an entire column in an Excel worksheet. One way to do this is to use a full column reference.

Full column references

Excel supports "full column" like this:

...Read more

The goal is to identify dates in column B that fall between a given start date and end date. The start and end dates are exposed as inputs on the worksheet that can be changed at any time, labeled "Start" and "End" in the example shown.

Named ranges

For convenience, both...Read more

In this example, the goal is to sum the amounts in column D by week number, using the dates in column C to determine the week number. The week numbers in column G are manually entered. The final results should appear in column H. All data is in an Excel Table named data in...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

The goal is to generate a series of dates by month based on a given start date. In the current version of Excel, the easiest way to solve this problem is to use the SEQUENCE function inside the EDATE function...Read more

The goal is to generate a series of sequential weekend days (Saturday and Sunday) with a formula. The start date is entered in cell B5. The number of dates to create (n) is entered in cell B8. If either of these two values are changed, a new list of weekend dates should be generated. In the...Read more

The goal is to return "Small" when the value in column D is "S" and "Large" when the value in column D is "L". In other words, if the value in column D is "S" return "Small" else return "Large".

If else in Excel

The concept of "If else" in Excel is handled with the...Read more

The WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. WEEKNUM takes two arguments: a date, and (optionally) ...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

In this example, the goal is to return the sum for an entire row in an Excel worksheet. One way to do this is to use a full row reference.

Full row references

Excel supports full row references like this:

=SUM(1:1) // sum all of row 1
=SUM(...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 extract the first 3 values or the last 3 values from the named range data (B5:B15). We also want to exclude any empty cells from our results. In the worksheet shown the formula in cell D5 is:

=INDEX(FILTER(...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 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

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

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

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