How to use the TRANSPOSE function in Google Sheets

1. TRANSPOSE Function

1.1 Reorganization of Data

  Data reorganization in spreadsheets is one of the common data manipulation processes, especially if we want to make a meaningful summary. There are times where we want to change the presentation of the data by converting one of the variables from the data to a different orientation. By doing this, we can relate two or more variables or information,  extract meaningful interpretations and create visualizations easily. One of the functions that can change the orientation of the data is the TRANSPOSE function. 

1.2 What does the TRANSPOSE do in Google Sheets?

         The TRANSPOSE function takes in an array or a range of cells and converts the vertical lists to horizontal lists (Fig. 1.1) and horizontal lists to vertical lists (Fig. 1.2). This function is mainly applied to data reorganization, but it can also be applied to array operations. 

Figure 1.1 TRANSPOSE Function: Vertical to Horizontal

1.3 TRANSPOSE Syntax

Below is the syntax for TRANSPOSE:

There is only one input for the TRANSPOSE function: the array_or_range input, which is the name of the input itself. It’s the list of values that will be rearranged. Let’s take a look at basic examples (Fig. 1.1-1.2) to help us build an intuition on how the formula works. 

         The vertical list (A1:A4, including the header) in Fig. 1.1 is transposed  to a horizontal arrangement. We can also do the reverse, which is converting a horizontal list to a vertical list (Fig. 1.2). It’s important to note here that the cell formatting (except value formatting) is not copied to the output. Therefore, the cell formatting must be reapplied to the output data. 

Figure 1.2 TRANSPOSE Function: Horizontal to Vertical

  1. Breaking Down the Formula

Figure 2.1 TRANSPOSE Function

In this section, we’ll talk about the rules and criteria for the input of the TRANSPOSE function, the syntax, how TRANSPOSE processes its input and how the input affects the output. We’ll build an intuition to make it easier for us to visualize and create a TRANSPOSE formula for our problems.

2.1 TRANSPOSE Input/Arguments

2.1.1 Array_or_Range

This input can be an array or a range of cells. Therefore, the data can be stored in a range of cells or can be generated from other functions. Let’s look at the possible inputs for the TRANSPOSE function. 

Here are the important rules that you need to consider for the array_or_range:

2.1.1.1 Syntax and Location of the Array_or_Range

In Google Sheets, the array_or_range can be written or coded in three ways depending on the scenario:

  1. Range of Cells: 

Typically, our data set is stored in a sheet, and we want to be able to use this set of data in our formulas. In order to do that, we write the references of the cells where the data set is located. In the formula from Fig. 1.1, the reference A1:A4 means that the data set is in column A, row 1 to row 4 of the same column. The reference defines the address of the cell, and the formula uses the reference to access the value within the referenced cell. 

The main advantage of referencing the cells is that the formula becomes dynamic. We can change the values within the referenced cells, and the formula will update automatically in response to the changes. 

We can write the reference in the formula or highlight the cell. If we perform the latter, the spreadsheet automatically writes the references for us (Fig. 2.2), so we don’t have to write the references manually.

Figure 2.2 Referencing cells by highlighting them

Also, it’s important to know that we can refer to the cells from any sheet. This means that the cells can be:

  1. in the same sheet with the function
  2. in  another sheet within the same workbook
  3. Or in another sheet in another workbook (using ImportRange function)
  1. Hard-Coded Array:

We can directly write the list of values within the formula. For now, we can think of an array as a list of values that are not located in any sheet. To write them directly, we need to write the list inside the curly brackets {} (see the formula below).

=TRANSPOSE({1;2;3;4})

There are times where you don’t want to add any values within your sheets, and this method will let you do that. You don’t have to write the values in the sheet and reference them in the formula, especially if the values are constants and the size of the list is small. You can write the values directly like the formula above.

This method is for a smaller list of values that are constant. It’s inconvenient to use this in larger sizes of data or scenarios where we want your formula to be dynamic. 

  1. Arrays from other Functions

