Table of Contents
- INTRODUCTION
- PURPOSE OF TRANSPOSE FUNCTION IN GOOGLE SHEETS
- PREREQUISITES TO LEARN TRANSPOSE FUNCTION
- SYNTAX: TRANSPOSE FUNCTION IN GOOGLE SHEETS
- EXAMPLE 1:TRANSPOSE FUNCTION IN GOOGLE SHEETS
- EXAMPLE 2:TRANSPOSE A TABLE USING TRANSPOSE FUNCTION
- DIFFERENCE BETWEEN MS EXCEL – TRANSPOSE FUNCTION AND GOOGLE SHEETS TRANSPOSE FUNCTION
INTRODUCTION
Suppose we have a data which needs to be rotated i.e. the rows should become the columns and vice versa. How would we do that?
One method is , to create a new sheet and start doing this manually but would that be an efficient solution. What if the report is too big like 1000 lines or so.
The solution to this problem is the TRANSPOSE FUNCTIONS.
If you remember matrices, we used to find out the transpose of the matrix. We do the same here with the data.
TRANSPOSE FUNCTION comes under the LOOKUP AND REFERENCE group of functions in GOOGLE SHEETS.
TRANSPOSE FUNCTION converts the ROWS INTO COLUMN AND COLUMN INTO ROWS ( CALLED TRANSPOSE )
The transposing of data is many times required where we need to change the layout of the data for any reason.
The transposing can also be done by COPYING THE DATA, AND PASTING SPECIAL AND CHOOSING THE TRANSPOSE FUNCTION but that’ll create an additional copy of the data.[ Procedure discussed separately ]
PURPOSE OF TRANSPOSE FUNCTION IN GOOGLE SHEETS
TRANSPOSE FUNCTION rotates the layout of the data i.e. rows becomes the columns and columns becomes the rows. Simply stating it swaps rows and columns.
Transposing is needed sometimes due to the layout issues or some other reasons. It is not practical to do this process manually as the report may contain thousands of cells. So this method is very helpful in such cases.
Transposing is needed whenever we want to change the rows to columns or columns to rows. The process is quite fast and easy to learn.
PREREQUISITES TO LEARN TRANSPOSE FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in GOOGLE SHEETS.
- Of course, Google Sheets login.
SYNTAX: TRANSPOSE FUNCTION IN GOOGLE SHEETS
The syntax ( the way how formula is phrased for GOOGLE SHEETS) of MATCH is
=TRANSPOSE(ARRAY OF CELLS OR RANGE)
ARRAY ARRAY IS A GROUP OF CELLS WHICH CAN BE DECLARED AS A RANGE .
ARRAY can be like
A1:A10
OR A1:C10 with three columns and ten rows
or
{1;2;3;4}
EXAMPLE 1:TRANSPOSE FUNCTION IN GOOGLE SHEETS
DATA SAMPLE
Let us try to transpose an array.
Suppose the following array is present horizontally.
1 2 3 4 5 6 7 8
We will transpose this array using the TRANSPOSE function.
EXAMPLE DATA
STEPS TO APPLY TRANSPOSE FUNCTION
STEPS FOR THE EXAMPLE TAKEN
Follow the steps to rotate the given array from horizontal to vertical as discussed in example.
- Simply double click the cell where you want the result to get started. [ As you know that we are dealing with the arrays, the result needs space.]
- Enter the formula as =TRANSPOSE(B5:J5)
- Press Enter.
- The transposed result will appear as shown in the following picture.
The result will be transposed array.
EXAMPLE 2:TRANSPOSE A TABLE USING TRANSPOSE FUNCTION
DATA SAMPLE
In previous example, we used the transpose function only on a single array. Now we’ll try it on a table.Let us try to transpose the following table.Suppose the following TABLE is present
| S.NO. | COUNTRY | CAPITAL |
| 1 | INDIA | DELHI |
| 2 | UK | LONDON |
| 3 | FRANCE | PARIS |
| 4 | US | WASHINGTON |
| 5 | INDONESIA | JAKARTA |
We will transpose this array using the TRANSPOSE function.
STEPS TO APPLY TRANSPOSE FUNCTION [EXAMPLE 2]
STEPS FOR THE EXAMPLE TAKEN
Follow the following steps to rotate the table in GOOGLE SHEETS .
REFER PICTURE: ABOVE
- Double Click the cell where you want the first value of the output to appear.
- Enter the formula as =TRANSPOSE(C5:E10).
- Press ENTER.
- The table will be transposed.
- The following picture shows the result.
GENERALIZED STEPS TO APPLY TRANSPOSE FUNCTION
We can transpose any array or a table simply by using the TRANSPOSE function easily.
The generalized steps to transpose the data are as follows.
- Select the cell where you want the result.
TAKE CARE THAT YOU HAVE ENOUGH SPACE FOR THE TRANSPOSED RESULT. OTHERWISE THE TRANSPOSED DATA MAY OVERWRITE THE FILLED CELLS.
- Enter the formula as =TRANSPOSE( FIRST CELL OF THE RANGE/ARRAY : LAST CELL OF THE RANGE/ARRAY).
- Press ENTER.
- The result will appear.
DIFFERENCE BETWEEN MS EXCEL – TRANSPOSE FUNCTION AND GOOGLE SHEETS TRANSPOSE FUNCTION
- The MS Excel TRANSPOSE FUNCTION needs to be executed in the different manner using the CONTROL+SHIFT+ENTER style.
But there is no requirement of Control+Shift+Enter in google sheets.
- MS Excel transpose function needs the select of the result cells before using the function, but there is no such requirement in the GOOGLE SHEETS.
