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.
5 Answers
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
- Fill in the value for your first cell.
- RIGHT click the fill handle, pull down and then back up.
- In the menu box that pops up click "Series".
- In the message box be sure to check "Columns".
- Assuming that the "Step value" is "1" enter 20000 in the "Stop value:" window.
- Click "OK".
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 the1with 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”.
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.
- Type
1in the first row. - Type
=in the second row and select the first row value. - Type
+1in the second row (it automatically displays2), e.g.=A770+1. - Copy (Ctrl+C) the second row column value.
- Select the entire column and paste (Ctrl+V).
