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.
SYNTAX: TRANSPOSE FUNCTION IN EXCEL
The syntax ( the way how formula is phrased for excel) of MATCH is
=TRANSPOSE(ARRAY OF CELLS)
EXAMPLE 1:TRANSPOSE FUNCTION IN EXCEL:ARRAY
STEPS TO APPLY TRANSPOSE FUNCTION
EXAMPLE 2:TRANSPOSE FUNCTION IN EXCEL: A TABLE
STEPS TO APPLY TRANSPOSE FUNCTION [EXAMPLE 2]
GENERALIZED STEPS TO APPLY TRANSPOSE FUNCTION
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.
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.
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
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.