Sometimes we don’t want to use our raw data set directly. There are times where we want to extract some information from the raw data and use that extracted information in the formula. The example below summarizes a raw data set using the QUERY function. The resulting array is fed to the TRANSPOSE function, and therefore, the output will be rearranged.  

=TRANSPOSE(QUERY(A1:B6,”SELECT A, SUM(B) GROUP BY A”))

2.1.1.2 Dimension and Size of Arrange_or_Range

In spreadsheets, the dimension is related to rows and the columns. If our data is written in one column but multiple rows or one row but multiple columns, the dimension of our data is one-dimensional (1D). If our data is in a table with multiple rows and columns, then the dimension is two dimensional (2D). 

The rows and columns serve as coordinates of the cells within the spreadsheet. For 1D data, we only need to specify one coordinate (row or column) to know where the value is located. For 2D, we need to define the row and column to locate the value. 

The size of the data is related to the number of rows and columns, which also translate to the number of entries. 

The array_or_range can be 1D or 2D:

  1. One-Dimensional (1D):
    1. Horizontal List: 
  • Range of Cells (Fig. 1.2)
  • Hard-coded Arrays: (values in a horizontal list are separated by commas)

=TRANSPOSE({“Input”,1,2,3})

The array here is the same as the range A1:A4 in Fig. 1.2. 

  1. Vertical List: 
  • Range of Cells (Fig. 1.1)
  • Hard-coded Arrays: (values in a vertical list are separated by semicolons)

=TRANSPOSE({“Input”;1;2;3})

  1. Two-Dimensional (2D):
  • Range of Cells: (Fig. 2.3)

Figure 2.3 2D Range of Cells: Multiple Rows and Columns

  • Hard-Coded Arrays: (the array below is a 4 (row) by 2 (column) array)

=TRANSPOSE({“Code”,”Amount”;”YY-1003″,2000;”YY-1004″,1500;”ZZ-1001″,1000})

2.1.1.3 Data Type of Array_or_Range

The data type can be anything and can vary within the data set itself.

2.2 The TRANSPOSE Process

2.2.1 How does the TRANSPOSE work?

We already had a glimpse of how it works from section 1.3, and in this section we’ll look at more detailed examples of how the TRANSPOSE processes its inputs.

2.2.1.1 TRANSPOSE Coordinates

If the input is a 1D list, it’s easier to define the process of the TRANSPOSE function as converting the orientation of the list from horizontal to vertical or vertical to horizontal, but this is a superficial definition of the process. We’ve seen that it can also accept 2D inputs, and we can extend the aforementioned definition if we isolate the columns and the rows. The definition makes sense, but it’s not rigorous. Let’s try to connect the definition to the name of the function itself – transpose. 

Let’s look back at the data set from Fig. 2.3. First, let’s write down the row and column coordinates of each of the values in the data set (Fig. 2.4). Now, we will transpose the row and column coordinates. The row coordinate will become the column coordinate and vice-versa (Fig. 2.5). Then, let’s rearrange the values based on the new coordinates. Based on the new coordinates the “Code” header will remain at the same position while the “Amount” header will be relocated to row 2, column 1 (2,1). The first entry in the data set, YY-1003, will be relocated to row 1, column 2 (1,2) and so on. If we apply all of the new coordinates to the data set, it will be reorganized into the given table in Fig. 2.5. It’s important to note here that the row and column coordinates are relative to the data set and not based on the coordinates in the spreadsheet. 

Figure 2.4 Row and Column Coordinates of the Input

Figure 2.5 Transposed Coordinates

In summary, the TRANSPOSE function switches the row and column coordinates. This is the more rigorous definition of the process of the TRANSPOSE function. 

2.2.1.1 Formatting of Values

Cell formatting is not copied to the output except for value formatting. Notice the formatting of values in the Amount Column in the data set and the transposed data (Fig. 2.6). The value formatting is carried over to the output data. 

Figure 2.6 Formatting of Values

2.3 TRANSPOSE Output

2.3.1 Dimension and Size of the Output

