Want to keep the TNW Conference vibe going?? Tickets for TNW2022 are available now >>

The heart of tech

This article was published on March 26, 2020

This fancy Google Sheets formula is the coolest function you’ll never use

This fancy Google Sheets formula is the coolest function you’ll never use
Yaron Yitzhak
Story by

Yaron Yitzhak

Yaron is a Data Analyst & Audience Developer at TNW. He also enjoys writing stories on the intersection of finance and technology, and e Yaron is a Data Analyst & Audience Developer at TNW. He also enjoys writing stories on the intersection of finance and technology, and explainers on how to work with data even if you don’t have advanced technical skills.

Welcome to TNW Basics, a collection of tips, guides, and advice on how to easily get the most out of your gadgets, apps, and other stuff.

From budgeting and keeping track of your investments, to project management and data analysis; Google Sheets can be used for an extremely wide variety of tasks.

Being a data nerd and having some programming skills myself I often carry out quite sophisticated tasks, but I still appreciate sheets and use it everyday. Due to its simple user interface, collaborative features, and array of capabilities that require no coding.

I teach the basics of ‘playing with data with Google Sheets for journalistic purposes’ to our editors and writers. It was then that I noticed I taught a particular formula that I simply found very cool, but didn’t really have a use for, and probably most other people won’t either.

[Read: This is the fastest way to start a new Google doc, sheet, or presentation]

It’s called transpose, and what it allows you to do is to grab a table of data and switch the columns and the rows. Like this.

Original table

Now add a new sheet, and type the following formula in cell A1 and hit enter:

=TRANSPOSE(Sheet1!A1:C6)

Replace the ‘Sheet1’ part with the name of the sheet containing the table.

New table

There you go, the rows are now treated as columns, and the columns as rows. Good luck with finding a purpose for this, other than boasting your spreadsheet skills.

Also tagged with