Aung Shine
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.
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.
- 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.
