Many times, we have need when we want to generate a sequence of numbers for various purpose. When I started Excel, I simply used to put 1 in A1 and =A1+1 in A2 and dragged down to required number of rows. I, sometimes, still do it. Just old habits die hard. Over a period of time, I did learn many other ways which I would like to share here. Here, I will be talking about filling in a column i.e. vertically. Utilizing same line of logic, horizontal sequential numbers can be generated. Also, if your starting number is not 1 but some other number, you will have to utilize same line of logic to do it.
Once again, I would like to tackle this topic in 4 ways.
1. Formula Way
2. Manual Way
3. Hybrid Way (Combining both Formula and Manual ways)
4. VBA
1. Formula Way
1.1 Easiest and Most Popular Way – Putting 1 in A1 and Putting =A1+1 in A2 and dragging down
This is the easiest and most common way to accomplish the task. Starting can be done in any cell. Say, you put the value 1 in G10. In G11, the formula would be =G10+1 and can be dragged down.
Now, delete a row test – All values get filled in with #REF after the row which got deleted. Test Failed.
Now, add a row test – After adding a row, the formula from previous cell is dragged to the new cell. Now, this new cell and succeeding cell has the same value. Hence, no longer a sequence. Test Failed.
1.2 ROWS($1:1) – Put following formula in a cell and drag down
= ROWS($1:1)
Now, delete a row test –
Case 1: The formula was put in row 1 and dragged down. The sequence updates automatically after deletion of a row. Test Passed.
Case 2: The formula was not put in row 1 but in some other row and dragged down. The sequence sometimes updates automatically, sometimes deletes that particular sequence and sometimes creates duplicate sequence for deleted one. Test Failed.
Now, add a row test –
Case 1: The formula was put in row 1 and dragged down. After insertion of a row, the formula from previous cell is copied to the new cell. The right sequence gets populated. Test Passed.
Case 2: The formula was not put in row 1 but in some other row and dragged down. After adding a row, the formula from previous cell is dragged to the new cell. Now, this new cell and succeeding cell has the same value. Hence, no longer a sequence. Test Failed.
1.3 ROW(1:1) – Put following formula in a cell and drag down
= ROW(1:1)
Now, delete a row test –
The behaviour is exactly the same as in 1.2. But in case 2, sometimes, it gives #REF error.
Now, add a row test –
The behaviour is exactly the same as in 1.2.
1.4 ROW() – Best Way – Put following formula in a cell and drag down
=row()-previous row number
Hence, in A1, it would become =ROW()-0 or Simply =ROW()
In C7, it would become = ROW()-6
Now, delete a row test –
Case 1: The formula was put in row 1 and dragged down. The sequence updates automatically after deletion of a row. Test Passed.
Case 2: The formula was not put in row 1 but in some other row and dragged down. The sequence updates automatically after deletion of a row. Test Passed.
Now, add a row test
Case 1: The formula was put in row 1 and dragged down. After insertion of a row, the formula from previous cell is copied to the new cell. The right sequence gets populated. Test Passed.
Case 2: The formula was not put in row 1 but in some other row and dragged down. After adding a row, the formula from previous cell is dragged to the new cell. The right sequence gets populated. Test Passed.
Note – This is the best formula as it passes both additional and deletion of a row. And also, VERY EASY TO REMEMBER.
1.5 Use of INDIRECT –
1. Put 1 in a cell. Let's say it is A1
2. In below cell, put following formula and drag down
=1+INDIRECT("A"&ROWS($1:1))
If 1 was put in any other cell, formula will have to be readjusted accordingly so that it refers to the previous cell. Let's say 1 was put in E8. Then formula in E9 would be which can be dragged down –
=1+INDIRECT("E"&ROWS($1:8))
Now, delete a row test –
Case 1: The formula was put in row 1 and dragged down. The sequence updates automatically. Test Passed.
Case 2: The formula was not put in row 1 but in some other row and dragged down. The sequence updates automatically. Test Passed.
Now, add a row test –
Case 1: The formula was put in row 1 and dragged down. After insertion of a row, the formula from previous cell is copied to the new cell. Now, this new cell and succeeding cell has the same value. Hence, no longer a sequence. Test Failed.
Case 2: The formula was not put in row 1 but in some other row and dragged down. After adding a row, the formula from previous cell is dragged to the new cell. Now, this new cell and succeeding cell has the same value. Hence, no longer a sequence. Test Failed.
2. Manual Way
Note – Manual way generates the sequence one time. Hence, if you delete a row that particular sequence number will go away. Hence, Delete a row test is not a good test for this. If you add a row, you create a blank value. We will have to live with this limitation.
2.1. Drag Down 1
1. Put 1 in a cell.
2. Drag down to required number of rows.
3. When you leave the fill handle, you get Auto Fill Options Box where you can select the down pointer next to + and you can select Fill Series
2.2. Drag Down 1 with CTRL pressed
1. Put 1 in a cell.
2. CTRL+Drag down to required number of rows.
3. Sequential numbers will be filled in.
2.3. Drag Down 1 & 2
1. Put 1 in a cell and 2 below that.
2. Select both the cells and drag down to required number of rows.
3. Sequential numbers will be filled in.
2.4. Drag Down 1 by Holding Right Mouse Buttong
1. Put 1 in a cell.
2. Drag down to required number of rows by holding right mouse button.
3. When you leave mouse, following will pop up where Fill Series can be selected
2.5 Fill Way – Best way to generate very large sequence
1. Put the start value say 1 in a cell and select this cell.
2. Home tab > Fill > Series
3. Select Columns if you want to fill Series in Column which most likely be.
4. Put a Stop Value i.e. where you want it to stop. Leave Step Value as 1 as you want to sequentially increment.
5. Now, this will fill in your column with 1 to 100.
Note – I suggest that you experiment with this Series as it provides many good features.
3. Hybrid Way (Combining both Formula and Manual ways)
1. Generate the sequence in manual way.
2. Put the following formula assuming the first value was in A1 and sequence was filled till A16.
=RANK(A1,$A$1:$A$16,1)
3. The series which you generate out of step 2 is a sequential series.
Now, add a row test –
It passes both Case 1 and Case 2 of Delete a Row Test.
Now, add a row test –
When formula is copied from previous to new cell #N/A is populated in new cell for both Case 1 and Case 2. Test Failed.
4. VBA Way
VBA code way is always limitless. This always depends upon what you want to achieve and how to achieve. For example, utilizing VBA, you can insert formulas from Formula Way or can generate static sequence. You can write macros and can use this repeatedly.
We demonstrated that =ROW() is the best way to generate the sequence. Below, is a macro which can be used for this purpose to fill in from A1:A20. (You can modify this accordingly)
Sub SequentialSeries()
Range("A1:A20").Value = "=row()"
End Sub
Note – If range is something else for example D5:D100, then Range("D5:D100").Value = "=row()-4" can be put in place of Range("A1:A20").Value = "=row()"
This was explained in 1.4.