Dave Bruns

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 dates one year apart. In the current version of Excel, the easiest way to do this is with the SEQUENCE function together with the DATE, YEAR, MONTH, and DAY functions. In older versions of Excel, you can use the same date functions and a more manual approach....Read more

The goal is to generate a series of sequential dates with a formula. In the current version of Excel, the easiest method is to use the SEQUENCE function. SEQUENCE can return all dates at the same time into a range on the worksheet. In older versions of Excel without the SEQUENCE function, you...Read more

In this example, the goal is to create a list of 10 random names from a larger list of 100 names. In other words, we want to select a random subset of names from a larger list. The names to select from are in column B, starting in row 5. The formula should handle any number of...Read more

In this example, we are sorting a table with 10 rows and 3 columns. In the range J5:J7 (the named range custom), the colors "red", "blue", and "green" are listed in the desired sort order. The goal is to sort the table using values in the...Read more

In this example, the goal is to perform a random sort of the data in B5:B16 with a formula. This can be done with the SORTBY function and the RANDARRAY function.

SORTBY function

The...Read more

In the example shown, we want to sort data in B5:D14 first by group in descending order. Here is the configuration needed:

• array = B5:D14
• by_array1 = D5:D14
• sort_order1 = 1

The formula below will sort data by group A-Z...Read more

The SORT function requires very little configuration. In the example shown, we want to sort data in B5:D14 by the third column, Group. For array, we provide entire range, B5:D14. For sort_index, we provide 3:

=SORT(B5:D14,3)
...Read more

This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the COUNTA function as an array like this:

...Read more

This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove limit data to values associated with group A only:

FILTER(B5:B16,C5:C16=E4)

Notice we are...Read more