How to Transpose Data in Excel

Many of us use Microsoft Excel to maintain a database. However, we sometimes do face a problem with the various functions in Microsoft Excel. One such function is ‘transpose’. If you wish to change the order of the data in Microsoft Excel, you should know ‘how to transpose data in excel’.

Using the transpose function, you can shift the data in the columns to rows, and the data in rows to columns. ‘Transpose’ function has to be used as an ‘array formula’. Array formula calculates manifold values and returns one single value. Using ‘transpose’ you can change the horizontal and vertical orientation of a range in a worksheet. Follow the steps written below to transpose data in excel.

How to transpose data in excel?

  • Click on ‘Start’ menu – go to ‘All Programs’, then click on ‘Microsoft office’, and choose Microsoft Excel from the options appearing below it.
  • Alternatively, you can right click on the desktop, place the cursor on ‘New’ and choose ‘Microsoft Excel’ from the list of options.
  • Now open a new document or the document where you have saved the data.
  • Next step is selecting a range of data with size, similar to another range, containing data. Both the ranges that you select should be similar in size, but with the columns and rows reversed. For example, let’s consider the rows and columns to be A1 through B4 respectively.
  • Click a cell. In this cell, you will have to type the formula for transpose. The formula, considering the example stated above will be ‘=Transpose(A1:B4)’.
  • This formula will create a transposed link for the cells A1:B4 in the worksheet.
  • Next, starting with the formula cell, select A5:A7 range. After having selected this range, press F2 followed by Ctrl+Shift+Enter.
  • Check the formula before selecting the range because if the formula is not entered as an array formula, then the single resultant will be ‘1′
  • At last, hit ‘Enter’ and all the transposed data will appear in the selected cell range.
  • If, after having put the formula correctly and selected the right range of cells, you cannot see the transposed data, then check if Excel is functioning correctly, or else you can try again.  It looks a bit complicated but simplifies with practice.

How to transpose data from columns to rows?

Another very simple way transposing data from rows to columns or vice versa is using the ‘Paste Special’ option.

Suppose you have entered data in two columns and four rows, and wish to transpose the data to four columns and two rows, you cannot think of shifting data from each cell. If you have a very small data, you can think of manually transposing it, but if you have huge data, transposing it will lead to many mistakes.

Using the method given below you can transpose data easily.

Consider the example below.


Ron John
A 12 22
B 30 10
C 5 15
D 20 16

Fig. 1

  • In this example, Ron and John’s data is mentioned in four different categories in four rows & two columns.
  • Now if you wish to shuffle the data as shown below (Fig.2), you will need to transpose the data.


A B D
Ron
John

Fig. 2

Using Transpose Function:

  • First, select the entire data you wish to transpose. That means in this example you will select the data of Fig. 1 on the excel sheet. Copy this data either by right click option and select ‘copy’ or simply by ‘Ctrl+C’.
  • Second step select an empty cell on other sheet, or somewhere below in the empty space on the same sheet. Right click and choose ‘Paste Special’ option.
  • In the ‘Paste Special’ option box, check the box corresponding to ‘Transpose’.
  • Click ‘Ok’. On clicking ‘Ok’ your data will be transposed as shown in the figure below.


A B C D
Ron 12 30 5 20
John 22 10 15 16

Fig. 3 Transposed Data

Follow these simple steps and transpose the data, as you want without any mistakes.


Related Content:

  1. How to Calculate Date Difference in Excel
  2. How to Protect Data
  3. How to Calculate Median
  4. How to Calculate Mode
  5. How to Password Protect a File
.

Leave a Reply