Split Data in One Column to Multiple Columns

This post will guide you how to split data in a single column into multiple columns with a formula in Excel. How do I transpose data from one column to multiple columns in Excel. How can I make one long column into multiple columns in Excel.

Split Data in One Column to Multiple Columns


Assuming that you have 5 sets of product’s list in one column, and you would like to convert each set in its own column or split each sets into its own staked columns. How to achieve it.

If you want to split each sets in one column from row to row in stacked columns, and you need to create a formula based on the OFFSET function, the COLUMNS function, and the ROWS function.

Just like this:

=OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*2,0)

Type this formula into a blank cell C1, and press Enter key in your keyboard, and then drag the AutoFill Handle from C1 to D1. Select the Cell C1 and D1, then drag the AutoFill Handle in Cell D1 from D1 to D5.

split data in one column to multiple11

split data in one column to multiple1

If you want to split each sets in one column from one column into another column, you just need to use the following formula based on the INDEX function, the ROW function and the COLUMNS function. Like this:

=INDEX($A$1:$A$10,ROW(C1)+(2*(COLUMNS($C$1:C$1)-1)))

Type this formula into the formula box of the Cell C1, and press Enter key in your keyboard, and drag the AutoFill Handle from C1 to C2. Select the Cell C1 and C2, and then drag the AutoFill handle in Cell C2 from C2 to G2.

split data in one column to multiple3

split data in one column to multiple4

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel ROWS function
    The Excel ROWS function returns the number of rows in a cell reference.The ROWS function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROWS function is as below:= ROWS(array)….
  • Excel COLUMNS function
    The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)….
Related Posts

Extract or Filter Every Nth Row
filter every nth row1

Assume that you're in a situation where you need to filter every nth row from a list having a few values. I'm pretty sure you'd choose to do it manually, which is also a great choice when you only have ...

How to Create Dynamical Drop-Down List and Sort by Alphabetical Order in Excel
How to Create Dynamical Drop-Down List 14

In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ ...

How to Highlight Every Other Row or Every Nth Row in Excel?
How to Highlight Every Other Row or Every Nth Row in Excel 11

Sometimes we may want to highlight every other row or every Nth row in a spreadsheet to make data in different rows looks more clearly. Though we can apply table styles to highlight table rows automatically, we can also highlight ...

How to Count Duplicate Values Only Once in A Range in Excel?
How to Count Duplicate Values Only Once in A Range in Excel5

When counting the number of times for objects appear in a list or a range, we usually record the duplicate value only once and ignore the redundant ones. We cannot apply formula with only one function to implement this in ...

How to Create Dynamic Drop Down List without Blank in Excel
create dynamic drop down list with blank5

This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel. In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be ...

How to Stack Data from Multiple Columns into One Column in Excel
Stack Data 12

In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function. This time if we want to stack data from multiple columns to one column, how can ...

How to Count Only Unique Values Excluding Duplicates in Excel
Count Only Unique Values Excluding Duplicates 6

We enter a list of numbers or products and there are some duplicates in the list, if we want to just do count for the unique values and exclude the duplicates, how can we do? Now you can follow the ...

How To Transpose Every N Rows of Data into Muliptle Columns in Excel
How to Transpose every N rows from one column to multiple2

This post will guide you how to transpose data from rows to column with a formula in Excel. How do I transpose every N rows from one column to multiple columns in Excel. Assuming that you have a list of ...

How To Increment Cell Reference by X Rows in Excel
increase cell reference by x rows2

This post will guide you how to increment cell reference by x Rows when you drag a formula to populate cells in a column in Excel. By default, when you drag the AutoFill Handle down to other cells , the ...

How to Get Row Number From a Vlookup in Excel
get row number from vlookup2

This post will guide you how to get row number from a Vlookup in Excel. How do I return a row number from a Vlookup with a formula in Excel. Assuming that you have a list of data in range ...

Sidebar