29

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.

2

5 Answers 5

34

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

5
  • 4
    Awesome answer! Only comment is, that on OS X (which I use) the keyboard shortcut is Cmd-Shift-Down Arrow.
    – ktm5124
    Nov 18, 2014 at 22:07
  • I might add that Ctrl-Shift-Down Arrow seems to select all cells from the active one to the first one with "data" in it. Which seems to be why you have to enter random text in the 20,000th row. Also when clicking "fill" you can pick the increment. I wanted to increment by 5, so tried filling the first two cells with 5 and 10 respectively. Excel wasn't smart enough to realize that was the increment I wanted. So I just put my initial 5 in the first cell and set the "step value" to 5. This worked.
    – Rohn Adams
    Feb 4, 2016 at 2:47
  • For Excel 2016 after selecting 'Series', I select 'AutoFill' for the Type and click OK and it works as expected for me.
    – bvh
    Dec 21, 2022 at 21:39
  • @bvh Yes. That is the method used by Clif in the other answer.
    – teylyn
    Dec 21, 2022 at 23:21
  • Fill drop-down is in Editing section. Had to post this cause I took sometime to find it. Jan 9, 2023 at 10:51
16
  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".
3

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”.
1
  • Perfect! I was looking for something that would auto update if a row is added or removed, which this does. Oct 22, 2020 at 16:16
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.

-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).
1
  • 2
    You didn't understand the question. Read it again.
    – teylyn
    Jun 5, 2018 at 10:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .