Dear Analyst #117: New 2023 Google Sheets functions for data manipulation that already exist in Excel

The Google Workspace team announced a slew of Google Sheets functions a few months ago (February 2023). These functions look familiar and that’s because Microsoft Excel released most of them two years ago. I never had a chance to play around with the new functions in Excel since I don’t have the latest Office 365 version. Now that they are live in Google Sheets, I played around with them and find them pretty interesting for data manipulation purposes. I think what’s interesting about these new functions is that they help with both super basic data organization use cases but also more advanced data cleaning use cases too. Here’s a rundown of some of the new functions and more importantly, examples of real-life use cases. If you want a copy of the Google Sheet I use in this episode, go here.

Watch a tutorial showing all the new Google Sheets functions in 2023:

What’s interesting about these “new” Google Sheets functions?

Here’s a quick rant on these “new” Google Sheets functions. They aren’t new. They are basically a direct copy of what exists in Excel already (if you have Office 365). I think Google Sheets has some pretty awesome features that differentiate it from Excel (auto-fill, collaboration features, it’s free, etc.) But I’ve always viewed Google Sheets as a tool that is playing catchup to Excel. These functions are an example of Google playing catchup with Excel’s features versus coming up with something new.

These “new” functions in Google Sheets also highlight something Microsoft discovered a few years ago about how people are using spreadsheets: data is not organized in a structured way. You have time periods across the columns and the rows. You have headers and sub-headers. People don’t typically organize and clean their data for the purposes of a PivotTable but rather for ease of use. With this in mind, I think these new Google Sheets functions are targeted at the beginner spreadsheet user who may just be using Google Sheets to show who’s sitting at different tables at a banquet dinner or showing a shift schedule.

Next to each function, I also put a usefulness rating (🌶 being not useful and 🌶🌶🌶🌶🌶 being really useful) based on what I think would be useful for a beginner Google Sheets user.

1) EPOCHTODATE() – Turn computer-generated dates into a human-readable date format

USEFULNESS RATING: 🌶

This is a pretty basic one. You’ll typically get epoch dates when getting some output from a database or any type of computer-generated date/time. It’s usually a long string of numbers and EPOCHTODATE simply converts that “computer time” into a date and time that us humans can comprehend.

Gave this a rating of 1 because I don’t see many instances where you’ll have the epoch time format in your spreadsheet save the rare occasion you have a a Unix export of data that has these epoch times.

2) TOROW(), TOCOL() – Arrange a bunch of cells into a single row or column

USEFULNESS RATING: 🌶🌶🌶🌶🌶

Also a pretty simply formula that helps with basic data manipulation tasks. Big fan of this one because it removes the need to cut and paste ranges of data on top of each other. I think TOCOL() will be used more often just because you typically want to get a continuous list of values in one column. Here’s an example where you have a bunch of names arranged by groups (perhaps groups of students in a class) and you just want to get all the names in one column:

There are also some interesting options that let you remove errors and blanks as well as how the data should be “scanned” and put together. Someone just asked me how to do a data manipulation task similar to this and using TOCOL() with the scan_by_column flag set to false does the trick.

3) CHOOSEROWS(), CHOOSECOLS() – Choose which rows or columns you want from a data set

USEFULNESS RATING: 🌶🌶🌶🌶

I would put these new functions in the camp of “making it easier to filter out the data I don’t need.” I find this useful when you know when you want to quickly get the top 3 scores or maybe the top score and bottom score from a list of test scores, for instance. There are probably a bunch of other use cases I’m not able to think of, but in general it’s a really useful function to quickly “pull out” the rows or columns of data you need from a data set. CHOOSEROWS() in action:

While we’re at it, I’d say CHOOSECOLS() is equally as useful because you can just pull out the columns of data that matter for you. In this case, you can just pull out the list of students and just the scores from the subjects that matter for you. This feels like a more user-friendly version of the {} syntax for concatenating different ranges to create a custom range (typically used for creating a custom VLOOKUP formula with multiple conditions)

4) WRAPROWS(), WRAPCOLS() – Turn a bunch of cells into a specified number of rows or columns

USEFULNESS RATING: 🌶🌶

Kind of an interesting formula for a specific use case (I think). You put in a list of cells, and then the number of rows or columns you want to turn the list into. I don’t find these formulas that useful because your data has to be in really bad shape to warrant using these formulas. Then again, I may not be thinking of all the use cases where one would use these formulas.

For instance, you might have a list of employees with their location, job, etc. all listed out versus properly arranged in columns. This is where you would use the WRAPROWS() formula:

A more realistic use case is you have a list of names and you want to put them into 3 groups. You would use WRAPROWS() to quickly put this list of names into 3 columns:

In this case the number of names don’t fit perfectly into 3 columns so there are two N/As at the end. There’s this handy pad_width parameter which kind of acts like an IFERROR() function where you can just put in a placeholder value for those extra cells:

5) VSTACK(), HSTACK() – Stack rows from different sheets on top of each other

USEFULNESS RATING: 🌶🌶🌶

I think the reason why VSTACK() might be useful is when you have data coming in on multiple sheets. The data is also structured the same across those three sheets. Then you can have one primary sheet that aggregates all the data using VSTACK().

Not sure when you might use HSTACK() but the example Google shows is when you’re combining dates together. Kind of a weird scenario, but sure whatever.

In this Google Sheet, I have 3 sheets called shows1, shows2, and shows3. Each sheet has the same columns in the same order, but the data is different between the three:

Then with VSTACK(), you can “add” or concatenate all these data sources together on one page:

Again, this assumes your data is structured exactly the same across sheets or even on the same spreadsheet. If the data is, then using VSTACK() could be a nice way to put together these “disparate” data sources compared to using the bracket syntax {}. This feels like an alternative to CHOOSEROWS() where Google Sheets is just making it easier to use the {} syntax.

6) LET() – Assign the result of a formula to a variable to use in the future

USEFULNESS RATING: 🌶🌶

I have mixed feelings about the usefulness for this formula. It technically already exists using named ranges. But this is the formula version of named ranges. I also wouldn’t say it’s that much easier to understand compared to a named range hence the 2-pepper rating. It’s also not a “beginner” function.

Say you have a bunch of product ratings like in the table below. In the Average Score column, you want to put the word “High” if the average rating for a product is greater than 4. If the average rating is between 3-4, then you want the word “Medium.” 3 or below should say “Low”:

Today, you might write a simple formula like this to get this output of “High” and “Low”:

=if(average(B44:D44)>4,"High",if(average(B44:D44)>3,"Medium","Low"))

A typical nested IF statement. Now with the LET() function, you simple are assigning the average(B44:D44) “result” to a variable. The formula below would output the same exact thing as the nested IF statement above:

=LET(avg_rating, average(B44:D44), if(avg_rating>4,"High",if(avg_rating>3,"Medium","Low")))

Here’s a look at the formula in the context of the example:

The formula doesn’t look that much “easier” compared to writing out the nested IF statement. But for more complicated formulas beyond a regular average, this could make the formula much more readable and easier to debug.

One reason I like this function is that it starts to bridge the gap between working in a spreadsheet and using Google Apps Script (or Office Script if you’re in Excel). Starting to treat things like variables might make the learning curve to scripting in Google Apps Script easier and more approachable to a Google Sheets user who has never touched an Apps Script.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting: