How to Split 1 Row into Multiple Columns in Microsoft Excel 2010

In this article, you will learn how to split 1 row into multiple columns.

If we have a row that contains values & we require copy them into multiple columns meeting the criteria.

We will use a combination of 4 functions i.e. INDEX, MOD, ROWS, COLUMNS



Syntax: =INDEX(array,row_num,column_num)

array: Array is a range of cells or table.

row_num: It is the row number in the array from which to return the value.

column_num: it is optional. It is the column number in the array which is used to return the value.


MOD: Returns the remainder after a number is divided by a divisor.

Syntax =MOD(number,divisor)

number: It is a numeric  value whose remainder you want to find.

divisor: It is the number which is used to divide the number argument.


Rows: It returns the number of rows in a reference or array

Syntax: =(array)

array: Array is a reference to a range of cells or an array.


Columns: Returns the number of columns in a reference or array

Syntax: =(array)



Let us understand with an example:

Row# 1 contains numbers such as 1,2,3 & so on till 20

Following is a snapshot of the required output
The criterion is range A3:A7 should contain value stored in cell A1:E1.

After every 5 cells in the first row; the next 5 values (F1:J1) need to be stored in the range B3:B7 & so on.

The formula in cell A3 would be =INDEX($1:$1,,1+MOD(ROWS($F$3:F3)-1,5)+5*(COLUMNS($E$3:E$3)-1))

Copy down & across the formula, we will get the desired result.


In this way, you can get the values in multiple columns from one row meeting criteria.




Users are saying about us...

  1. I’m trying to use this formula/logic to do almost the same thing, but with a long column instead of a long row. In my case I want to make every 15th line a new column where the (numbers, in this case) will continue at the top of each column.

    What I cannot for the life of me figure out is what, in your example, does “ROWS($F$3:F3)” refer to? Why would you need those values? Do you just need some value in order to do the math, and it could be any cell as a starting point? (i.e. “ROWS($D$27:D27)”)

Leave a Reply



Terms and Conditions of use

