How do you create duplicate cells quickly?

Hi,

How do I duplicate the values to say 4 times in a column?

So I have in a column,

dog
cat
bird
fish

I want to duplicate the above to look like this below,

dog
dog
dog
dog
cat
cat
cat
cat
bird
bird
bird
bird
fish
fish
fish
fish

I'll need to do this for 500 unique values 12 times each, so is there a formula to do this easily?

Thanks in advance!

worked like a charm. Thanks!

By the way, is there a quick way to send the formula down really far past down the A original column so that I don't have to drag the results manually for a long time?

Hi kooldis

How far down do you want to go? How will you communicate this to Excel?

Assuming that the formula is in column B and you want it to go down to row 1001, in the box just above the column header A B C etc type in B1001 and hit enter. This will take you to B1001. Hold down Ctrl+Shift and press the up arrow. Hold down Ctrl and press d. The formula will be copied down from your entry to B1001.

Hope this helps

Alastair

I've been using this formula ' =INDEX(A:A,ROUNDUP(ROW(A1)/4,0)) ' but never actually understood how it worked. I understand the row function gives the reference number of a cell, but why is it that when you divide it by a number like 4 and do a roundup function, it will return 4 duplicates of the word result I want? It works for every number no matter if I put 4 or 22 in red. How is that possible? I imagine that should be a number where the division doesn't work out and you don't get the number of duplicates you want.