Today we are going to discuss about various methods in Excel to Transpose data.
This will involve User interface and Dynamic formula solution. So, lot to learn, Let's dive in.
Transposing is the process of converting the records or rows into fields or columns.
Range B4:F4 in input data represents the Jan month record for each product. In Expected result, same values have been placed in the range J3:J7 as column.
7 Row * 5 Column input data is converted into 5 Row * 7 Column.
Method-1:
Transpose through User interface,
Copy data from B3:F9 using the shortcut key CTRL + C,
Select your desired cell, in our case I3 cell,
Use shortcut key Alt + E + S + E.
That's it done, as simple as that. But this is static, when input data gets some change, we need to do the transpose again.
Method-2:
Transpose using function in Excel 2016 or earlier version,
This is an Array formula,
so, we need to press CTRL + SHIFT + ENTER, and
We should select our resulting range before typing the formula,
In our case, select the range I3:O7, and type the formula,
=TRANSPOSE(B3:F9) and press CTRL + SHIFT + ENTER.
You can see the curly braces before and after the formula. That means, it is an array formula.
Transpose using function in Excel 2019 or later version,
No need to select entire range and you can simply press ENTER.
use below formula in I3 cell,
=TRANSPOSE(B3:F9)
You can see the formula spills the result in I3:O7.
Method-3:
Formula in I3 cell,
=INDEX($B$3:$F$9,COLUMNS($I$3:I3),ROWS(I$3:I3))
Then drag the formula across rows and columns,
To understand the concept behind this construction, evaluate Row_num and Col_num argument in J3 cell using shortcut Key F9.
In Row_num argument,
Columns($I$3:J3) will deliver us 2,
In column_num argument,
ROWS(I$3:J3) will deliver us 1,
INDEX picks, 'jan' from the intersection of 2nd row and 1st column from input data.
That's how, column data are getting changed into records.
Method-4:
This is not dynamic but tricky and interesting, you can have some fun in this method,
Start typing xB3 in I3 cell, xC3 in I4, xD3 in I5, xE3 in I6 and xF3 in I7,
We used 'x' before cell reference, but you can type anything based on your preference.
Select from I3:I7, get your mouse cursor into right side bottom corner of the selection
and drag it till O column by clicking the left mouse button and release the click at O column.
Then select I3:O7 range and open Find and Replace dialog box by pressing shortcut Key
Alt + H. Type x in Find what text box and = in Replace with text box as shown below,
and click Replace All.
Since we change x into =, then xb3 becomes cell reference of =B3 and picks value from B3, B4, B5 till F9 and final result is our desired result of transposed data.
Method-5:
Transpose through Power Query,
To do that, we need to change our input data into Table,
Select the data B3:F9 and press shortcut Key CTRL + T, while keeping the table selected press ALT + J +T + A and start typing proper name for table, in our case it is 'Data'.
Press ALT + A + P + T to get table into Power Query Editor.
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Result = Table.PromoteHeaders( Table.Transpose( Table.DemoteHeaders( Source ) ) )
in
Result
Method-6:
Transpose through Power Query,
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
DemoteHeaders = Table.DemoteHeaders( Source ),
Result = Table.PromoteHeaders( Table.FromRows( Table.ToColumns( DemoteHeaders ) ) )
in
Result
Kommentarer