Mursalin Ibne Salehin
We need to AutoFill Ascending Numbers in our Excel worksheet very often. In this article, we will show how you can autofill ascending numbers easily in excel. There are certain cases when we need to fill ascending numbers automatically in excel dataset. We will demonstrate the autofill process here.
Download Practice Book
Download the practice book here.
What are Ascending Numbers?
In simple language, we can say the ascending number is the arrangement of numbers from the smallest to the largest. Also ascending means ‘going up’. So, ascending order means the numbers are going up. For example, 4, 7, 11, 23, 45, 75, 101 are in ascending order. So, they are ascending numbers.
5 Ways to AutoFill Ascending Numbers in Excel
1. AutoFill Ascending Numbers Using Fill Handle in Excel
In this method, we will use the ‘Fill Handle’ feature of excel. This is a very quick and easy procedure. To explain this method, we will use a dataset that contains the information of Names, Region & Sales of some sellers. We will try to autofill their ID Number ascendingly. We will use the same dataset in all methods.
Follow the steps below to learn this method.
STEPS:
- In the first place, select the cell from where you want to start numbering and put the first number manually. We have selected Cell B5 and typed 100.
- Secondly, drag down the Fill Handle or just double-click on it. The ‘AutoFill Options’ icon will appear.
- Select the AutoFill Options icon and select Fill Series from the drop-down menu.
- Instantly, the rest of the cells will be filled ascendingly like below.
- Again, if you want to maintain a step value, type the first two values and select them. We have typed 100 and 105 in Cell B5 & B6.
- Finally, use the Fill Handle to see results like below.
Related Content: How to Autofill Numbers in Excel without Dragging (5 Quick Methods)
2. Fill Command to AutoFill Ascending Numbers
We can also use the Fill Command option of excel to autofill ascending numbers. Fill Command gives us the opportunity to fill many types of series. So, it is very helpful to autofill ascending numbers.
Pay attention to the steps below.
STEPS:
- Firstly, type the first number and select the cells where you want to enter the ascending numbers.
- Secondly, go to the Home tab and select Fill.
- Then, select Series from the drop–down menu.
- The ‘Series’ window will occur. Click OK to proceed.
- After that, you will get ascending series numbers in the empty cells.
- Now, if you want to change the step value, you can change it from the ‘Series’ window.
Here, we have set the step value at 5. You can also select the stop value & type here. If you need a geometric ascending pattern select ‘Growth’ in the Type field.
- Finally, if you press OK, you will see the below results.
Read More: How to AutoFill Numbers in Excel with Filter (2 Methods)
3. Fill Ascending Numbers by Skipping a Row in Excel
Sometimes, we need to keep a whole row empty after putting values in the previous row. In this method, we will put value in the first row and keep the second row empty. We will maintain this pattern to autofill ascending numbers.
Observe the steps below to know more.
STEPS:
- Put the first number manually in the first cell in the beginning. We have typed 100 in Cell B5.
- Now, select Cell B5 & B6.
- Then, drag the Fill Handle down.
- You will see the below results after using the Fill Handle.
Read More: How to Number Rows Automatically in Excel (8 Methods)
4. AutoFill Ascending Numbers with Formulas
We can use some formulas to number rows or columns ascendingly. These formulas are helpful when you need to number columns and rows in large datasets. We will use the same dataset again in our sub-methods.
4.1 Use of ROW Function
The ROW Function returns the values of a row number for a cell or range. It helps us to autofill ascending numbers in columns.
Follow the steps below for this method.
STEPS:
- Select Cell B5 at first.
- Now type the formula:
=ROW()
- Hit Enter to see the result.
Here, the ROW Function is displaying the row number of the cell where the formula is written.
- Then, use the Fill Handle to see results like below.
- Now, if you want to start numbering from 1, then type:
=ROW(B1)
Here, the ROW Function displays the row number of Cell B1.
- Hit Enter to see the result.
- Finally, use the Fill Handle to see results automatically.
4.2 Use of OFFSET Function
We can use the OFFSET Function for the same purpose. The OFFSET Function returns a range that is a specified number of rows and columns from a reference cell. Follow the below steps to use this function.
STEPS:
- Select Cell B6Â at first.
- Secondly, type the formula:
=OFFSET(B6,-1,O)+1
Here, the OFFSET Function takes the value of the previous cell and adds 1 with it. The value of Cell B5 is 0 because it is empty. Then, it adds 1 with it.
- Press Enter to see the result.
- Finally, use the Fill Handle to see ascending numbers.
- Again, if you want to start numbering from 5, then, add 5 instead of 1 in the previous formula.
=OFFSET(B6,-1,0)+5
- Press Enter and drag down the Fill Handle to see the results.
NOTE: To use this formula, the previous cell where you are writing the formula must be kept empty. In this method, we have used the formula in Cell B6, so Cell B5 was kept empty.
4.3 Use of COUNTA Function
The COUNTA Function counts the number of non-blank cells in a cell range or the cell reference. Here, we will use the COUNTA Function to autofill columns ascendingly.
STEPS:
- At first, select Cell B5 and type the formula:
=COUNTA($C$5:C5)
Here, we have locked Cell C5 using the dollar ($) sign. The COUNTA Function is applied in the range given inside the function.
- Now, press Enter and use the Fill Handle to see results.
- Furthermore, if you want to start from 10, then add 9 in the previous formula.
=COUNTA($C$5:C5)+9
- Finally, hit Enter and use the Fill Handle to see the results.
4.4 Use of SUBTOTAL Function
We can again use the SUBTOTAL Function to create a formula to autofill ascending numbers in a column/row. The SUBTOTAL Function allows us to create groups and then perform various operations like COUNT, SUM, MAX, AVERAGE, and more.
Follow the steps to know more.
STEPS:
- Select Cell B5 and type the formula below.
=SUBTOTAL(3,$C$5:C5)
Here, the 3 inside the SUBTOTAL Function specifies the use of COUNTA and the second argument describes the range where the SUBTOTAL Function is applied.
- Hit Enter and then use the Fill Handle.
- Now, interestingly, if you use Filter and hide some rows, the ascending numbers will be automatically updated.
4.5 Adding 1 to the Previous Row
We can number the rows in a column by simply adding 1 to the previous column. Follow the steps to know this technique.
STEPS:
- Firstly, type a value manually in the first cell. We have typed 1 in Cell B5.
- Now, type the formula:
=B5+1
- Then, press Enter and use the Fill Handle to see the results.
5. Apply Excel Table to AutoFill Ascending Numbers
We can convert our dataset to a table and autofill ascending numbers in a column. It is used to create serial numbers from top to bottom in a column.
We will use the same dataset to explain this method.
STEPS:
- In the first place, select any cell in your dataset.
- Now, go to the Insert tab and select Table.
- Select ‘My table has headers’ from the Create Table window and click OK.
- Then, put the below formula in Cell B5.
=ROW()-ROW(Table2[#Headers])
- Finally, hit Enter to see the result.
- Moreover, if you insert any new row, the ascending numbers will be updated automatically.
Read More: How to Auto Number or Renumber after Filter in Excel (7 Easy Ways)
Conclusion
We have discussed 5 easy and quick ways to autofill ascending numbers in an excel worksheet. I hope these methods will help you to find your solution. You can also download the practice book to exercise these methods. Last of all, if you have suggestions or queries, feel free to ask in the comment section.
