Tag: MS Excel

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.

Check Full List of Articles & Post – Word & Excel

• How to Move Rows Data to Column in Excel
• How to Copy only Subtotal Rows in Microsoft Excel

Many times while working in Excel, it is necessary required to copy only subtotal rows in Microsoft Excel as shown in below picture.

Following are the way to copy only subtotal rows in Excel.

Read – How to Move Rows Data to Column in Excel

Hide the Detail Rows i.e. Show only Sub-total Rows as below:

Select “Go-To” or <Ctrl> + <G>. Select “Special”.

Select visible fields only.

Yes, you have done. Now copy and paste the data.

You may use ‘comment section’ below for your valuable comments/feedback.

Check Full List of Articles & Post – Word & Excel

• How to Move Rows Data to Column in Excel
• How to Copy only Subtotal Rows in Microsoft Excel