Fill cells with a repetitive pattern in excel

I have cells like 1,1,1,2,2,2. How do I use fill handle to continue to fill with 3,3,3,4,4,4,5,5,5 and so on? I have tried the fill series but it doesn't seem to work.

2 Answers

in A1 enter:

=ROUNDUP(ROW()/3,0)

and copy down. For example:

There are other formulas to generate a variety of repeating patterns.

EDIT#1:

To get a repeating pattern of:

1
2
3
1
2
3

use:

=MOD(ROW()-1,3)+1

and copy down.

To start with 0 rather then 1, just subtract the last 1 in the formula.

It's actually fairly easy when you think about it.

1. In A1, type 1.
2. In A2 type =A1.
3. In A3 type =A1.
4. Select the three of them and drag down.

It will get the desired result.