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

**Table of Contents**hide

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

### 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
**5**rows.

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

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

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

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

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

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