PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

EXCEL FUNCTIONS
-TRANSPOSE

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 Excel.

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 EXCEL

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.

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 Excel.
  •  Of course, Excel software.
Helpful links for the prerequisites mentioned above

SYNTAX: TRANSPOSE FUNCTION IN EXCEL

The syntax ( the way how formula is phrased for excel) of MATCH is

=TRANSPOSE(ARRAY OF CELLS)

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 EXCEL:ARRAY

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.

 

EXCEL FUNCTION TRANSPOSE EXAMPLE
EXCEL:TRANSPOSE FUNCTION:ANIMATED EXAMPLE

STEPS TO APPLY TRANSPOSE FUNCTION

STEPS FOR THE EXAMPLE TAKEN
If the array to be rotated from horizontal to vertical as discussed in example.
 
1. Select the exact number of cells in the same layout of the actual data which needs to be transposed i.e. with the same number of rows to be converted to same number of columns at a new place. Look at the picture above. We selected the exact 8 vertical cells before applying the function.(K7 TO K14 in this case)
2. Now type the formula =TRANSPOSE(C6:J6) but don’t PRESS ENTER.
3. Press CTRL+SHIFT+ENTER.
4. The selected ARRAY will be transposed.

EXAMPLE 2:TRANSPOSE FUNCTION IN EXCEL: A TABLE

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.COUNTRYCAPITAL
1INDIADELHI
2UKLONDON
3FRANCEPARIS
4USWASHINGTON
5INDONESIAJAKARTA
We will transpose this array using the TRANSPOSE function.

 

hOW TO USE TRANSPOSE FUNCTION IN EXCEL
EXCEL:TRANSPOSE FUNCTION EXAMPLE 2

STEPS TO APPLY TRANSPOSE FUNCTION [EXAMPLE 2]

STEPS FOR THE EXAMPLE TAKEN
Follow the following steps to rotate the table in EXCEL.
REFER PICTURE: ABOVE
 1. Check the number of rows and columns of the given table. e.g. our example table has 3 columns and 6 rows. Now select the layout for the transposed table i.e. 3 rows and 6 columns. Now do nothing, and start typing the formula starting with  an “=”. The selection won’t be removed.
2. Now type the formula =TRANSPOSE(C3:E8) but don’t PRESS ENTER.
3. Press CTRL+SHIFT+ENTER.
4. The selected table will be rotated [transposed]

GENERALIZED STEPS TO APPLY TRANSPOSE FUNCTION

If the array or a table is to be rotated from horizontal to vertical or vertical to horizontal as discussed in example.
1. Select the exact number of cells in the same layout of the actual data which needs to be transposed i.e. with the same number of rows to be converted to same number of columns at a new place.
 2. Now type the formula =TRANSPOSE(range of cells containing the data) but don’t PRESS ENTER.
3. Press CTRL+SHIFT+ENTER.
4. The selected ARRAY or TABLE will be rotated or transposed.

DO YOU KNOW

IF WE PRESS ENTER DIRECTLY AFTER TYPING THE TRANSPOSE FUNCTION, THE ANSWER WILL NOT BE AS PER EXPECTATIONS. PRESS CTRL+SHIFT+ENTER.

CONFUSION CLARIFICATIONS

SELECTION BEFORE USING TRANSPOSE FUNCTION

Selecting the cells before the usage of TRANSPOSE FUNCTION is very important. Let us check what will happen if the format is not exactly matching to the original cells.

The pre selection of the cells (where we intend to transpose the original set of data) behaves like a mask.

e.g.

Suppose we have some data which shows the sales of cars in different months.

Now , we will try to transpose this data with different pre selected cells.

 

 

EXCEL:ADDITIONAL EXAMPLES

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

PREVIOUS ARTICLE
NEXT ARTICLE

LEARN

Encourage us by following at …

Facebook
Twitter
Pinterest

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]