Google Sheets is a smart tool that you are most likely going to cross paths with no matter what field you come from. However, this smart tool does include its fair share of hiccups.

When working with a spreadsheet, especially a visual spreadsheet, you may need to change the format of your data, which can get complicated the larger your sets are. 

Thankfully, Google Sheets is filled with handy functions that can help achieve most of your interactions.

For this guide, we will be focusing on the transposing of data sets. Transposing data sets is simply the swapping of rows and columns and switching between the vertical and the horizontal view of data in a spreadsheet. 

We can do this by using the TRANSPOSE Function.

What is the TRANSPOSE Function?

The TRANSPOSE Function is one of Google Sheets’ numerous formatting formulas which is solely used for the purpose of switching between the vertical and the horizontal view of a data set in a spreadsheet by swapping the rows and columns of a data set.

The TRANSPOSE Formula is typed as follows:

=TRANSPOSE(array_or_range)

Where

• =TRANSPOSE is the function trigger,
• (array_or_range) is where we highlight the targeted data set.

Let us say we have a data set that is the list of scores of two separate tests that a teacher is reporting, after finishing his list, the teacher decides that a horizontal view of these scores would be a better fit for his report, the list is too long for a total redo.

The TRANSPOSE function would be perfect for this task. Have a look at our given list:

To switch this list from vertical to horizontal, we should first select a blank cell to write our formula in. For the above list, the TRANSPOSE Function will be used as follows:

=TRANSPOSE(A1:B11)

Press Enter or the Return Key after writing the above formula.

The list should automatically be transposed to look like this:

You can do the same to a horizontal set to switch it into its vertical format.

You can not, however, delete nor edit a single entry in the transposed set. Changes can only be made by deleting or editing an entry in the original data set. 

The only change you can do to the transposed data set is to delete it entirely.

Extra Help

 The TRANSPOSE function is not strictly used in the above method alone, it actually has a few more tricks up its sleeve. Jump TRANSPOSE function OR Using the TRANSPOSE Function Every N Rows/Columns

For limiting the TRANSPOSE function to every certain number of rows or columns, we have to first use the INDEX function to sort out these cells.

The INDEX function is used for fetching specific data out of a larger set of data according to a specified shared element. Its formula is as follows:

=INDEX(reference, [row], [column])

Apply this function to your original data set to extract your desired entries. For example, to extract an entry every 2 rows from our original data set, we will write the following formula:

=INDEX($A:$B,ROW(A1)*N-N+COLUMN(A1))

Where N is the number of jumps or skips the INDEX function makes.

Applying a skip of N=2, type this formula into a blank cell as follows:

=INDEX(A1:B11,ROW(A1)*2-2+COLUMN(A1))

Press Enter or the Return Key to reveal the fetched data and drag from the cell to copy the formula to the rest of the cells. It should look like this:

Then, apply the TRANSPOSE function to the results to shift the rows and columns into horizontal orientation using the following formula:

=TRANSPOSE(D1:E4)

Your results should look as follows:

Now your INDEX fetched data is transposed into a horizontal orientation.

Sheet to Sheet transposition

If you’re working across different sheets, you can use the TRANSPOSE function to send the transposed data set result into another sheet using only one formula. 

Using the data in our original spreadsheet, go to a new sheet, choose a blank cell and write the following formula:

=TRANSPOSE(Sheet1!A1:B11)

Press Enter or the Return Key to reveal the following results:

What Does the TRANSPOSE Function Do?

The TRANSPOSE function is one of the numerous functions of Google Sheets. It swaps the rows and columns of a certain data set which changes its orientation from vertical to horizontal or vice versa. 

The TRANSPOSE function is used across both cells and sheets to control and edit the way a set of entries is shown.

Individual entries in the transposed data set result can not be edited or deleted, this can only be done by applying changes to the original data set. 

You can only delete the entire transposed data set without applying changes to the original.

The TRANSPOSE function can not also overwrite pre-existing data, meaning that if a certain cell initially had data in it, an overlaying TRANSPOSE function result would be displayed as #REF. 

Fix this by either clearing out the preexisting data in that specific cell or by changing cell positions.

Conclusion

To sum up everything we have learned, the TRANSPOSE function is a Google Sheets feature that swaps the rows and columns in a highlighted data set to switch their orientation from vertical to horizontal or vice versa.

We discussed the TRANSPOSE function formula and applied it to an example data set. We applied the basic formula to the basic data set. 

We explored the TRANSPOSE function for every N row or column and its relation to the INDEX function. The INDEX function in Google Sheets works on fetching a specific section of entries from a larger data set of entries based on a shared element.

Lastly, we learned how to use the TRANSPOSE function over various sheets and the formula associated with it.

The TRANSPOSE function is an extremely simple and a highly helpful feature of Google Sheets. We hope this tutorial was clear, useful, and easy to follow.