How to Create a Number Sequence with Formula in Excel

Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. Sometimes we need to generate a Number Sequence in an excel worksheet. But doing that manually is a tiresome job. It also takes a lot of time. In this article, we’ll show you the easy ways to Create a Number Sequence with Formula in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


8 Easy Ways to Create a Number Sequence with Formula in Excel

To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Product ID. Here, we’ll show you the possible methods to create the Product ID sequence.

create a number sequence in excel formula


1. Insert Excel SEQUENCE Function to Create a Number Sequence

The SEQUENCE function can generate a sequence of numbers. For that purpose, we should input the row, column, start and step numbers. But only the row number is a must. In this example, we’ll use this function. Therefore, follow the steps below.

STEPS:

  • First, select cell D5.
  • Then, type the formula:
=SEQUENCE(5)
  • Subsequently, press Enter.
  • Thus, it’ll spill the number sequence into rows.

Insert Excel SEQUENCE Function to Create a Number Sequence

Read More: How to Create a Number Sequence in Excel Without Dragging


2. Generate a Number Sequence in Excel with ROW Function

The ROW function returns the row number in reference to a specific cell. So, learn the following steps to create a Number Sequence in Excel with the ROW function.

STEPS:

  • Firstly, click cell D5.
  • After that, input the formula:
=ROW(B1)
  • Press Enter.
  • Hence, it’ll return 1 as B1 is in the 1st.
  • Lastly, use the AutoFill tool to complete the rest.

Generate a Number Sequence in Excel with ROW Function

Read More: Auto Numbering in Excel After Row Insert (5 Suitable Examples)


3. Use COUNTA Function for Creating a Number Series in Excel

We can easily count non-empty cells using the COUNTA function. In this example, we’ll show how you can generate number series with this function. Hence, follow the process to carry out the operation.

STEPS:

  • First of all, in cell D5, insert the formula:
=COUNTA($B$5:B5)
  • Press Enter.
  • Consequently, use AutoFill to return the series.

Use COUNTA Function for Creating a Number Series in Excel

Read More: How to Increment Row Number in Excel Formula (6 Handy Ways)


4. Make a Number Series with OFFSET Function

The OFFSET function extracts cell data for reference specified in the argument section. Learn the below process to make a number sequence with this function.

STEPS:

  • Make sure the cell above the formula cell is empty.
  • Now, select cell D5.
  • Type the formula:
=OFFSET(D5,-1,0)+1
  • Next, press Enter.
  • Then, apply AutoFill.
  • As a result, you’ll get the number sequence.

Make a Number Series with OFFSET Function

Read More: How to Add Automatic Serial Number with Formula in Excel


5. Create Simple Formula in Excel for Number Sequence

Moreover, we can create a simple formula for creating a number sequence. Therefore, follow the steps.

STEPS:

  • In the beginning, choose cell D5 to type the formula:
=D4+1
  • Then, press Enter.
  • The D4 cell must be empty in this case.
  • Subsequently, return the following numbers by using AutoFill.
  • Hence, you’ll get a number sequence.

Create Simple Formula in Excel for Number Sequence

Read More: How to Add Numbers 1 2 3 in Excel (2 Suitable Cases)


6. Apply Formula with SUBTOTAL Function to Create a Number Sequence

With the help of the SUBTOTAL function, we can perform various operations. Here, we’ll input 3 as the function number to count non-empty cells. Now, see the steps below.

STEPS:

  • Select cell D5 at first.
  • Next, type the formula:
=SUBTOTAL(3,$B$5:B5)
  • Consequently, press Enter.
  • Finally, use AutoFill.

Apply Formula with SUBTOTAL Function to Create a Number Sequence

Read More: Subtotal Formula in Excel for Serial Number (3 Suitable Examples)


7. Get Automatic Number Sequence with Relative Reference Named Range Method

In this method, we’ll apply an Excel formula. But before that, we’ll perform some tricks. So, follow along.

STEPS:

  • First, go to Formulas Define Name.

  • As a result, a dialog box will pop out.
  • Insert a name (New) in the Name box.
  • In the Refers to box, input the formula:
=INDIRECT(“R[-1]C”,FALSE)
  • Press OK.

The INDIRECT function generally stores a cell reference. And we can use the reference value with other functions.

  • Now, in cell D5, type the formula:
=SUM(New,1)
  • After that, press Enter.
  • Here, New returns 0.
  • Thus, you’ll get 1.
  • Apply AutoFill to complete the number series.


8. Combine ROW and IF Functions for Creating a Number Series in Excel

Our last method is interesting compared to the above ones. Here, we’ll combine the ROW and IF functions for creating a formula. Hence, follow the process below for performing the task.

STEPS:

  • Choose cell D5 to place the formula:
=IF(C5="","",ROW()-ROW($C$4))
  • Then, press Enter.
  • Apply AutoFill.

NOTE: ROW()-ROW($C$4) part of the formula returns 1. The IF function tests whether C5 is empty or not.
  • Now, type a product name in cell C5.
  • Immediately, you’ll see 1 in cell D5.

  • Likewise, input other products.
  • Accordingly, you’ll get the number sequence automatically.

Read More: How to Create a Number Sequence with Text in Excel (5 Methods)


Conclusion

Henceforth, you will be able to Create a Number Sequence with Formula in Excel following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles 

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo