In Google Sheets, if we ever need to flip (or transpose) the columns and rows of an array or a data range, TRANSPOSE formula is the one to go with.



array_or_range – the array or the address reference to the range whose columns and rows we need to be swapped or transposed.

Usage: TRANSPOSE Formula

This is one of the simplest formulas we encounter while using the Google Sheets spreadsheet application. Let us explore its working with help of a few examples, and we will use the following sample data.

TRANSPOSE Formula - Illustration 1

The test data spans from cells A1 through to C7. So, we will key in the formula in the cell D1 as illustrated in the snapshot below, and try to understand its behavior.

TRANSPOSE Formula - Illustration 2

We’ll see the returned data flows from D1 towards right and further down. The number of columns and rows returned by this formula is same as the rows and columns of the input array_or_range respectively. So, it is very important that we keep the cells, where we expect the data to flow, clear of any values. Otherwise, the formula returns #REF! error. This is demonstrated in the screenshot below.

TRANSPOSE Formula - Illustration 3

Going back to the second image, we will notice that the rows in the source range changes to columns and vice versa.

We have witnessed the working of the TRANSPOSE formula when we keyed in the address reference as its input parameter. Let us now try to input an array instead, as illustrated in the image below.

TRANSPOSE Formula - Illustration 4

The semi-colons separate each individual row values in the input range. Whereas commas separate each individual column values. Such an input is now transposed as shown in the image above. Just like in the previous case, the expected real estate for this formula should be free from any other values. Otherwise, it returns an #REF! error.

Image by Freepik