Google Sheets Functions – TRANSPOSE

Here we’re going to look at how we can use the TRANSPOSE function to change our data from being vertical to being horizontal, or vice versa.


Changing a single column or row of data

Here we have the days of the week in a column and we want to put them horizontally, so that each day is in a different column across the page.

functions9-1

In cell C1, I write the following function:

functions9-2

As you can see, it changes the information from being vertically stacked to being horizontally laid out.

functions9-3

This works both ways. If the original data was horizontally laid out, then in cell A1, we could write this function:

functions9-11

This would display the days vertically.


Converting 2 vertical columns to 2 horizontal ones

Here we have the days of the week and some teachers. I want to lay both sets of information horizontally.

functions9-4

This is easy, just include both columns in the range in the TRANSPOSE function.

functions9-5

This will then take column A and put it in row 1, then take column B and put it in row 2.

functions9-6


Converting multiple horizontal rows into vertical columns

Similar to the previous example, we can do the same for more than 2 sets of data.

functions9-7

Include the complete range in the TRANSPOSE function.

functions9-8

And sure enough, it transposes the information from rows to columns.

functions9-9


The above is using the TRANSPOSE function, to do it automatically for you, but remember you can also copy your data and using paste transpose, you can transpose it manually. Just right click, select Paste special, then Paste transpose. See my post on moving and pasting data for more examples.

functions9-10


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s