how to divide columns into new rows

Hello ankitaaq and Welcome to Excel Forum.
Here is how this might be done for 5 rows of data using formulas.
1. As modeled on Sheet1, move the data over one column and down one row
2. Use the following to populate B1 and over: =IF(MOD(COLUMNS($B1:B1)-1,200)=0,(COLUMNS($B1:B1)-1)/200,A1)
3. Use the following to populate A2 and down: =SUM(A1,1)
On Sheet2:
1. Populate A2 and down using: =IF(MOD(ROWS(A$2:A2)-1,5)=0,(ROWS(A$2:A2)-1)/5,A1)
2. [Optional but helpful] Populate B1 and over using the method for filling a series shown in the video starting at about the 1:30 mark.
https://www.youtube.com/watch?v=CePq6bnC5oM
Note that this is only used to find the last column for copying the formula step 3, the values are not used in calculation.
3. Populate B2:GS41 using:

Formula:

Please Login or Register  to view this content.

Note that once the data is arranged on Sheet2 both column A and row 1 may be deleted.
For future reference, you will usually get quicker responses if you include a sample file (instructions are in the HOW TO ATTACH YOUR SAMPLE WORKBOOK banner at the top of the page).
Let us know if you have any questions.