Serializing a lot of elements manually in Excel is boring. To get rid of this monotonous issue, I’m going to show you ways to auto generate a number sequence. For preparing the tutorial I’m using Excel 2019. No worries if you have another version.

First things first, let’s get to know about the worksheet which is the base of our examples today.

In this sheet we have a table of 5 people with their ages. Using this 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 our intention is to look at different ways of generating numbers so we are keeping the methods at the right side.

**Table of Contents**hide

## Practice Workbook

I’ve shared the workbook for you. You can download it from the link below.

## Auto Generate Number Sequence

### 1. Basic Addition Method

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

First, insert a number into the first row. Generally, serial numbers start from 1. For the example purpose keep it simple and insert 1 at the row from where you want to begin.

Now, add 1 to this row number. Use **Cell Reference** while adding so. For example, I’ve inserted 1 into the **C4 **cell. So, the formula was **C4+1**

Now do the same for the rest of the rows of the column. Make sure to keep changing **Cell References**.

Though it’s unusual to generate serial numbers with a difference more than 1, you may need to generate a number increasing by 2 (or any other increment). Use 2 in place of 1 in the previous formula.

### 2. AutoFill Method to Auto generate Number

For the **AutoFill** method you have two options. **Fill Handle** and **Fill Series**.

#### 2.1. Fill Handle

Insert value in a row and select it as **active cell**. You will see a small circular type box at the **bottom right** (marked in the image below).

I’ve written 1 in the cell and set it as **active**(selected).

Here you have two options:

**Double Click**on the icon

Or

**Clicking**on the icon,**Drag**to your desired cell

You can choose either of the options.

OH! It gave a bunch of 1s. Definitely we didn’t want anything like this. Why did this happen?

When I used the **Fill handle** only 1 was stored. Excel didn’t understand what I wanted (surely I didn’t make any command to let it understand). Let’s rectify this.

Let’s roll back and insert another number in the next row as well. Let the other number be 2.

Now select both the cells at once. You will see that **Filler** icon at the bottom right.

**Double click** or **Drag** it down the row and you will see something like the image below.

This time it worked. When we set two values, Excel understands the pattern and gives the output according to that. Here selecting 1 and 2 simultaneously, select understand a sequence of numbers incremented by 1.

#### 2. 2. Fill Series Method

For the Fill Series method you don’t need to insert two values as previous.

Insert value into one row. Selecting that row explore the **Editing** section of the **Home** tab. There in the Fill option you will find an option called **Series**.

Click **Series**, a new dialog box will open. In the dialog box set the values and direction.

Here I have set **Columns** under **Series in **(since my number generating direction was column).

**Step value**: 1 (Increment by 1)

**Stop value**: 5 ( Generated until 5)

These were my preference while preparing this example, you can choose yours.

### 3. Using the ROW Function

The **ROW** function returns the row number of the cell you are presently in (active cell).

I’ve written the **ROW** function at the 4th row (F4), and thus it returned the value 4.

So, to start from 1 (since usually we begin the serial from 1), subtract one number less than the row number.

Here, my row number was 4 (returned by **ROW** function), so I subtracted 3 from the result and it gave me the number I wanted.

Do the same for the rest of the rows or exercise **AutoFill Fill Handler**.

The method of subtracting a number from the number generated by the **ROW** function may not be as flexible as you want it to be. Let’s have a look at another approach to the **ROW** function.

Simply insert the **name of the cell **within the **ROW** function. The function will return the number of row.

Here I used **A1** as parameter in **ROW** and it gave 1. You can choose any cell, it will provide you the number.

Can you tell me how I have produced the results, showing in the image above? Yes, like we have done many times before, used the **AutoFill Fill Handler**.

### 4. COUNTA Function Method to Auto Generate Sequence

Another method worth mentioning here is the **COUNTA** function. The **COUNTA** function counts the number of cells that are not empty within a range. This method will help you serialize the rows that are not empty.

To utilize the **COUNTA** function, first we need to check whether the cell( or range of cells) are empty or not. We will do that by a function called **ISBLANK**.

**ISBLANK** is a Boolean function, returns **TRUE** or **FALSE**.

Use an **IF** statement to use the result of **ISBLANK**.

**IF(logic, if_TRUE, if_FALSE)**

Here our logic will be checking whether empty/ blank or not.

Let, for the time being not assigning any value for the **TRUE** result.

We will write the **COUNTA** function to trigger when logic becomes **FALSE**.

The formula will be like

**IF(ISBLANK(Cell Reference),””,COUNTA(Absolute Cell Reference of first cell : Cell Reference))**

Here I’ve started from A3 cell. So it will first check whether A3 was empty or not, if not **COUNTA** will trigger.

Within **COUNTA** I have set the range in a way that it always starts from the A3 cell. Exercise Fill Handler to get the rest of the values.

### 5. OFFSET Function Method

The **OFFSET** function returns the reference of a cell or a range of cells. You have to insert 3 parameters within an **OFFSET** function

**OFFSET(reference, rows, columns)**

**Reference: **The cell you want to start

**Rows:** Which number of row

**Columns:** Which number of column

Here as an example, I wrote A4, 0, 0 in the **reference**, **rows**, **columns** placeholders respectively. It provided **Ashraf Ali** as result.

Now let’s utilize the **OFFSET** function to generate a number sequence.

Formula will be like

**OFFSET(Cell,-1,0)+1**

Within the **OFFSET()** function, in this formula, first, we are referring to a particular cell. Then set one row earlier by using -1 in rows placeholder. 0 is denoting that, no traverse within column. Now add 1 to the value that **OFFSET** has returned.

Write it on the Excel. You will better understand by example.

I’ve set **H4 **in the cell value within. Now **OFFSET** returned the *value of H3* (one row less because of -1). Then added 1 to that value and gave the number.

Use **Fill Handler** for the rest of the rows to generate the sequence. You may have noticed; I removed the title of this column. If I hadn’t done that, then our result would be like the image below

It returned a string. Can we add 1 to a string? Let’s have a try

An error. To rectify these sorts of errors, keep an empty cell above your starting point while using the **OFFSET** function to generate a sequence of numbers.

### 6. SEQUENCE Function to Auto generate Number

You can generate a number sequence using a function called **SEQUENCE**.

For using the **SEQUENCE** function you have to provide one parameter, rows.

**SEQUENCE(N)**

**‘N’** is the number of rows you want to fill.

There are three optional fields in **SEQUENCE** function as well.

**Columns**,** start**,** step**.

**SEQUENCE(rows, columns, start, step)**

**Rows:** How many rows you want to fill

**Columns:** Number of columns you want to fill. If you leave it blank, it will count 1 by default.

**Start:** Beginning number of your sequence. By default, it’s 1

**Step:** Increment of each value within the sequence. By default, it’s 1

However on the downside, If you don’t have **Microsoft Office 365**, you will not get the advantage of this **SEQUENCE** function. Since I’m using Excel 2019, I can’t show you any examples. But I’m sharing a video link for better understanding.

## Conclusion

That’s all for today. I’ve tried listing a couple of ways to auto generate a number sequence in Excel. Hope you will find that helpful. Feel free to comment if anything seems difficult to understand. Let me know which of these methods you liked most. You can share your own way of generating number sequences.