Here, we will learn how to use the Excel dynamic array functions: CHOOSECOLS, CHOOSEROWS, DROP, EXPAND and TAKE.
CHOOSECOLS function
The Excel CHOOSECOLS function returns specific columns from the given array or range. For example, to return the first and third columns from the array A4:C12, use CHOOSECOLS like this: =CHOOSECOLS(A4:C12,1,3). The function can be found in Excel 365 - Excel 2024. CHOOSECOLS takes the arguments like this: array, col_num1, [col_num2],… of which array and col_num1 are required. Array accepts the array from which to extract columns. Col_num1 accepts the numeric index of the first column to return. It must be supplied as a whole number. Positive number extracts column from the start of the range, negative number extracts column from the end of the range. After col_num1, the function accepts additional optional col_num arguments to extract additional columns.
CHOOSEROWS function
The Excel CHOOSEROWS function returns specific rows from the given array or range. For example, to return the first, third, fifth, seventh and ninth rows from the array A4:C12, use CHOOSEROWS like this: =CHOOSEROWS(A4:C12,1,3,5,7,9). The function can be found in Excel 365 - Excel 2024. CHOOSEROWS takes the arguments like this: array, row_num1, [row_num2],… of which array and row_num1 are required. Array accepts the array from which to extract rows. Row_num1 accepts the numeric index of the first row to return. It must be supplied as a whole number. Positive number extracts row from the start of the range, negative number extracts row from the end of the range. After row_num1, the function accepts additional optional row_num arguments to extract additional rows.
DROP function
The Excel DROP function returns a subset of the given array by removing given number of rows and columns. For example, to remove 5 rows from the end of the array A4:C12 and 2 columns from the start of the same array, use DROP like this: =DROP(A4:C12,-5,2). The function can be found in Excel 365 - Excel 2024. DROP takes the arguments like this: array, [rows], [columns], of which array is required and at least one numeric value for rows or columns is required. Array accepts the array from which to extract a subset. Both rows and columns accept the number of rows and columns to remove respectively. Positive number removes row or column from the start of the range, negative number removes row or column from the end of the range. To return all rows and columns, use DROP like this: =DROP(array,).
EXPAND function
The Excel EXPAND function expands the given array by adding given number of rows and columns. For example, to expand the array A4:C12 to be 10 rows by 4 columns, and fill the new cells with "#", use EXPAND like this: =EXPAND(A4:C12,10,4,"#"). The function can be found in Excel 365 - Excel 2024. EXPAND takes the arguments like this: array, [rows], [columns], [pad_with], of which array is required and at least one numeric value for rows or columns is required. Array accepts the array that needs to be expanded. Both rows and columns accept a positive number that must be the same size of the array or more. To return all rows and columns, use EXPAND like this: =EXPAND(array,). Finally, the pad_with argument is also an optional argument that accepts the value to use for new cells. Omitting the argument will force the function to fill the new cells with the N/A error.
TAKE function
The Excel TAKE function returns a subset of the given array by extracting given number of rows and columns. For example, to extract 5 rows from the end of the array A4:C12 and 2 columns from the start of the same array, use TAKE like this: =TAKE(A4:C12,-5,2). The function can be found in Excel 365 - Excel 2024. TAKE takes the arguments like this: array, [rows], [columns], of which array is required and at least one numeric value for rows or columns is required. Array accepts the array from which to extract a subset. Both rows and columns accept the number of rows and columns to extract respectively. Positive number extracts row or column from the start of the range, negative number extracts row or column from the end of the range. To return all rows and columns, use TAKE like this: =TAKE(array,).
You have successfully learnt how to use the Excel dynamic array functions: CHOOSECOLS, CHOOSEROWS, DROP, EXPAND and TAKE.
