Explanation

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 SORTBY function sorts provided values by one or more “sort by” arrays. The sort by arrays make it possible to sort data in a completely custom manner. The main requirement of the sort by array(s) is that they have dimensions that are compatible with the data being sorted. In this example, the named range data (B5:B16) holds the first 12 letters of the alphabet. That means we need a sort by array that contains 12 values. For example, we can sort the values in data in reverse order with a hardcoded array constant like this:

=SORTBY(data,{12;11;10;9;8;7;6;5;4;3;2;1})

The semicolons in the array used for sorting indicate a vertical array in rows, the same as the source data. To generate a random array of numbers to sort with, we need another function.

RANDARRAY function

The RANDARRAY function generates an array of random numbers between two values. The size or the array is determined by rows and columns arguments. To generate 12 random numbers to sort with, we can use the RANDARRAY function together with the ROWS function like this:

RANDARRAY(ROWS(data))

ROWS returns the number of rows in data , which in this case is 12. This number goes into the RANDARRAY function as the rows argument, and RANDARRAY returns an array of 12 decimal values like this:

{0.489071793902109;0.380639786424253;0.12859884623431;0.520000510523814;0.638866975537127;0.105109233209619;0.219291392470457;0.938867459800217;0.782387454565537;0.915924172473614;0.73975376365456;0.50617850806796}

Note: The array above is only an example. Because RANDARRAY generates a new set of random values with every worksheet change, it is difficult to capture the exact values used to sort the array.

Final formula

Bringing the pieces explained above together, the final formula used in D5 is:

=SORTBY(data,RANDARRAY(ROWS(data)))

ROWS provides a count of rows to RANDARRAY, which generates a random array of 12 decimal numbers. This array is returned directly to the SORTBY function as the by_array1 argument. SORTBY uses the random values to sort the data, and returns the 12 letters into a spill range starting in D5.

Note: RANDARRAY is a volatile function and will recalculate every time the worksheet is changed, causing values to be resorted. To stop values from sorting automatically, you can copy the formulas, then use Paste Special > Values to convert formulas to static values.

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 .