Here, we will learn how to use the Excel dynamic array functions: TOCOL, TOROW, WRAPCOLS and WRAPROWS.
TOCOL function
The Excel TOCOL function transforms the given array into a single column. For example, to transform the array A4:C7, and to ignore blanks and errors in the array, use TOCOL like this: =TOCOL(A4:C7,3). The function can be found in Excel 365 - Excel 2024. TOCOL takes the arguments like this: (array, [ignore], [scan_by_column]), of which array is required. Array accepts the array that needs to be transformed. Ignore controls whether all the values in the array will be kept or ignore blanks or errors or both. To return all the values, use 0, that is the default value used when the argument is omitted. To ignore blanks, use 1. To ignore errors, use 2. To ignore both blanks and errors, use 3. Scan_by_column accepts a Boolean value (TRUE/FALSE) that controls how the function will scan the values in the array, by row or by column. FALSE scans the values by row, that is from left to right. TRUE scans the values by column, that is from top to bottom. Omitting the argument will force the function to take the default value of FALSE.
TOROW function
The Excel TOROW function transforms the given array into a single row. For example, to transform the array A4:C7, and to ignore blanks and errors in the array, use TOROW like this: =TOROW(A4:C7,3). The function can be found in Excel 365 - Excel 2024. TOROW takes the arguments like this: (array, [ignore], [scan_by_column]), of which array is required. Array accepts the array that needs to be transformed. Ignore controls whether all the values in the array will be kept or ignore blanks or errors or both. To return all the values, use 0, that is the default value used when the argument is omitted. To ignore blanks, use 1. To ignore errors, use 2. To ignore both blanks and errors, use 3. Scan_by_column accepts a Boolean value (TRUE/FALSE) that controls how the function will scan the values in the array, by row or by column. FALSE scans the values by row, that is from left to right. TRUE scans the values by column, that is from top to bottom. Omitting the argument will force the function to take the default value of FALSE.
WRAPCOLS function
The Excel WRAPCOLS function converts the given one-dimensional array into a two-dimensional array by wrapping the given number of values into each column. For example, to wrap the range S4:S15 into columns each containing 4 values, use WRAPCOLS like this: =WRAPCOLS(S4:S15,4). The function can be found in Excel 365 - Excel 2024. WRAPCOLS takes the arguments like this: (vector, wrap_count, [pad_with]), of which vector and wrap_count are required. Vector accepts the one-dimensional array that needs to be wrapped. Wrap_count accepts the number representing how many values each column can contain. Pad_with accepts a custom value to use for unfilled cells in the last column. Omitting the argument will force the function to fill each unfilled cell with the #N/A error.
WRAPROWS function
The Excel WRAPROWS function converts the given one-dimensional array into a two-dimensional array by wrapping the given number of values into each row. For example, to wrap the range S4:S15 into rows each containing 3 values, use WRAPROWS like this: =WRAPROWS(S4:S15,3). The function can be found in Excel 365 - Excel 2024. WRAPROWS takes the arguments like this: (vector, wrap_count, [pad_with]), of which vector and wrap_count are required. Vector accepts the one-dimensional array that needs to be wrapped. Wrap_count accepts the number representing how many values each row can contain. Pad_with accepts a custom value to use for unfilled cells in the last row. Omitting the argument will force the function to fill each unfilled cell with the #N/A error.
You have successfully learnt how to use the Excel dynamic array functions: TOCOL, TOROW, WRAPCOLS and WRAPROWS.
