Drive

If you look at the formula in the upper-left cell (the one displaying A), it's exactly what you entered: =TRANSPOSE(A1:B3). If you look at the other cells' formulas, they will contain a CONTINUE formula. The cell displaying F, for example, contains the formula =CONTINUE($A$10, 2, 3). That indicates that the cell is continuing the output from cell A10 and displaying the entry in the 2nd row and 3rd column of that cell's result. Of course, if the formula in A10 changes (or the contents of the original range A1:B3 changes), then those changes will be reflected in the transposed array. And of course you can use the results of the transposed array just like the results of any other formula: cutting and pasting, having other formulas depend on those cells, etc.

If you now enter a formula in A10 that returns a larger range (e.g. =TRANSPOSE(A1:B6)), then the output array will grow to fill enough cells to display the entire new output. If you enter a formula that returns a smaller range (e.g. =TRANSPOSE(A1:B2)), then any leftover CONTINUE functions beyond the formula's result will display "--":



Now, suppose that before you started, there was data in one of the cells that got overwritten by the transpose output. For example, if A12 contained a value when you edited A10:

It's possible to have multi-cell array formulas grow automatically by adding more CONTINUE functions, so you don't have to worry about keeping them large enough. In any spreadsheets created after May 15, 2008 this will be the default behavior; in older spreadsheets, you can get it with the EXPAND function.

When you enter an formula in an older spreadsheet and you want it to expand, just wrap it in an EXPAND function. For example, if you want column B to contain a list of all the unique values in column A, you would normally enter =UNIQUE(A:A) in B1. If you want that list to grow as more values appear in column A, instead enter =EXPAND(UNIQUE(A:A)). Now, if you enter more unique values in column A, the data in column B will automatically grow to match its size.

Expand formulas can even add more rows to your spreadsheet, if they need to. Suppose you've set up Google Forms to fill in three columns on the first sheet of your spreadsheet, and you want the second sheet to contain all of that data, but in a sorted order. Just enter =EXPAND(SORT(Sheet1!A:C)) in the upper-right of the second sheet. You'll immediately see the current data sorted, and as more data gets entered, the second sheet will grow to accommodate it, so you always see your entire sorted dataset.

EXPAND works with any array-valued formula: the data query functions like ImportHtml, the filtering ones like FILTER or UNIQUE, and even regular ones like TRANSPOSE. It also works with multiple-input array formulas: try entering =EXPAND(ArrayFormula(A:A*2)) in B1, and the entire column B will always contain twice the values in column A, even if you insert or delete rows.

EXPAND does have one unusual side effect: you can't remove CONTINUE functions in a stretchy array, since the spreadsheet will notice they're missing and put them right back. But you can cut and paste them somewhere else, as long as you don't delete them entirely.

If you create a new spreadsheet, EXPAND is the default behavior for all multi-cell array formulas, even if you don't use the EXPAND function. If you want more control over what shows and what doesn't, you can duplicate the previous behavior with the NOEXPAND function. For example, =NOEXPAND(UNIQUE(A:A)) will only add CONTINUE functions when you first enter it, and when you press Ctrl-R on it, but never by itself.

"Single-cell" array formulas let you write formulas with array inputs, instead of array outputs. When you wrap a formula inside an =ARRAYFORMULA function, you can pass arrays and ranges to functions and operators that usually only take non-array arguments. Those functions and operators will apply to each entry in the arrays one at a time, and return a new array with all of the outputs.

For example, =ARRAYFORMULA({1,2,3} + {4,5,6}) computes 1+4, 2+5, and 3+6, and then returns {5,7,9}. If you enter that formula in a cell, it will output 5, 7, and 9 in three cells, using the multi-cell formula rules from above:



Of course, you could also use a range like A1:C1 instead of {1,2,3}. However, all of the arrays and/or ranges must be the same size and dimensions. So =ARRAYFORMULA(A1:C1 + A2:C2)) will work, but =ARRAYFORMULA(A1:C1 + A2:Z2)) will give an error (the first has three columns, while the second has 26), and so will =ARRAYFORMULA(A1:C1 + A2:A4)) (since A1:C1 has one row and three columns, while A2:A4 has three columns and one row).

The real power of ARRAYFORMULA comes when you take the result from one of those computations and wrap it inside a formula that does take array or range arguments: SUM, MAX, MIN, CONCATENATE, etc. For example, since =ARRAYFORMULA({1,2,3} + {4,5,6}) returns {5,7,9}, writing =ARRAYFORMULA(SUM({1,2,3} + {4,5,6})) is like writing =SUM({5,7,9}), so it returns 21. This lets you put an entire complex calculation into a single cell, hence the name "Single-cell array formulas".

Some more complex examples:

• =ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0))) For each cell in A1:A10, computes IF(Cell>5, Cell, 0), and then sums the results. The result is equivalent to =SUMIF(A1:A10, ">5").
• =ARRAYFORMULA(MAX(IF(A1:B100<256, A1:B100, 0))). For each cell in A1:B100, computes IF(Cell<256, Cell, 0), and then takes the maximum of the results. The end result is that you've found the largest entry in A1:B100 that's smaller than 256.
• =ARRAYFORMULA(SUM(IF(A1:A10>B1:B10, A1:A10, B1:B10))). First computes IF(A1>B1, A1, B1), then IF(A2>B2, A2, B2), and so on up to IF(A10>B10, A10, B10). Sums the results. The end result is that you've summed up whichever entry in each row is larger.
  

Note: Please be aware that array formulas cannot be exported.