Excel Auto Generate Number Sequence (5 Simple Ways)

Serializing a lot of elements manually in Excel is boring. To get rid of this monotonous issue, I’m going to show you how to generate a number sequence automatically. 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.

Excel Sheet - Excel Auto Generate Number Sequence

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.

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.

Input for sequence generate - Excel Auto Generate Number Sequence

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

Cell reference increment - Excel Auto Generate Number Sequence

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

AutoFill Cell reference increment - Excel Auto Generate Number Sequence

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.

Increment 2 - Excel Auto Generate Number Sequence

2. AutoFill Method

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

Fill Handle Marked- Excel Auto Generate Number Sequence

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

Here you have two options:

  1. Double Click on the icon

Or

  1. Clicking on the icon, Drag to your desired cell

You can choose either of the options.

Fill Handle goes wrong - Excel Auto Generate Number Sequence

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.

Pattern AutoFill- Excel Auto Generate Number Sequence

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

Fill Handle Pattern - Excel Auto Generate Number Sequence

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

Fill Handle Apply - Excel Auto Generate Number Sequence

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.

Fill Series - Excel Auto Generate Number Sequence

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

Dialog Box Series - Excel Auto Generate Number Sequence

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.

Fill Series Output - Excel Auto Generate Number Sequence

3. Using the ROW() Function

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

ROW() - Excel Auto Generate Number Sequence

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.

Number generate from ROW() - Excel Auto Generate Number Sequence

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.

AutoFill Row- Excel Auto Generate Number Sequence

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.

Another ROW method - Excel Auto Generate Number Sequence

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

AutoFill Row generate - Excel Auto Generate Number Sequence

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

Another method worth mentioning here is a COUNTA() function. 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 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 a 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 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)) 

Counta sequence generator - Excel Auto Generate Number Sequence

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.

Autofill counta - Excel Auto Generate Number Sequence

5. OFFSET() Function Method

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

OFFSET result - Excel Auto Generate Number Sequence

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.

OFFSET generate sequence - Excel Auto Generate Number Sequence

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. AutoFill OFFSET generate sequence - Excel Auto Generate Number SequenceYou may have noticed; I removed the title of this column. If I hadn’t done that, then our result would be like the image belowOFFSET returns value - Excel Auto Generate Number Sequence

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

OFFSET Values sequence - Excel Auto Generate Number Sequence

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 Method

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo