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:

<figure class="wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-4-3 wp-has-aspect-ratio"></figure>

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.

<figure class="wp-block-image size-full"></figure>

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.

<figure class="wp-block-image size-full"></figure>

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:

<figure class="wp-block-image size-large"></figure>

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.

<figure class="wp-block-image size-large is-resized"><img decoding="async" data-attachment-id="53565" data-permalink="https://www.thekeycuts.com/dear-analyst-117-new-2023-google-sheets-functions-for-data-manipulation-that-already-exist-in-excel/2-tocol-options/" data-orig-file="https://i0.wp.com/www.thekeycuts.com/wp-content/uploads/2023/05/2-tocol-options.png?fit=1046%2C1062&ssl=1" data-orig-size="1046,1062" data-comments-opened="1" data-image-meta="{"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"}" data-image-title="2-tocol-options" data-image-description="" data-image-caption="" data-medium-file="http