The dimension, size and arrangement of the output of the TRANSPOSE function will be dependent on its input. For array results, just make sure that the destination cells of the outputs are vacant cells. If there are values that are occupying the cells where the results should be displayed, then an error will be returned instead of the results (Fig. 2.7). 

Figure 2.7 The TRANSPOSE function returns a #REF error due to C1 blocking the results

2.3.2 Data Type of the Output

The data type of the output will be the same as with the data type of the input.

  1. Applications of TRANSPOSE

In this section, we’ll tackle how to formulate your own TRANSPOSE for different scenarios. Some complicated tasks will require some tricks, but basically it all boils down to the rules of the TRANSPOSE function. We just need to follow the rules, and we’ll be able to create the TRANSPOSE formula that we want.

3.1 How to formulate an TRANSPOSE Formula 

  1. First we need to identify the input for our TRANSPOSE:
  1. Array_or_Range: It’s better to start determining the locations of the array_or_range. Is it an array? From another sheet? For array operations, what’s the arrangement?
  1. Preprocessing of Raw Data

Step 1 helps us identify if our data are suited as input for the TRANSPOSE function.  Most of the time, we don’t have control on the arrangement and values of our raw data. With this step, we can carefully follow the rules of the input and see if we need to take some preprocessing steps before we can apply the inputs to the function. Do we filter or sort the data set? Does the data set require more data manipulation before reorganization? 

  1. Setting up the TRANSPOSE Formula:

