Excel: how to duplicate every row into the same column?
Matthew Harrington 
Given a column like this in an Excel spreadsheet
aaa
bbb
ccc
...How can I produce another column that looks like this (not manually copying and pasting of course since I have thousands of entries in the original column)
aaa
aaa
bbb
bbb
ccc
ccc
...4 Answers
With data in column A, in B1 enter:
=INDEX(A:A,ROUNDUP(ROWS($1:1)/2,0))and copy downward.
Notice you get two copies of each item. If you use 3 in the formula, you would get three copies of each item.
If you want to keep the original order of the data:
- Write "1" into the very first row of a new column (for example cell B1) and "2" into the cell below it
- Select these two cells and click&drag the green square at the bottom right further downwards. This will fill the entire column with 1, 2, 3, ...
- Select all your data, including the new column
- Copy it by pressing Ctrl+C
- Go to the first cell below your data
- Paste your data by pressing Ctrl+V
- Select everything, including the new column, for example by pressing Ctrl+A
- Press Tab until the new column has a cell highlighted in white
- Use Excel's sort button
- Delete the column that contains 1, 1, 2, 2, 3, 3, ...
Before sorting, you'll have:
aaa 1
bbb 2
ccc 3
... ...
aaa 1
bbb 2
ccc 3
... ...After sorting (before deleting the new column), you'll have:
aaa 1
aaa 1
bbb 2
bbb 2
ccc 3
ccc 3
... ...Power Query can also get the result: Select the Range- go to Data- From Sheet- open Power Query editor- select the column and right click Duplicate Column- select 2 columns and go to Transform- select Unpivot Columns- Remove Attribute column- Close and load to..:
If having your data sorted is OK:
- Select all your data
- Copy it by pressing Ctrl+C
- Go to the cell below your data
- Paste your data by pressing Ctrl+V
- Select everything (the original and the copied data), for example by pressing Ctrl+A
- Use Excel's sort button
Before sorting, you'll have:
aaa
bbb
ccc
...
aaa
bbb
ccc
...After sorting, you'll have:
aaa
aaa
bbb
bbb
ccc
ccc
...
