How to TRANSPOSE Data in Google Sheets

Let’s say you have employee data with names in one row and their age in the next row. To make it more readable, it’s better to have it in a vertical mode where you have names in one column and ages in another.

In Google Sheets, you can transpose data from rows to columns and columns to rows. This allows you to restructure the data, make it more readable, or adjust the look you want.

In Google Sheets, you have three different options to transpose data.

• Use the TRANSPOSE Function =TRANSPOSE(A1:K2)
• Use the TRANSPOSE Option from the Paste Special
• Write a code that TRANSPOSE data from one range to another

In this tutorial, we will learn all of these methods in detail. But before that, let’s understand what transpose is.

What is TRANSPOSE in Google Sheets

When we talk about TRANSPOSE, it’s all about flipping the data. If you have your data in a row and need it in a column, or vice versa, TRANSPOSE can do it for you. Think of it like turning a range sideways. As I said, there are three different ways to do this in Google Sheets, and choosing the best method is completely up to you.

TRANSPOSE Function in Google Sheets

TRANSPOSE Function is easy to use. All you need to do is refer to the range you want to transpose and then hit enter to get the result. Below is the syntax of the function:

=TRANSPOSE(range)

In the above example, you can see that we have referred to the range A1 to K2. Now, this range has two rows, and we need to convert these rows into two columns.

So, in cell A4, we have entered the transpose formula referring to the range and then hit enter to get the result.

When you hit enter, the data from the rows is transposed into columns (switch rows and columns). The TRANSPOSE function is smart enough to understand the shape of the range; if it is a row, it transposes it as a column, and if it is a column, then it will transpose it as a row.

In the example below, when I used the TRANSPOSE function again and then referred to the range where we already have the transposed data, it converted columns into rows.

When using the TRANSPOSE function, check if the destination range has enough space to get the new data. Otherwise, the function will return an #REF! Error in the result.

Using TRANSPOSE with Other Functions

You can use TRANSPOSE with other functions to make it more powerful. Here’s how you can combine it with some common functions:

1. TRANSPOSE with SUM

If you want to sum a column of numbers after transposing, you can nest them together like this: =SUM(TRANSPOSE(A1:A10)). This will switch the column to a row and then sum the values.

2. TRANSPOSE with ARRAYFORMULA

If you want to apply a function to an entire range and then transpose the result, you can use =TRANSPOSE(ARRAYFORMULA(A1:A10 * 2)) to double each value in the range and then transpose it.

3. TRANSPOSE with VLOOKUP

You can transpose the range you’re searching within a VLOOKUP. For example, =VLOOKUP(“lookup_value”, TRANSPOSE(A1:B10), 2, FALSE) searches for “lookup_value” in the transposed range.

4. TRANSPOSE with QUERY

To rearrange data returned by a query, you can use =TRANSPOSE(QUERY(A1:C10, “SELECT A, B, C WHERE A > 10”)) to transpose the results of the query.

5. TRANSPOSE with FILTER

To filter data and then transpose it, use =TRANSPOSE(FILTER(A1:B10, B1:B10 > 100)) to filter rows where column B is greater than 100, and then transpose the result.

Using the TRANSPOSE Option from Paste Special

You have the transpose option in the paste special. When you copy data from a range of cells, Google Sheets allows you to use the paste special option to transpose the data. This is a quick and easy way to switch your data from rows to columns or vice versa.

You can use the below options to do this.

1. First, select the range of cells you want to transpose. Right-click and select “Copy”, or use the shortcut Ctrl+C (Windows) or Cmd+C (Mac).
2. Click on the cell where you want the transposed data to paste. This cell will be the top-left cell of the new transposed data.
3. Right-click on that cell, go to the over “Paste special,” option, and then select the “Transpose” option from there to paste the transposed data.

One of the best things about this method is that you get instant results. You don’t have to worry about writing functions or keeping track of formulas.

Note – When you want to paste data to a new range and transpose it along with that, check if the destination data has enough space to get data pasted there. The new data will overwrite the old data if you already have data.

Write Google App Script to Transpose Data

Below is the Google Apps Script that transposes data within the currently active sheet. This script will transpose data from the range A1:K2 in the current sheet and place the transposed data starting from the A4 cell.

function transposeCurrentSheetData() {
  // Get the active spreadsheet and sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();

  // Define the range to transpose (you can modify this to your specific needs)
  var sourceRange = sheet.getRange('A1:K2'); // Replace with your source range
  var data = sourceRange.getValues();

  // Transpose the data
  var transposedData = transposeArray(data);

  // Set the transposed data starting from a specified cell (A12 for example)
  sheet.getRange('A4').offset(0, 0, transposedData.length, transposedData[0].length).setValues(transposedData);
}

function transposeArray(array) {
  var transposedArray = [];
  for (var i = 0; i < array[0].length; i++) {
    transposedArray[i] = [];
    for (var j = 0; j < array.length; j++) {
      transposedArray[i][j] = array[j][i];
    }
  }
  return transposedArray;
}

To use this code, Click on Extensions in the top menu, then select Apps Script. And then, adjust the sourceRange to match the range you want to transpose. After that, specify where the transposed data starts (e.g., cell ‘A4’). Save the script with a name like “TransposeScript” or one you like to specify. Click the script editor’s play button (▶️) to run the script.