How to Auto Generate Number Sequence in Excel (9 Examples) (2023)

Often, serializing a lot of elements manually in Excel is boring. Fortunately, it is pretty easy to auto-generate number sequences in Excel. To get rid of this monotonous issue, I will show you some easy and handy ways to auto-generate a number sequence in Excel. For the purpose of demonstration, I’m using Microsoft Office 365. No worries if you have another version.

Table of Contents hide

Download Practice Workbook

9 Suitable Examples to Auto Generate Number Sequence in Excel

1. Basic Addition Method

2. AutoFill Method to Auto Generate Number Sequence

2.1 Using Fill Handle

2.2 Applying Fill Series to Auto Generate Number Sequence

3. Using ROW Function

4. Applying COUNTA Function Auto Generate Sequence

5. Utilizing OFFSET Function

6. Using SEQUENCE Function to Auto Generate Number Sequence

7. Create a Repeating Number Sequence

8. Create Number Sequence Ignoring Blank Cells

How to Auto Generate Number Sequence with Text in Excel

Conclusion

Further Readings

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Auto Generate Number Sequence.xlsx

9 Suitable Examples to Auto Generate Number Sequence in Excel

First things first, let’s get to know about the worksheet, which is the basis of our examples today. In this sheet, we have a table of 5 people with their ages. Using this, you will see how to auto generate numbers for each of them. Generally, you see the serial number that belongs to the left of any particular element. But as we intend to look at different ways of generating numbers, we are keeping the methods on the right side. For the purpose of demonstration, I have used the following sample dataset.

How to Auto Generate Number Sequence in Excel (9 Examples) (1)

1. Basic Addition Method

In this serialization method, we will increment the previous row number by 1. So, we will add 1 to the previous row number to our current cell.

Steps:

  • Firstly, select the cell D5 and put the value as 1.
  • Secondly, write the following formula on cell D6.

=D5+1

How to Auto Generate Number Sequence in Excel (9 Examples) (2)

  • Thirdly, press the Enter button and use the AutoFill tool to apply the formula to the rest of the column.

How to Auto Generate Number Sequence in Excel (9 Examples) (3)

  • Finally, you will get your desired sequence.

How to Auto Generate Number Sequence in Excel (9 Examples) (4)

2. AutoFill Method to Auto Generate Number Sequence

If you want to use the AutoFill method, you have two options. However, it includes the Fill Handle and Fill Series. Hence, go through the below parts in order to apply them to our data.

2.1 Using Fill Handle

In general, you have to insert a value into a row and select it as an active cell. Then you will see a small circular box at the bottom right to complete the operation.

Steps:

  • Initially, select the active cell D5 and put 1.
  • Similarly, select cell D6 and put 2.
  • After that, select the cells, and you will see that Filler icon at the bottom right.

How to Auto Generate Number Sequence in Excel (9 Examples) (5)

  • Lastly, drag it down in order to complete the operation.

How to Auto Generate Number Sequence in Excel (9 Examples) (6)

2.2 Applying Fill Series to Auto Generate Number Sequence

For the Fill Series method, you don’t need to insert two values as previously. Simply Insert the value into one row, and the rest of the part will be completed automatically.

Steps:

  • At first, select cell D5 and input 1 as the value.
  • Then, go to the Editing command from the Home tab and select Series from the Filloption.

How to Auto Generate Number Sequence in Excel (9 Examples) (7)

  • Here, set Columns under Series and Step value: 1 and Stop value: 5 from the Series Dialog Box.
  • Next, press ok.

How to Auto Generate Number Sequence in Excel (9 Examples) (8)

  • Finally, you will find the sequence.

How to Auto Generate Number Sequence in Excel (9 Examples) (9)

3. Using ROW Function

Usually, The ROW function returns the row number of the cell you are presently in (active cell). So, select an empty row from the first in order to get 1 anywhere in the dataset.

Steps:

  • Firstly, write the following formula in cell D5.

=ROW(A1)

How to Auto Generate Number Sequence in Excel (9 Examples) (10)

  • Finally, use the AutoFill tool to the entire column to get the final result.

How to Auto Generate Number Sequence in Excel (9 Examples) (11)

4. Applying COUNTA Function Auto Generate Sequence

Furthermore, another method worth mentioning here is the COUNTA function. However, the COUNTA function counts the number of cells that are not empty within a range. Moreover, this method will help you serialize the rows that are not empty.

Steps:

  • Initially, write the following formula in cell D5.

=COUNTA($B$5:B5)

How to Auto Generate Number Sequence in Excel (9 Examples) (12)

  • Lastly, hit the Enter key and utilize the AutoFill tool to get the result.

How to Auto Generate Number Sequence in Excel (9 Examples) (13)

5. Utilizing OFFSET Function

Generally, the OFFSET function returns the reference of a cell or a range of cells. Here, you have to insert three parameters within an OFFSET function. However, go through the steps below to complete the task.

Steps:

  • At first, insert the following formula in cell D5.

=OFFSET(E5,-1,0)+1

How to Auto Generate Number Sequence in Excel (9 Examples) (14)

Here, you have to select an empty cell for the first value. Otherwise, it will show errors.

  • Again, select cell D6 and insert the formula below.

=OFFSET(D6,-1,0)+1

How to Auto Generate Number Sequence in Excel (9 Examples) (15)

  • At the end, apply the AutoFill tool, and the output will be as below.

How to Auto Generate Number Sequence in Excel (9 Examples) (16)

