Many times situation occurs when it is required to move rows data to column in MS excel. This can be done very easily through Excel Formula, however the the manual process of doing the same is hefty. Suppose there is a file in following format:
| Emp Code | Emp Name | Salary Apr-17 | Salary of May-17 | …. | Salary of Mar-19 |
| 111 | Emp1 | 1000/- | 1050/- | …. | 1500/- |
| 222 | |||||
| 333 | |||||
| … | |||||
| 999 |
And the desired format of sheet is as shown below:
| Emp Code | Emp Name | Salary Month | Salary |
| 111 | Emp1 | Apr-17 | 1000/- |
| 111 | Emp1 | May-17 | 1050/- |
| … | |||
| 111 | Emp1 | Apr-19 | 1500/- |
| … | |||
| 999 | Emp 999 | Apr-19 | 1250/- |
Use following formula to move rows data to column in Excel:
=IF(MOD(ROW(),42)=0,””,OFFSET($E$1,0,MOD(ROW()-1,42),1,1))
Input formula =IF(MOD(ROW(),42)=0, “”, OFFSET($E$2,0,MOD(ROW()-1,42),1,1))
Use formula =IF(MOD(ROW(),42)=0, “”, OFFSET($A$4,INT((ROW()-1)/42),0,1,1))
=IF(MOD(ROW(),42)=0,””,OFFSET($A$4,INT((ROW()-1)/42),1,1,1))
=IF(MOD(ROW(),42)=0,””,OFFSET($E$4,INT((ROW()-1)/42),MOD(ROW()-1,42),1,1))
Read- How to Copy only Subtotal Rows in Microsoft Excel
You may use ‘comment section’ below for your valuable comments/feedback.