12

In my first column, I want to autonumber it from 1, 2, 3, 4, ..., x. How do I do that?

10 Answers 10

13

Normally, you just fill A1 in with 1, fill A2 with the formula =A1+1, and then drag the black box on the bottom right of cell A2 down as far as you want to go.

Alternately, you can always just use =ROW() to have to output the row number of the cell.

2
  • 7
    If you use ROW(), anchor it to the first cell with =ROW()-ROW(A$1), so you can move the cells around.
    – Mark
    May 5, 2009 at 10:27
  • =ROW()-ROW(A$1) is good, because it handles row deletions well.
    – mg1075
    Jul 25, 2012 at 21:17
8

For most sequences, you can do this:

  • Start typing them down the column (like 1, 2, 3 or 2, 4, 6, or Monday, Tuesday, Wednesday)
  • Highlight the portion you have filled in
  • Grab the bottom right corner of the highlighted selection and drag it down and Excel will fill in the rest of the sequence
3

=1+R[-1]C As you can check on the Microsoft Office Online Help, Excel doesn't have any tool for doing that, but you can fill it with the ROW function...

2

use the fill down option under one of the menus

2
  • 1
    What menu, this is too broad.
    – fohrums
    Oct 17, 2018 at 23:10
  • Chill man, 2009 was such a simpler time.
    – lsl
    Oct 22, 2018 at 23:16
0

As you can check on the Microsoft Office Online Help, Excel doesn't have any tool for doing that, but you can fill it with the ROW function...

Quoting:

  • Select the first cell in the range that you want to fill.
  • Type the starting value for the series.
  • Type a value in the next cell to establish a pattern.

For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4.

  • Select the cells that contain the starting values.
  • Drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.)
  • Selected cell with fill handle across the range that you want to fill.

To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left.

0

In the first row type 1, in the second row put this formula:

=IF(B2>0,SUM(A2+1),"")

and drag all the way down in the sheet.

0

The simplest solution is to use the tables feature of excel - it provides you a column with implicit (calculated) formula which is expanded to all new rows in such table.

E.g. if you'd like to create autonumber in this format:

1/2/.../187/...

Insert this formula in the column of the table where autonumber should be placed:

=ROW()-ROW(Table1[[#Headers];[AutoNum]])

(replace table name with yours - here "Table1", and change reference to name of the autonumber column name - here "AutoNum")

If you'd like to create text autoID in this format:

"RowID_001"/"RowID_002"/.../"RowID_187"/...

Insert this formula in the column of the table where autoID should be placed:

=CONCATENATE("RowID_";TEXT(ROW()-ROW(Table1[[#Headers];[AutoNum]]);"000"))

(the format of number within autoID is defined by the string in the TEXT formula )

0

Suppose you table starts at A5 (the header), then put in A6 this formula: ROW()-ROW($A$5)

This way it increments the number as you increase your table (by tabbing) and works when you delete a row.

0

Select the cell where you want to start and enter your starting value (here 1). Reselect that cell, HOME > Editing - Fill, Series..., select Columns (in your case) for Series in, and Linear (in your case) for Type. Choose your step value: (in your case 1) and your Stop value: (your value for n - I chose 10), click OK:

SU130274 example

0

The Most Wonderful way to do this just fill the number in your column and in the next column do this column above+1 and then use the fill series button on the home right corner of the excel sheet and it will autofill the whole column. For eg: What i Did was I Put the number in A1 column as 100 and In A2 column I did apply the formula that is =A1+1 and then press enter so value comes here as 101 and then select A2 Cell then use the fill Option where you get the option fill down the series. and Hence I got all the numbers in increasing order. After you have got the numbering then copy the whole A column where you have got the value then again then Use right click option to use Paste special as values. So formula will be gone and you will get your int values In there.

1
  • Please format your answer instead of writing just a single large passage, Screenshots, bullets, numbering would be helpful. a better formatted question gets up votes
    – SeanClt
    Mar 24, 2016 at 23:10

You must log in to answer this question.