Similar Readings

  • How to Show Grand Total in Pivot Table (3 Easy Methods)
  • Use Format Function in Excel (with Suitable Examples)
  • How to Show Percentage in Legend in Excel Pie Chart (with Easy Steps)

6. Using SEQUENCE Function to Auto Generate Number Sequence

Furthermore, you can generate a number sequence using a function called SEQUENCE. Using the SEQUENCE function, you have to provide only one parameter, rows.

Steps:

  • At first, write down the following formula in cell D5.

=SEQUENCE(5)

How to Auto Generate Number Sequence in Excel (9 Examples) (17)

  • Lastly, it will provide the final result.

How to Auto Generate Number Sequence in Excel (9 Examples) (18)

7. Create a Repeating Number Sequence

In this part, I will show you how to automatically create a repeating number sequence. Usually, it will repeat the number after a particular value. Here, I have used the IF function. However, you can follow the steps below to easily complete the task.

Steps:

  • At first, click on cell D5 and put the value as 1.
  • After that, enter the following formula on cell D6 and press Enter.

=IF(D5=3,1,D5+1)

How to Auto Generate Number Sequence in Excel (9 Examples) (19)

  • Finally, apply the AutoFill tool to get the final sequence.

How to Auto Generate Number Sequence in Excel (9 Examples) (20)

8. Create Number Sequence Ignoring Blank Cells

Often, you may have to work with a number of sequences with blank cells, and you don’t want to provide a number sequence into the blank cells. Here, I have combined the IF function and the COUNTA function. However, follow the steps below to create a number sequence ignoring blank cells. For the purpose of demonstration, I have slightly changed the dataset.

How to Auto Generate Number Sequence in Excel (9 Examples) (21)

Steps:

  • Initially, write down the following formula in cell D5.

=IF(C5<>"",COUNTA($C$5:C5),"")

How to Auto Generate Number Sequence in Excel (9 Examples) (22)

Formula Breakdown

The formula uses the IF and COUNTA functions and returns the serial number. Here’s how this formula works:

  • COUNTA($C$5:C5)—-> counts the cell C5.
    • Output:1
  • =IF(C5<>””,COUNTA($C$5:C5),””)—-> checks the empty cell and returns the serial number as 1 for non-empty cell and noting for empty cell.
    • Output:1
  • After that, hit the Enter key and utilize the AutoFill tool to the whole column.

How to Auto Generate Number Sequence in Excel (9 Examples) (23)

  • Finally, the output will appear in the following image.

How to Auto Generate Number Sequence in Excel (9 Examples) (24)

9. Using Relative Reference Named Range

Last but not the least, you can use a relative reference named range in order to auto-generate number sequence. For this reason, you have to define a particular name as the function. However, go through the following steps.

Steps:

  • At first, go to the Formulas tab and select the command Define Name.

How to Auto Generate Number Sequence in Excel (9 Examples) (25)

  • Secondly, write UP as the Name and insert the following formula into Refers to.

=INDIRECT("R[-1]C",FALSE )

  • Thirdly, press OK.

How to Auto Generate Number Sequence in Excel (9 Examples) (26)

  • Again, select cell D5 and enter the formula below.

=SUM(UP,1 )

How to Auto Generate Number Sequence in Excel (9 Examples) (27)

  • Finally, you will receive the desired output.

How to Auto Generate Number Sequence in Excel (9 Examples) (28)

How to Auto Generate Number Sequence with Text in Excel

Moreover, you can use Excel’s TEXT and ROW functions to create a number sequence with text. Let’s go through the description below to complete the task.

Steps:

  • First, type the following formula in cell D5.

=B5&TEXT(ROW(A1)-1,"-00-")&C5

How to Auto Generate Number Sequence in Excel (9 Examples) (29)

Formula Breakdown

The formula uses the TEXT and ROW functions and returns the username. Here’s how this formula works:

  • ROW(A1)—-> returns the row number of cell A1, which is 1.
  • ROW(A1)-1—-> becomes
    • Output:0
  • TEXT(0,”-00-“)—-> turns into the text format of 0.
  • TEXT(ROW(A1)-1,”-00-“)—-> simplifies to
    • Output: ‘-00-
  • B5&TEXT(ROW(A1)-1,”-00-“)&C5—-> results into
  • “Lynn” & “-00-” & “25 Years”—-> returns the serial with texts.
    • Output: Lynn-00-25 Years
  • Lastly, use the AutoFill tool in order to apply the same formula to the entire column.

How to Auto Generate Number Sequence in Excel (9 Examples) (30)

Read More: How to Combine Text and Numbers in Excel and Keep Formatting

Conclusion

These are all the steps you can follow to auto-generate number sequences in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit ExcelDemy.com.

Further Readings

  • How to Use CODE Function in Excel (5 Examples)
  • Use CHAR Function in Excel (6 Suitable Examples)
  • How to Use TEXT Function to Format Codes in Excel
  • Use FIXED Function in Excel (6 Suitable Examples)
Top Articles
Latest Posts
Article information

Author: Melvina Ondricka

Last Updated: 02/13/2023

Views: 5281

Rating: 4.8 / 5 (68 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Melvina Ondricka

Birthday: 2000-12-23

Address: Suite 382 139 Shaniqua Locks, Paulaborough, UT 90498

Phone: +636383657021

Job: Dynamic Government Specialist

Hobby: Kite flying, Watching movies, Knitting, Model building, Reading, Wood carving, Paintball

Introduction: My name is Melvina Ondricka, I am a helpful, fancy, friendly, innocent, outstanding, courageous, thoughtful person who loves writing and wants to share my knowledge and understanding with you.