How to AutoFill Ascending Numbers in Excel (5 Quick Ways)

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 Handlefeature 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.

AutoFill Ascending Numbers Using Fill Handle in Excel

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.

AutoFill Ascending Numbers Using Fill Handle in Excel

  • 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.

AutoFill Ascending Numbers Using Fill Handle in Excel

  • Instantly, the rest of the cells will be filled ascendingly like below.

AutoFill Ascending Numbers Using Fill Handle in Excel

  • 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.

AutoFill Ascending Numbers Using Fill Handle in Excel

  • Finally, use the Fill Handle to see results like below.

AutoFill Ascending Numbers Using Fill Handle in Excel

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.

Fill Command to AutoFill Ascending Numbers

  • Secondly, go to the Home tab and select Fill.
  • Then, select Series from the dropdown menu.

Fill Command to AutoFill Ascending Numbers

  • The ‘Series’ window will occur. Click OK to proceed.

Fill Command to AutoFill Ascending Numbers

  • After that, you will get ascending series numbers in the empty cells.

Fill Command to AutoFill Ascending Numbers

  • Now, if you want to change the step value, you can change it from the ‘Series’ window.

Fill Command to AutoFill Ascending Numbers

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.

Fill Command to AutoFill Ascending Numbers

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.

Fill Ascending Numbers by Skipping a Row in Excel

  • Then, drag the Fill Handle down.

Fill Ascending Numbers by Skipping a Row in Excel

  • You will see the below results after using the Fill Handle.

Fill Ascending Numbers by Skipping a Row in Excel

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()

AutoFill Ascending Numbers with Formulas

  • Hit Enter to see the result.

AutoFill Ascending Numbers with Formulas

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.

AutoFill Ascending Numbers with Formulas

  • Now, if you want to start numbering from 1, then type:
=ROW(B1)

AutoFill Ascending Numbers with Formulas

Here, the ROW Function displays the row number of Cell B1.

  • Hit Enter to see the result.

AutoFill Ascending Numbers with Formulas

  • Finally, use the Fill Handle to see results automatically.

AutoFill Ascending Numbers with Formulas


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

AutoFill Ascending Numbers with Formulas

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.

AutoFill Ascending Numbers with Formulas

  • Finally, use the Fill Handle to see ascending numbers.

AutoFill Ascending Numbers with Formulas

  • Again, if you want to start numbering from 5, then, add 5 instead of 1 in the previous formula.
=OFFSET(B6,-1,0)+5

AutoFill Ascending Numbers with Formulas

  • Press Enter and drag down the Fill Handle to see the results.

AutoFill Ascending Numbers with Formulas

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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo