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.
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.
How to AutoFill Ascending Numbers in Excel: 5 Quick Ways
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.
Read More:Â How to Use Autofill Formula in Excel
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 Repeat Formula Pattern in Excel
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.
Similar Readings:
- AutoFill Cell Based on Another Cell in Excel
- How to AutoFill from List in Excel
- How to Create a Custom AutoFill List in Excel
- How to Fill Down Blanks in Excel
- How to Apply AutoFill Shortcut in Excel
- How to AutoFill Sequential Letters in Excel
- How to Auto Number Cells in Excel
- How to AutoFill Numbers in Excel
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.
Similar Readings:
- How to Auto Number or Renumber after Filter in Excel
- How to Autofill Numbers in Excel Without Dragging
- How to Autofill Dates in ExcelÂ
- How to AutoFill Numbers in Excel with Filter
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.
Read More: How to Enter Sequential Dates Across Multiple Sheets in Excel
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.
Similar Readings
- How to Auto Generate Number Sequence in Excel
- How to Add Sequence Number by Group in Excel
- How to Repeat Number Pattern in Excel
- Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
- How to Autofill Dates in Excel Without Dragging
- How to Autofill Days of Week Based on Date in Excel
- How to AutoFill Months in Excel
- How to Create Automatic Rolling Months in Excel
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.
Read more: How to Increment Month by 1 in Excel
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.
Similar Readings:
- How to Autofill a Column in Excel
- How to Fill Column in Excel with Same Value
- How to Fill Down to Last Row with Data in Excel
- How to AutoFill Formula When Inserting Rows in Excel
- Filling a Certain Number of Rows in Excel Automatically
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.
Similar Readings:
- How to Auto Populate from Another Worksheet in Excel
- How to Perform Predictive AutoFill in Excel
- Applications of Excel Fill Series
- [Fix] Excel Fill Series Not Working
- Fix: Excel Autofill Not Working
Download Practice Book
Download the practice book here.
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.
Related Articles
- How to Turn Off AutoFill in Excel
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Solved:] Excel Double Click AutoFill Not Working
- Excel VBA: Autofill Method of Range Class Failed
- How to Use VBA AutoFill in Excel
- AutoFill Formula to Last Row with Excel VBA
- [Fixed!] AutoFill Formula Is Not Working in Excel Table
- Drag Number Increase Not Working in Excel