# 11 New Analytical Functions In Google Sheets For 2023

Coming hot on the heels of last year’s batch of new lambda functions, Google recently announced another group of new analytical functions for Sheets.

Included in this new batch are the long-awaited LET function, 8 new array manipulation functions, a new statistical function, and a new datetime function.

Let’s begin with a look at the new array functions. The LET function is at the end of the post.

## New Array Manipulation Functions

Although there are eight new array manipulation functions, you can think of them as four pairs of horizontal/vertical array functions.

Although most of this behavior was already possible with existing functions, these new functions simplify the syntax and are therefore a welcome upgrade. They also maintain function parity with Excel, which will help folks coming from that world.

### TOROW Function

TOROW transforms a range into a single row.

This formula turns the input array in A1:C2 into a single row:

=TOROW(A1:C2)

It has optional arguments that determine how to handle blank cells or error values, and also whether to scan down columns or across rows when scanning the input range.

### TOCOL Function

The TOCOL function transforms a range into a single column. It behaves in the same way as the FLATTEN function.

This formula in cell A5 transposes each row into a column format and puts them one atop another:

=TOCOL(A1:C2)

Like the TOROW function, it has optional arguments that determine how to handle blank cells or error values, and also whether to scan down columns or across rows when scanning the input range.

### CHOOSEROWS Function

Given a range of data, the CHOOSEROWS function lets you select rows by row number.

For example, this formula selects the first, second, and fourth rows:

=CHOOSEROWS(A1:C5,1,2,4)

This can also be achieved, of course, by the FILTER function or the QUERY function. However, this new function is a nice, lightweight alternative for when you know the row numbers and don’t need to perform a conditional test on each row.

### CHOOSECOLS Function

CHOOSECOLS is a welcome addition to the function family.

It lets you select specific columns from a range, which previously required a QUERY function and was more awkward because you use letter references for the columns.

This example selects the first and third columns from the input range:

=CHOOSECOLS(A1:C5,1,3)

### WRAPROWS Function

WRAPROWS takes a 1-dimensional range (a row or a column) and turns it into a 2-dimensional range by wrapping the rows.

It takes three arguments: 1) an input range, 2) a wrap count, which is the maximum number of elements in the new rows, and 3) a pad value, to fill in any extra cells.

In this example, the formula wraps the first row into multiple rows with a max of two elements on each row. Notice the second comma in the formula. This sets the pad value to blank, which means cell B8 is blank.

=WRAPROWS(A1:I1,2,)

### WRAPCOLS Function

WRAPCOLS takes a 1-dimensional range (a row or a column) and turns it into a 2-dimensional range by wrapping the columns.

It takes three arguments: 1) an input range, 2) a wrap count, which is the maximum number of elements in the new columns, and 3) a pad value, to fill in any extra cells.

In this example, the formula wraps the first row into multiple columns with a max of two elements down each column. Notice the second comma in the formula. This sets the pad value to blank, which means cell E4 is blank.

=WRAPCOLS(A1:I1,2,)

### VSTACK Function

The VSTACK function stacks ranges of data vertically.

For example, you could VSTACK to easily combine two (or more) datasets:

=VSTACK(A1:B5,D2:E5)

It takes the data in the range A1:B5 (which includes a header row) and appends the data from D2:E5 underneath, so you have it all in a single table:

### HSTACK Function

HSTACK combines data ranges horizontally.

For example, this formula in A14 combines three data ranges horizontally:

=HSTACK(A1:A5,C3:C7,A7:A11)

## Margin Of Error Function

The MARGINOFERROR function calculates the margin of error for a range of values for a given confidence level.

It takes two arguments: 1) the range of values, and 2) the confidence level.

Let’s use this dataset as an example:

The MARGINOFERROR function is:

=MARGINOFERROR(C2:C11,0.99)

which calculates the margin of error at a confidence level of 99%:

This is perhaps easier to understand on a chart, showing the margin of error bars (1 standard deviation) and the mean value (red line):

## Epoch To Date Function

EPOCHTODATE converts a Unix epoch timestamp to a regular datetime (in Univerasal Coordinated Timezone, UTC).

It takes two arguments: 1) the Unix epoch timestamp, and 2) an optional unit argument.

A Unix timestamp looks like this:

1676300687

The EPOCHTODATE Function takes this as an input (e.g. the Unix timestamp is in cell A1 in this example):

=EPOCHTODATE(A1)

The output of the function is:

2/13/2023 15:04:47

## LET Function

The LET function lets you use defined named variables in your formulas. It’s a powerful technique that reduces duplicate expressions in your formulas.

### LET Example 1

Consider this LET example, which categorizes the total sales using an IFS function.

The LET function allows us to define a “sales” variable that references the SUM of sales in column C. We can reuse the variable “sales” anywhere else in this formula:

=LET( sales , SUM(C2:C11) , IFS( sales>6000000 , "Exceed target" , sales>5000000 , "On target" , sales>4000000 , "Below target" ))

Without LET, the formula repeats the SUM expression repeatedly, which makes changing this formula more difficult:

=IFS( SUM(C2:C11)>6000000 , "Exceed target" , SUM(C2:C11)>5000000 , "On target" , SUM(C2:C11)>4000000 , "Below target")

### LET Example 2

This formula — using LET, SEQUENCE, and FILTER — will get all the weekdays (Monday – Friday) for the year ahead, starting from today:

