How to Use Array Formulas in Google Sheets.In early 2023, Google introduced several new features for Sheets, including eight for working with arrays. Using these functions, you can convert an array to a row or column, create a new array from a row or column, or append the current array.With more flexibility to work with arrays and going beyond the basic ARRAYFORMULA function, let’s see how to use these array functions with formulas in Google Sheets.Tip. Some of these features may sound familiar to you if you also use Microsoft Excel.
Transform an Array: TOROW and TOCOL
If you have an array in your dataset that you want to convert to a single row or column, you can use the TOROW and TOCOL functions.The syntax for each function is the same: TOROW(array, ignore, scan) and TOCOL(array, ignore, scan ) where both require only the first argument.
- Array: The array you want to convert, in “A1:D4” format.
- Ignore: no options are ignored by default (0), but you can use 1 to ignore spaces, 2 to ignore errors, or 3 to ignore spaces and errors.
- Scan: This argument specifies how to read the values in the array. By default, the function scans in rows or uses False, but you can use True to scan in columns if you like.
Let’s look at some examples using the TOROW and TOCOL functions and their formulas.In this first example, we will take our array from A1 to C3 and turn it into a string using the default arguments with this formula:=TOROW(A1:C3) 
As you can see, the array is now in a string. Because we used the default scan argument, the function reads from left to right (A, D, G), down, then left to right again (B, E, H) until completion – scanned line by line. 
To read an array by columns instead of rows, we can use True for the scan argument . . We will leave the ignore argument empty. Here is the formula:=TOROW(A1:C3,,TRUE) 
Now you can see that the function reads the array from top to bottom (A, B, C), top to bottom (D, E, F), and top to bottom (G, H, I). 
The TOCOL function works the same way, but converts the array to a column. Using the same range, A1 to C3, here is the formula with default arguments:=TOCOL(A1:C3) 
Again, using the default value for the scan argument, the function reads from left to right and provides the result as such. 
To read an array by columns instead of rows, insert True for the scan argument like shown below. :=TOCOL(A1:C3,,TRUE) 
Now you can see that the function reads the array from top to bottom instead. 
Create a New Array From Rows or Columns: CHOOSEROWS and CHOOSECOLS
You can create a new array from an existing one. This allows you to create a new range of cells with only certain values from another. To do this, you will use the CHOOSEROWS and CHOOSECOLS functions of Google Sheets.The syntax for each function is the same: CHOOSE (array, row_num, row_num_opt) and CHOOSECOLS (array, col_num , col_num_opt ),, where the first two arguments are required for both.
- Array: An existing array in “A1:D4” format.
- Row_num or Col_num: the number of the first row or column that you want to return.
- Row_num_opt or Col_num_opt: Additional row or column numbers. you want to come back. Google suggests using negative numbers to return rows from bottom to top or columns from right to left.
Let’s look at some examples using CHOOSEROWS and CHOOSECOLS and their formulas.In this first example, we will use an array from A1 to B6. We want to return the values in rows 1, 2 and 6. Here is the formula:=SELECT(A1:B6,1,2,6) 
As you can see, we got these three lines to create our new array. 
In another example, we will use the same array. This time we want to return rows 1, 2 and 6, but 2 and 6 in reverse order. You can use positive or negative numbers to get the same result.When using negative numbers, you must use this formula:=SELECT(A1:B6,1,-1,-5) 
To explain, 1 is the first string returned, -1 is the second string returned, which is the first string starting from the bottom, and -5 is the fifth string from the bottom.Using positive numbers, you would use this formula to get the same result:=SELECT(A1:B6,1,6,2) The CHOOSECOLS function works similarly, except you use it when you want to create a new array from columns instead of rows.Using an array from A1 to D6, we can return columns 1 (column A) and 4 (column D) with this formula:=SELECTCOLOR(A1:D6,1,4) 
We now have a new array with just those two columns. 
As another example, we will use the same array starting from column 4. Then we will first add columns 1 and 2 from 2 (column B). You can use both positive and negative numbers:=SELECTCOLOR(A1:D6,4,2,1) =SELECTCOLOR(A1:D6,4,-3,-4) 
As you can see in the screenshot above, with the formulas in the cells and not in the formula bar, we get the same result using both options.Note. Since Google suggests using negative numbers for posting results back, keep that in mind if you’re not getting correct results when using positive numbers.
Wrap to Create a New Array: WRAPROWS and WRAPCOLS
If you want to create a new array from an existing one, but wrap columns or rows with a certain number of values in each, you can use the WRAPROWS and WRAPCOLS functions.The syntax for each function is the same: WRAPROWS (range, count, padding) and WRAPCOLS (range, count, pad ), where the first two arguments are required for both.
- Span: The existing range of cells you want to use for the array, in “A1:D4” format.
- Count: The number of cells for each row or column.
- Padding: This argument can be used to place text or a single value in empty cells. This replaces the #N/A error you’ll get for empty cells. Enclose text or value in quotation marks.
Let’s look at some examples using the WRAPROWS and WRAPCOLS functions and their formulas.In this first example, we will use the cell range A1 to E1. We will create a new array spanning rows with three values in each row. Here is the formula:=WRAPROWS(A1:E1,3) 
As you can see, we have a new array with the correct result, three values per row. Since we have an empty cell in the array, the #N/A error is displayed. In the following example, we will use the pad argument to replace the error with the text “None”. Here is the formula:=WRAPROWS(A1:E1,3,”No”) 
Now we can see the word instead of the Google Sheets error.The WRAPCOLS function does the same, creating a new array from an existing range of cells, but does so by wrapping columns instead of rows.Here we will use the same array, from A1 to E3, wrapping the columns with three values in each column:=WRAPCOLS(A1:E1,3) 
As with WRAPROWS, we get the correct result, but also an error due to an empty cell. In this formula, you can use the pad argument to add the word “Empty”:=WRAPCOLS(A1:E1,3,”Empty”) 
This new array looks much better with a word instead of an error.
Combine to Create a New Array: HSTACK and VSTACK
The last two functions we’ll look at are for adding arrays. With HSTACK and VSTACK, you can combine two or more ranges of cells to form a single array horizontally or vertically.The syntax for each function is the same: HSTACK (range1, range2,…) and VSTACK (range1, range2,… ), where only the first argument is required. However, you will almost always use the second argument, which concatenates another range with the first.
- Range1: The first cell range you want to use for the array, in “A1:D4” format.
- Range2,…: The second range of cells you want to add to the first to create an array. You can combine more than two cell ranges.
Let’s look at some examples using HSTACK and VSTACK and their formulas.In this first example, we will combine the ranges A1 to D2 with A3 to D4 using this formula:=HSTACK(A1:D2,A3:D4) 
You can see our data ranges combined into one horizontal array.For the VSTACK function example, we will combine three ranges. Using the following formula, we will use the ranges A2 to C4, A6 to C8, and A10 to C12:=VSTACK(A2:C4,A6:C8,A10:C12) 
We now have one array with all of our data using the formula in one cell.
Manipulate Arrays With Ease
While you can use an ARRAY FORMULA in certain situations, such as with the SUM function or the IF function, these additional Google Sheets array formulas can save you time. They help you arrange the sheet exactly the way you want and with a single array formula.For more tutorials like this one but with functions other than an array, see How to use the COUNTIF or SUMIF function in Google Sheets.
How to Use Array Formulas in Google Sheets
How to Use Array Formulas in Google Sheets