How do you number a column 1, 2, 1, 2... in Excel?

victor-r

Hello. I'm relatively new to Excel. I work in research and I want to cut my data points in half. I figure I can create a column numbering each subsequent row 1, 2, 1, 2 and so on, then reordering the column to only take the number 1 data points. I am aware I can autofill by dragging, but it is a little hard when you have over 3000 data points. Is there an easier way? Thank you in advance!

siti-vi

Depending on RowNumber (Odd or Even Row number), use this formula to a cell

=MOD(ROW() + 1, 2) + 1

or

=MOD(ROW(), 2) + 1

and copy down

greg-g

Hi Victor,

in A1: =ROW($A$1)

in A2: =ROW($A$2)

now copy both cells, click cell A3 and SHIFT + Page Down as far down as you want, then paste.

puaka

You don't have to drag your formulas all the way down. Go to any filled column. Click End Key, Arrow Down, Now click to the column 1,2,1,2, hold shift key, click end key, arrow up. Fill down your formulas.