# Convert one column into a new column every 10 rows in Excel

I have a set of 110 numbers in Excel. I want to split this into 11 columns of 10 rows each.

So, for column:

``````324
292
300
120
276
320
984
136
300
280
69
289
266
292
266
494
315
118
295
289
``````

I want the output to be:

``````324 69
292 289
300 266
120 292
276 266
320 494
984 315
136 118
300 295
280 289
``````

What I've tried so far

1. Assigned `=A11` in B1, `=A21` in C1, `=A31` in D1 and then used the autofill. However, this gives the formula `=D11` in E1 and hence doesn't work.

2. Using Text to Columns wizard. This only provides for splitting of data for delimiter and there is now way of splitting based on number of rows.

The most similar question I've found is here where the goal is the opposite of mine (combining multiple columns into a single one).

This and this solve a similar problem but they copy the data row wise.

This question attempts the same problem in R.

I know this can be solved using a library like pandas, but I think there should be an easy way of doing it in Excel too.

• How are your VBA skills? You could start at the top of the column and run a macro to read the cell contents into an array (use ActiveCell.Offset(1, 0).Select to read the next cell). Then write another macro to write the contents back, using nested loops to offset by 1,0 - and then by -10,1 every tenth loop. – Charles Burge Feb 19 '19 at 2:58
• @Lee I have referenced another solution using R in my question. I know we can easily script to handle this situation. I am looking for something that works out of the box with Excel. – BenKenobi007 Feb 19 '19 at 15:39
• @CharlesBurge I am still not very experienced with excel. Given that I've coded with c# before, how difficult is VBA ? – BenKenobi007 Feb 19 '19 at 15:41
• VBA is very different from C#, but I think most people would consider VBA to be considerably easier. – Charles Burge Feb 19 '19 at 16:32

There are several ways of doing this. A previous answer by p.phidot used OFFSET. The OFFSET functions works, but is classified as a volatile functions. This is not the end of the world, it just means it recalculates every time something on the spreadsheet changes. Regular formulas only recalculate when something that affects them changes. As a non volatile alternative, I suggest the use of the INDEX function below. Copy it down 10 rows then copy to the right as far as you need to go.

``````=INDEX(\$A:\$A,(COLUMN(A1)-1)*10+ROW(A1))
``````

Assuming the 324 is located in A1, put this in B1 :

`=OFFSET(\$A\$1,(COLUMN()-1)*10+ROW()-1,0)`

and drag it downward & right-wards.

idea : use the destination row and column to feed the offset from 1st value.

• shouldn't it be `=OFFSET(\$A\$1,(COLUMN()-` 2 `)*10+ROW()-1,0,1,1)`? – Forward Ed Feb 19 '19 at 4:14
• Sure. In my case, I reuse the original list for the 1st column of the data and yours "re-produce" it as a separate column. [ An acceptable good answer too. ] /(^_^) – p._phidot_ Feb 19 '19 at 7:32
• I missed that tidbit. I actually saw it but did not understand at the time. Good job! – Forward Ed Feb 19 '19 at 11:55

Your problem can be solved by using few Helper values:

How it works:

• I'm assuming that your data are in Range A2:A41.
• Write `0` in `D1`, `10` in `E1` & `20` in `F1`.

N.B.

• `D1`, `E1` & `F1` are Helper Cells.

• `30` & `40` should value in `G1` & `H1`, if you need further Rows to Copy.

• Write this Formula in D2 & Fill Down then Right.

`=IF(ROW(D2)>11,"",INDEX(\$A:\$A,COLUMN(\$A\$1)+ROW(\$A10)+D\$1))`

• Adjust cell references in the Formula as needed.