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.
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.
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
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
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
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().
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
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
OFFSET() function returns the reference of a cell or a range of cells. You have to insert 3 parameters within an OFFSET() function
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
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 Method
You can generate a number sequence using a function called SEQUENCE().
For using the SEQUENCE() function you have to provide one parameter, rows.
‘N’ is the number of rows you want to fill.
There are three optional fields in SEQUENCE() function as well.
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.
The post Excel Auto Generate Number Sequence(5 Simple Ways) appeared first on ExcelDemy.
