32

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

1
2
3
4
5
etc.

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

I can do it easily until the end of the sheet, but then I have to scroll down to the 20,000th row, which is time-consuming, and clear the contents of all rows beneath it. Because I'm not looking to fill to the end of the sheet, but to the 20,000th row specifically.

ktm5124
  • 2,037

5 Answers5

38

Use the Goto command to go to row 20,000 and enter some text in the column that you want to fill.

Go to the top, enter the first 2 numbers.

Select the two cells. Use Ctrl-Shift-Down Arrow to select all cells down to row 20,000. Click Home ribbon > Fill drop-down > Series > OK

teylyn
  • 23,615
18
  1. Fill in the value for your first cell.
  2. RIGHT click the fill handle, pull down and then back up.
  3. In the menu box that pops up click "Series".
  4. In the message box be sure to check "Columns".
  5. Assuming that the "Step value" is "1" enter 20000 in the "Stop value:" window.
  6. Click "OK".
Clif
  • 596
4

Inspired by this answer,

  • Click in the Name Box (to the left of the Formula Bar) and type a range that covers the cells that you want to fill.  For example, if you are in column Q and you have a header row, type Q2:Q20001.  Type Enter.  The cells will be selected.
  • Click in the Formula Bar and type ROW()-1.  (Replace the 1 with the number of header rows you are skipping.)  Type Ctrl+Enter.  This will fill the cells with the (apparent) values 1 through 20000.

If this is good enough for you, quit.  Otherwise,

  • Copy (e.g., by typing Ctrl+C).
  • Click on the Paste menu, and select “Paste Values”.
Community
  • 1
G-Man Says 'Reinstate Monica'
  • 8,029
1

Following procedure is I think the easy one to do the auto-filling large number of cells, no mouse required :)

step-1: Fill 2 or more cells with the required pattern (maybe with some formula), select and drag (few empty cells) to check if auto-filling is working as per your requirement.

step-2: Select the first cell of your intended sequence (already filled one).

step-3: Press 'F5' (a 'Go To' window will appear), and type the last cell number till where you need to fill (for ex; A20000). Now, do NOT press enter alone, but press Shift+Enter to select all the cells from your first cell.

step-4: Press 'Ctrl+D', all the cells selected will be filled accordingly.

Tip: This procedure can easily be extended to multiple columns; each with different pattern or formula. Select the first filled cell of the leftmost column in step-2, then, select the last intended cell of the rightmost column in step-3. All columns will be auto-filled at once by pressing 'Ctrl+D'. This is a very useful shortcut if you use excel a lot.

Please let me know if there is an issue.

Attoti
  • 11
-1
  1. Type 1 in the first row.
  2. Type = in the second row and select the first row value.
  3. Type +1 in the second row (it automatically displays 2), e.g. =A770+1.
  4. Copy (Ctrl+C) the second row column value.
  5. Select the entire column and paste (Ctrl+V).
Kamil Maciorowski
  • 81,893
sagar jadhav
  • 1