Celebrate King's Day with TNW 🎟 Use code GEZELLIG40 on your Business, Investor and Startup passes today! This offer ends on April 29 →

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

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:


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.

Get the TNW newsletter

Get the most important tech news in your inbox each week.

Also tagged with