Excel 2024: Dropping, Taking, or Choosing from an Array

Say that you have an array in A1# that includes 1 row of headings, 10 rows of numbers and 1 row of totals. If you want to remove the headers, you could =DROP(A1#,1) to drop the top row.

=DROP(A1#, 2) will drop the top two rows.

The function syntax is DROP(array, rows, [columns]).

Any time that you use a negative number for Rows or Columns, Excel will drop from the bottom or right edge of the array.

=DROP(A1#,-1) will remove the bottom row.

=DROP(A1#,,-1) will remove the right-most column.

=DROP(A1#,-1,-1) will remove the bottom row and right-most column.

If you wanted to remove the top row and bottom row, you can not specify both 1 and -1 in the same DROP function. But you could use two DROP functions, like this: =DROP(DROP(A1#,1),-1).

While DROP is designed to remove rows or columns from the start or end of an array, the TAKE function will keep only the beginning or ending rows or columns from an array. =TAKE(Array, Rows, [Columns]). If you =TAKE(A1#,1), you will get the top row of the array. Negative numbers will take from the end of the array. To get the Total column, you could use =TAKE(A1#,,-1).

DROP and TAKE are oddly specific, in that they only point to the first or last rows/columns of an array. In contrast, CHOOSEROWS and CHOOSECOLS will let you specify any number of rows or columns. If you wanted to get the first and last rows of the array in A1#, you could use =CHOOSEROWS(A1#,1,-1).

If you wanted to get all of the odd numbered columns in an array, you could use =CHOOSECOLS(A1#, SEQUENCE(5,1,1,2)).

My question: when would you use DROP or TAKE versus CHOOSEROWS or CHOOSECOLS? Here is my early thoughts on this. DROP or TAKE only work on the rows or columns at the edge of the array. The one benefit of DROP or TAKE is that you can simultaneously remove a total row and total column.

You can DROP or TAKE multiple rows or columns, but only from one side of the array at a time. For example, you can remove the top 3 rows: =DROP(A1#,3) or the bottom 4 rows: =DROP(A1#,-4). But you can not remove the top row and bottom row without nesting two DROP functions.

The downside to CHOOSEROWS and CHOOSECOLS is that you can select only rows or only columns. But you can select any number of either: =CHOOSEROWS(A1#,1,2,5,6,9,10,11).