Step 1 and Step 2 will give us an idea how to arrange the input of the function and the format of the resulting output. It’s always better to visualize the input and process when constructing the formula. This will help us predict the dimension, size and arrangement of the results. Just remember that if the output is an array, we need to make sure that there are vacant cells for them to fill up. Otherwise, an error will be returned instead of the output. 

  1. Steps to write the formula:
  1.  In a cell where you want to write the formula, write =TRANSPOSE(

As you write the formula, Google Sheets will display the formulas that match what you’re typing (Fig. 3.1). You can then select from the options that are displayed by pressing the TAB key,  or just complete the formula by writing it manually. 

Figure 3.1 Writing the TRANSPOSE function

  1. Next, after the open parenthesis (, write the array_or_range syntax. If it’s a range of cells, we can highlight the cell reference. If we highlight the cells, the spreadsheet will automatically write the reference for us. If it’s an array, we can write it within the formula or nest other formulas that will generate the array. 
  1. We can write the close parenthesis or just hit ENTER, which will automatically complete the formula for us. 

3.2 How to do TRANSPOSE in Array Operations

The other important application of the TRANSPOSE function is in array operations. Before we delve into the applications, let’s look at examples of array operations. Let’s use the IF function for this scenario.  

There are three inputs in the IF function: logical_expression, value_if_true, value_if_false. The logical_expression is the condition that will be checked for TRUE or FALSE. The value_if_true is the value that will be returned if the condition is satisfied while the value_if_false is the value that will be returned if the condition is not met. 

Both three inputs of the IF function can be arrays or ranges of cells through the ArrayFormula function. How do the IF function process these inputs? Let’s look at the different examples below. 

  1. 1D Logical_Expression: One of the values is an array or a range of cell

The logical_expression is the condition, which is basically an equality or inequality, that will be checked in order to decide what values to return. We can divide the logical_expression into two parts: 

  1. Value1, Value2: The values that we want to check against each other. 
  2. The Logical Operators: equal sign (=), not equal to (<>), less than (<), greater than (>), less than or equal to (<=) and greater than or equal to (>=). 

The logical_expression, which is shown in the formula below, illustrates the aforementioned two parts. A2 serves as the value1 while the number 1 is the value2. The logical operator is less than (<). These are the necessary terms that complete the inequality, which returns TRUE or FALSE depending on the value of A2. This is the typical example of the syntax of the logical_expression:

=IF(A2<1,0.5,1)

The other inputs (i.e., value_if_true and value_if_false) are straightforward. We just need to input the values that will be returned based on the result of the testing of the condition.  

Now  that we’ve introduced the inputs, let’s look at examples. The task in the example below is to return an amount for each of the codes (Column A) based on the corresponding description (Column B) (Fig. 3.2). Instead of creating a formula for each of the codes, we’ll create one formula and input a range of cells. The value1 is a vertical list (B2:B6), and therefore, the output of the IF function also follows the same dimension, arrangement and size, but how does the IF function process the list? Let’s look at array operations and how array inputs interact with each other. 

Figure 3.2 1D Value1

The intuition is based on the layout of the spreadsheet. Using this intuition, let’s build visualizations and rules that will aid us in constructing the formulas. Let’s imagine the inputs as cells in spreadsheets, and arrange them like their arrangement in the formula, which is a vertical list for value1 and single values for the other inputs (Fig. 3.3). Next, let’s combine the inputs into one matrix by pairing them. The pairing is a one-to-one correspondence, which means that row 1 of value1 pairs with row 1 of value2, value_if_true and value_If_FALSE and so on, but there’s a problem in the size of the inputs. We can’t pair the inputs because they have different sizes (i.e., value1 is a 1D array while the other inputs are single values). To be able to pair them in a one-to-one correspondence, the dimensions, sizes, and arrangements of all the inputs must be the same. So how do we go about this problem? Let’s try to imagine adjusting the dimensions, sizes and arrangements until all of them are the same. We’ll adjust the dimension based on the input that has the largest dimension, which in this case is value1 (1D). For the size of row and column dimensions, we’ll do the same. To adjust, we duplicate the single values until they match the input with the largest dimension and size (Fig. 3.3). Now that all of the inputs have the same dimension, size and arrangement, we can now pair them into one matrix. 

This thought process here is just a way for us to visualize how the spreadsheet would evaluate the inputs, but it does not represent the actual process or what the spreadsheet is really doing in the background. We are just looking at the essence of the process using an analogy that works for us.

Figure 3.3 Matrix of Inputs: 1D Value1 vs. Single Value Inputs (Value2, Value_If_TRUE, Value_If_FALSE)

The IF function will follow the matrix of inputs above. It will start with the first row, evaluate the inputs and return the corresponding output. Then, it will evaluate the next row and so on. The final output data will follow the dimension, size and arrangement of the matrix of inputs. This is the basic foundation of array operations. Next, let’s look at a more complicated example.

  1. 1D Logical_Expression: Value1 and Value2 are both 1D Lists

What if both value1 and value2 are 1D arrays or ranges of cells? How do we arrange both inputs in the formula? If value1 is a vertical list, do we also set value2 as a vertical list like the example below (Fig. 3.4)? Or is it a horizontal list? Let’s delve in deeper to establish the rules that we need to follow when it comes to array operations. 

The task now is to separate the codes with YY and ZZ into separate columns. We’ll assign 1,000 in the amount if the condition is met and blank if the condition is false. The LEFT function below extracts the first two characters, which are the letters in the codes. The LEFT function normally doesn’t accept an array or a range of cells as input, which is also true for the IF function. Therefore, we use the ArrayFormula function to evaluate arrays or ranges of cells.

Figure 3.4 1D Value1 (Vertical) and 1D Value2 (Vertical)

Let’s follow the steps that we did earlier. Let’s imagine that the inputs are stored in cells and pair them to produce the matrix of inputs for this problem (Fig. 3.5). We can see that there’s a bit of a problem with adjusting the dimensions, sizes and arrangements, especially for value2. Value2 is a 1D vertical list with a different size than value1, and based on what we know so far, we need to adjust its size. The problem is that duplicating its values doesn’t make sense. If we analyze the task, what we need is to conduct a separate testing of conditions for each of the letter codes. One for YY and one for ZZ. If we just duplicate the values until its size matches the size of value1, then some rows in value1 will be tested for YY only while the others are only tested for ZZ. The spreadsheet doesn’t work that way. If you look at Fig. 3.4 and 3.5, it does not duplicate the values and leave the other rows of value2 empty. Therefore, if you look at the final matrix of inputs, the last three rows have empty value2s, and if you look back at the results in Fig. 3.4, the matrix clearly explains why there are #N/A Errors in the last three rows. The conclusion here is that if there are two or more 1D lists that have different sizes (not single values), they can’t have the same arrangement. If that is the case, then one of them must be vertical and the other is horizontal. This last statement here is the prologue to the TRANSPOSE function, but let’s hold it for now and continue with the discussion with the array operations. 

Figure 3.5 Matrix of Inputs: 1D Value1 (Vertical), 1D Value2 (Vertical) vs. Single Value Inputs (Value_If_TRUE, Value_If_FALSE)

Let’s apply the conclusion that we formulated above. Let’s arrange value2 into a horizontal list. Then, let’s adjust the dimensions and sizes. Value1 has the largest row size and value2 has the largest column size. Therefore, we’ll adjust all of the inputs based on these two. The result will be a 2D pairing (Fig. 3.6). The important thing here is that it’s always a one-to-one correspondence. Therefore, row 1, column 1 (1, 1) of value1 pairs with (1,1) of value2 and the other inputs. Row 1, column 2 (1, 2) of value1 pairs with (1, 2) of the other inputs. Then, move to the next row and so on. The resulting matrix of inputs will be a 2D array with a size that is equivalent to the row size of value1 and column size of value2. 

The IF function will follow the resulting matrix of inputs starting with the inputs in (1, 1) to (1, 2) and then the next row and so on. See the output data in Fig. 3.7.

Figure 3.6 Matrix of Inputs: 1D Value1 (Vertical), 1D Value2 (Horizontal) vs. Single Value Inputs (Value_If_TRUE, Value_If_FALSE)

Figure 3.7 1D Value1 (Vertical) and 1D Value2 (Horizontal)

Congratulations! You can now construct array formulas. Let’s dissect the scenario above and highlight the key points:

  1. There are two or more inputs.
  2. The inputs are 1D lists.
  3. The important factors: 1D inputs have different sizes but same arrangements.
  4. Let’s add another assumption: the inputs must be ranges of cells or arrays that are generated from other arrays. The example above used hard-coded arrays, so we’ll not use the same input. 

The key points above are the necessary ingredients for using the TRANSPOSE function to array operations. Let’s look at the same example from Fig. 3.7.

Instead of a hard-coded array for value2 ({“YY”,”ZZ”}), we’ll replace it with a range of cells (A2:A3) (Fig. 3.8). The value1 and value2 are both vertical lists with different sizes. The scenario below satisfied the four key points above. The IF function will return an incorrect output if we try to input the given arrangements. Therefore, we must convert one of the values into a horizontal list. In this case, the value2 is converted to a horizontal list using the TRANSPOSE function. 

Figure 3.8 IF with TRANSPOSE

In summary, if the array operations satisfy the four key points mentioned above, we can use the TRANSPOSE function to change the arrangement of the inputs in order for the array operations to work properly. 

3.3 How to Reorganize using TRANSPOSE

The main purpose of the TRANSPOSE function is to reorganize data, especially if we also want to summarize the data just like the example below (Fig. 3.9). The task in the example below is to create a summary by determining the total amount for each of the Subs per month, but let’s focus on the reorganization of the data. In this case, the months will be the headers, but we need to determine first the months that are present in the data set. 

First, we use the TEXT function to extract the month names from the dates (Fig. 3.10). Then, we use the UNIQUE function to extract the unique month names and remove the duplicates. Since the Dates are in a vertical list, the final array from the UNIQUE function will also be vertical, which contradicts the required arrangement of the headers. Therefore, we use the TRANSPOSE function to convert the vertical array to a horizontal array as shown in the figure below. 

Figure 3.9 TRANSPOSE for reorganization of data

Figure 3.10 TEXT to UNIQUE

3.4 How to TRANSPOSE every N number of rows

If we want to perform a complicated reorganization of data such as reorganizing the data set below, we may need extra steps in order to solve the problem. 

The data set below contains three sets of data of different categories: name, address and number (Fig. 3.11). This arrangement is common in scenarios where the data is copied from pdfs to spreadsheets. The task is to clean and organize the data set by separating the three sets of data into their own columns. 

Figure 3.11 Transpose every N rows

One of the known solutions for the problem above is to use the QUERY function to select and extract the data with the same categories (Fig. 3.12). The skipping clause will filter the data by skipping some number of rows, which in this case, is based on the number of categories (i.e., 3). The way it selects and extracts the data is shown in Fig. 3.12.  

Figure 3.12 QUERY: Skipping Clause

The QUERY function successfully extracted the names into one column. The next process is to use the TRANSPOSE function to convert the vertical list to a horizontal list (Fig. 3.13). Now, the data are separated into their own columns. Next, we copy or drag the formula from B1 up to nth number of rows, which in this case is 3 (Fig. 3.14). 

Figure 3.13 Applying the TRANSPOSE function

The data input of the QUERY function is a relative reference. Therefore, if we copy or drag the formula, it will adjust based on its position. In B2, the reference A2:A10 becomes A3:A11. It also moves one row. Therefore, the skipping will now start at A3 and will follow the count from it. The same process also applies to the QUERY formula in  B3. 

Figure 3.14 Copy the formula down Nth number of times 

3.5 How to TRANSPOSE 2D data into One Column

We can also perform the reverse of the scenario in section 3.4. We will combine all of the data into one column (Fig. 3.15). In this scenario, similar data are stored in different columns. The task is to combine them into one column. One way to do this is to combine the TEXTJOIN function with SPLIT function. The TEXTJOIN function combines text into one cell and separates them with a delimiter (i.e., separator) while the SPLIT function separates the text from one cell to cells in a row based on a delimiter (Fig. 3.16). The TRANSPOSE function will convert the horizontal list to vertical list.   

Figure 3.15 Transpose data into one column

Figure 3.16 TEXTJOIN to SPLIT

3.6 How to do TRANSPOSE with Inputs in Another Sheet

We can have inputs of TRANSPOSE from another sheet. The process of constructing the formula is still the same. We just need to reference the inputs from another sheet. 

3.6.1 TRANSPOSE with Inputs in Another Sheet (same Workbook)

The task is to convert the vertical list from the Data Sheet (Fig. 3.17) to a horizontal list in the Transpose Sheet (Fig. 3.18). 

Figure 3.17 The data set is located in a different sheet (Data Sheet) 

Here are the steps:

  1. Write = TRANSPOSE(
  1. Highlight (or write) the value1 and value2 for the logical_expression:

We can’t simply write A1:A4 because the input is now from a different sheet. We need to reference the cells together with the reference sheet. The fastest way to do this is to highlight the cells. While the cursor is inside the TRANSPOSE function, we go to the Data Sheet tab. Then, we highlight A1:A4, the spreadsheet automatically writes the cell reference including its sheet reference (Fig. 3.19). The sheet reference always starts with the sheet name followed by an exclamation mark (!). Then, this is followed by the cell reference. Complete the logical_expression. 

  1. Write a comma, and then, go back to where the destination sheet and press ENTER (Fig. 3.18). 

Figure 3.18 TRANSPOSE with inputs from a Different Sheet in the same Workbook

Figure 3.19 Highlighting the Cells from a different Sheet

3.6.2 TRANSPOSE with Inputs from Another Sheet (in another Workbook)

If the inputs are located from another workbook, then we need to use the IMPORTRANGE function in order to pull the data from another workbook. The IMPORTRANGE requires two inputs: the URL of the workbook and the sheet and cell references. Both inputs for the IMPORTRANGE must be in text format. Therefore, we need to enclose the URL and reference with quotation marks. IMPORTRANGE requires permission to pull the data from another workbook. Once access is granted, it will automatically pull the data.

Below is an example on how to apply the IMPORTRANGE to TRANSPOSE:

=TRANSPOSE(IMPORTRANGE("https://docs.google.com/spreadsheets/Example","Data!A1:A4")