Excel auto fill a column with incremental sequence

Question

I would like a column that increments by 1 for each sequence, like this:

1

1

1

2

2

2

3

3

3

etc.

I would like to do this for 20,000 rows. What's the easiest way to auto-fill this column on Excel ?

Answer 1

Assuming your sequence starts in Row 1, this will generate the pattern you want when filled down:

=1+TRUNC((ROW()-1)/3)

Here, ROW() returns the row number of the current row. When the row number is 1,2 or 3 the result inside the TRUNC() function is less than 1. So truncating the result gives zero. This produces 1 for rows 1,2 and 3.

When the row number is 4,5 or 6 the result inside the TRUNC() function is greater than 1 and less than 2. So truncating the result gives 1, and this produces 2 for rows 4,5 and 6.

And so forth.

Filling down 20,000 rows is best done by selecting the cell with the formula, then SHIFT-clicking on the last cell in that column (20k rows down).. Then choose "Fill Down..." from the Data menu.

Good luck, and I hope this helps.