Excel formula to convert single column data into fixed multiple columns, vertical to fixed horizontal

Scenario:

I have data in single column like the data contain in the order of name, age, city in vertical, that I need to convert horizontal like wise for all remaining data

Input

Output

If you have a large amount of data in single row that need to split into three columns, manual copying and pasting will take a long time. You can use a formula to automate the process:

This will split the data in the original column into three columns in the new worksheet. The formulas will reference the original data in the other worksheet, so if the data changes, the split data will update automatically to avoid this issue copy the entire output once and paste with using paste values. 

1. Go to a new worksheet in the same workbook.

2. In cell A1, type the following formula: =INDEX(Sheet1!A:A, (ROW()-1)*3+1)

3. Copy the formula in cell A1 to the range A2:Axx (or the last row of your data).

4. In cell B1, type the following formula: =INDEX(Sheet1!A:A, (ROW()-1)*3+2)

5. Copy the formula in cell B1 to the range B2:B540 (or the last row of your data).

6. In cell C1, type the following formula: =INDEX(Sheet1!A:A, (ROW()-1)*3+3)

7. Copy the formula in cell C1 to the range C2:C540 (or the last row of your data).

This will split the data into three separate columns with unique data in each column.