=LET( dates , SEQUENCE(365,1,TODAY(),1) , FILTER( dates , WEEKDAY(dates,2)<6 ))

By using LET, you can avoid repeating the SEQUENCE expression.

## 26 thoughts on “11 New Analytical Functions In Google Sheets For 2023”

1. Tocol and Torow will be useful Monday morning. Thanks for the good summary, Ben!

1. Ben says:

You’re welcome. Cheers, Per! I agree that these new functions will be very handy.

2. eddy says:

Thanks for your sharing. Interesting new functions.

A few days ago I had used choosecols function in my reports, but this morning the function result show “Unknown function: ‘CHOOSECOLS'”.
In this moment, I found function list when type choose, only display 1 function (CHOOSE).

1. Ben says:

They’re rolling out over a period of weeks, so you should get them soon…

1. The God of Biscuits says:

All 11 of these functions were actually supposed to be rolled out by February 15th, but Google have temporarily pulled all eight of the new array manipulation functions because they were causing sheets to crash and lose data in some contexts…

1. Ben says:

Yes, they are currently unavailable (2/23/23), which is unfortunate!

1. Helman Frow says:

Yeah, as of today the only ones working for me are:
MARGINOFERROR()
EPOCHTODATE()
LET()

3. Frank says:

Great content again, Ben. Thanks for the overview.

I was wondering what the usecase of VSTACK/HSTACK was over ={…}. In the examples in you article both could be used. VSTACK/HSTACK seems to be able to handle different size of row/columns, where curly brackets is very strict. Also seems to have more options for to giving/handling errors in cases where the array sizes do not match. Good improvement!

1. Ben says:

Hi Frank – I agree. I think the VSTACK/HSTACK will be more flexible and easier to use than the older array literals {…} that few people are even aware of.

Cheers,
Ben

1. McKay Savage says:

Do we have any way of knowing if the performance of VSTACK and HSTACK versus {} arrays will be the same? I’m worried that while more convenient, in complex formulas it will be slow enough to be noticeable.

1. Ben says:

Great question… don’t know the answer yet, but it would be worth some research.

2. Jean-Rene Bouvier says:

The only case where I found VSTACK better than {;} is when you pass arrays of different width to vstack. Example:
=VSTACK(A1:C1,B2,C2,A3,C3)
will trigger a #N/A error in the 3rd cell of the second output row that you can nix, by encapsulating vstack in an IFERROR, as in:
=IFERROR(VSTACK(A1:C1,B2,C2,A3,C3),”value for missing cells”)
You cannot do that with curly braces.
Are there other interesting cases for VSTACK/HSTACK ?

1. Frank says:

Elaborating on your point Jean-Rene, the most interesting use-case for me is when getting data from multiple sources (multiple sheets with the same output format) where a single source might be empty (no output). With the {} you’d get an error because that array is empty, with vstack you can catch the error and still get a result from the other output tables.

4. rootdown42 says:

Great summary. I always appreciate how your posts keep me up-to-date on what Google’s releasing.

1. Ben says:

You’re welcome!

5. McKay Savage says:

FYI Ben, a little mistake I think: it is the new TOCOL function that is the same as FLATTEN, not VSTACK as you wrote above. Flatten always returns a single column.

1. Ben says:

Good spot. Thank you! Fixed now.

6. Olusola Timothy Ogundepo says:

So happy to have this in our very own Google sheet! These functions are all game changer…

7. This is great, thanks so much!

8. Zhangali says:

LET function is really powerful. Thank you for sharing!

9. Zhangali says:

Something weird is happening… I have tried all these functions in a new blank sheet, and they worked. Then I am using them in another sheet (that was created before this update) but getting “Unknown function” error for all the functions, excel LET and EPOCHTODATE. Same account and same PC. That seems, those functions don’t work within old sheets. Hope that will get fixed soon.

10. Oops – sent this as an email before scrolling down and seeing the comments section.

Hi Ben

Yours is the best website I have found for tips on Google sheets. Love it! Keep up the great work.

A suggestion on your excellent article on VStack . . .

It would help if it addressed this situation:

Our ranges to be stacked will continue to grow as we add data. One might be 50 rows long today and grow to 60 rows tomorrow and 90 rows next week. If we choose one or more ranges that go beyond the populated range to accommodate future growth–or even to the bottom of the page, such as =vstack(A4:D,G4:J55), then the stacked range will include all the empty rows of that range.

Perhaps there is a solution for that so we only get populated rows in the output?

Thank you, and keep up the great work!

11. Learned a lot of new functions. Will they work in Microsft Excel because 50% of my work is offline. Btw, thank you so much for the teaching.

12. Julie says:

The documentation doesn’t relay this but the real beauty of the hstack and vstack operations is that you can combine arrays that aren’t necessarily the same aspect ratio (sometimes called jagged arrays or ragged arrays). With the vstack and hstack you just get the #N/A in the cells where there’s a non-matching value for that column / row. This is very different from other array operations (such as use of {} together with “;” and ‘,’); those just return an error (mismatched row size, mismatched column size) and no part of the joined array. These new formulas make it much easier to debug because you can see what is mismatched, and sometimes you just don’t need them to match anyway.

13. Eric says:

One use for the LET function could also be for “documentation/readability”.

i.e. “sales>5000000” is easier to understand than “SUM(C2:C11)>5000000”.

I could see myself using it just for this purpose alone, I have sometimes used “+ N(‘This arrayformula does …’)” just to remind me “how this works” 6 months from now.