Explanation

In this example, the goal is to remove empty rows from a range with a formula. One approach is to use the BYROW function to identify all non-empty rows in the range and pass this result into the FILTER function as the include argument. This is the approach used in the worksheet shown, where the formula in cell G5 is:

=FILTER(B5:E16,BYROW(B5:E16,LAMBDA(row,SUM(--(row<>""))>0)))

Working from the inside out, the BYROW function is used to check for non-blank rows like this:

BYROW(B5:E16,LAMBDA(row,SUM(--(row<>""))>0))

The purpose of BYROW is to process data in an array or range in a “by row” fashion. At each row, BYROW applies a custom LAMBDA function that contains the calculation needed to achieve the desired result. In this case, we want to identify non-blank rows. In other words, we want to check for rows that contain content in any cell. This is done in the following snippet:

LAMBDA(row,SUM(--(row<>""))>0)

The BYROW function delivers the range B5:E16 row-by-row to the LAMBDA above as the row argument. The LAMBDA then runs this calculation on each row:

SUM(--(row<>""))>0

The <> operator means “not equal to”, so <>"" means “not empty”. The result is an array of TRUE and FALSE values. In row 5, the result is an array like this:

=SUM(--({TRUE,TRUE,TRUE,TRUE}))>0

Next, the double negative (–) above converts TRUE to 1 and FALSE to 0:

=SUM({1,1,1,1})>0 // returns TRUE

The SUM function then returns 4, and the expression returns TRUE since 4 is greater than 0. Each row is processed in the same way, and BYROW returns all results in a single array like this:

{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}

Notice the array contains 12 results because the original range contains 12 rows. In this array, TRUE indicates non-empty rows and FALSE indicates empty rows. The array is returned directly to the FILTER function as the include argument:

=FILTER(B5:E16,{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE})

FILTER returns the 9 rows that correspond to TRUE as a final result.

Simple option

The formula above is more complex because it tests every cell in a row to make sure it is empty. If you only need to test a single column to determine if a row is blank you can use a simpler formula like this:

FILTER(range,CHOOSECOLS(range,1)<>"")

In this formula, we use the FILTER formula as before. However, for the include argument we use the CHOOSECOLS function instead of BYROW:

CHOOSECOLS(range,1)<>"" // column 1 only

CHOOSECOLS runs first and extracts just the first column from the range. Then we simply test each cell in the column with <>"". Because each cell corresponds to a row, the result is a single array of TRUE and FALSE values that are used to filter the range. Adapting this formula to the example above, we have:

=FILTER(B5:E16,CHOOSECOLS(B5:E16,1)<>"")

After CHOOSECOLS is evaluated, we have the following:

=FILTER(B5:E16,{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE})

The final result is the same as the FILTER + BYROW formula above. Note however that we are only testing column 1, so rows that contain data in other columns will be discarded.

More details

  • To simply identify blank and non-blank rows, see: Row is blank .
  • For more on the Boolean logic used above, see Boolean operations in array formulas .

Explanation

In this example, the goal is to sort a list of names and birthdays by month and year. The complication is that the birthdays also include a birth year, so if we try to sort the raw data by birthdays, we’ll end up with a list of birthdays sorted first by year. We will actually see the oldest people in the list first, because Excel dates are actually serial numbers that increase over time.

To sort the data by month and then by day, ignoring year, we need to construct another array in memory that we can use for sorting. This is done with the TEXT function, which we use to extract the month and day. In the example shown, the formula in E5 is:

=SORTBY(data,TEXT(birthdays,"mmdd"))

where data (B5:C16) and birthdays (C5:C16) are named ranges .

The SORTBY function can sort by one or more arrays. One of the benefits the SORTBY function provides over the SORT function is the ability to sort by an array that is not part of the data . For the array argument, we provide the full set of data (B5:C16). For the sort_by argument, we use the TEXT function like this:

TEXT(birthdays,"mmdd")

The TEXT function is a way to convert a numeric value to a text value using a specific number format . In this case, the number format is “mmdd”, which translates to a 2-digit month, followed by a 2-digit day (i.e. “0727” for the date in cell C5).

Because we give the TEXT function the full list of birthdays (C5:C16), which contains 12 dates, we get back 12 results in an array like this:

{"0727";"1025";"0116";"0425";"1126";"0203";"0826";"0907";"0512";"0323";"0607";"1210"}

Notice these values are actually text values (not numbers) but they work fine for the purpose of sorting in this case.

With this configuration, the SORTBY function returns all data sorted by the array returned by the TEXT function, and results spill into the range E5:F16, as seen in the example. Notice names and birthdays are now sorted by month, then by day. In other words, the birthdays are sorted in the order they will occur throughout the year.

Numeric sort variation

The formula explained above is based on creating text values that can be used for sorting. For the purposes of this example, this works fine. However, there are times where you might want or need a numeric sort option . In that case, you can use a variation like this:

=SORTBY(data,--TEXT(birthdays,"m.dd"))

The structure of this formula is the same as the formula above. However, the TEXT function is configured like this:

--TEXT(birthdays,"m.dd")

The number format “m.dd” will return a value like “7.27” for July 27, and “7.04” for July 4. We then use a double negative (–) to coerce the text value into a true number, which the SORTBY function uses to sort the data with the same result.

Older Excel versions

Because the SORTBY function is new in Excel 365 , you won’t find it in other Excel versions. In that case, the easiest way to solve this problem is to add a helper column to the original table with a formula like this, starting in cell D5:

=TEXT(C5,"mmdd")

Copy the formula down the table and name the helper column “Helper” in cell D4. Now you can use the “Helper” column to sort the table manually by month and day. It is possible to sort with a formula as well, but it is somewhat more complex . For more information on dynamic array alternatives, see: Alternatives to Dynamic Array Functions .