In this tutorial we are going to learn how to Enter Sequential Numbers in Excel.
A sequence can be defined as a list of objects or items which have been arranged in a specified manner.
This object can be alphabets, numbers, dates, a group of items, etc.
A set of numbers can be said to be sequential if they progress following a specified pattern.
This pattern can be ascending (upwards / incremental) or descending (downwards / decremental).
Now let’s move to the business of the day! How can we enter numbers sequentially in excel?
Do we have to manually type them?
The answer is No.
In the course of this tutorial, we will learn how to enter sequential numbers in excel using:
- The Fill Handle feature
- Adding 1 to the Previous Row Number
- The Fill Series feature
- The ROW Function
- The Sequence Function
- Converting the dataset to an excel table
At the end of this tutorial, you will be well-grounded on how to use the various methods, their respective limitations and you can apply the method that most suits your data to carry out your sequential numbering task.
Before we proceed, we must define key terminologies that you may come across in this tutorial.
- Columns – This is a vertical gridline in excel
- Rows – This is a horizontal gridline in excel
- Function: This is a pre-established formula that performs calculations by using specific values, called arguments, in a particular order E.g., AVERAGE, COUNT, COUNTIF
- Start Value – This is the first value in a sequence
- Stop Value – This is the last value in a sequence
- Step Value – This is the amount to increment each subsequent value in the array/sequence.
The table below shows a list of Assets and the year they were purchased in an office.
Using the above-mentioned methods, we will see how to assign a Serial Number (S/N), to identify these assets.
| S/N | Asset Name | Year of Purchase |
| Hp Laptop | 2003 | |
| Office Rack | 2003 | |
| Scanner | 2020 | |
| Printer | 2003 | |
| D.P.M | 2019 | |
| Telephone | 2007 | |
| Router | 2003 | |
| Switch | 2001 | |
| Server | 2003 | |
| Microwave | 2005 | |
| Fridge | 2003 |
Here’s How to Enter Sequential Numbers in Excel:
The Fill Handle feature
This feature is used to extend a series of objects (numbers, dates, or even text) to a desired number of cells.
Excel achieves this by identifying patterns in your data and spreading that pattern to fill up the remaining cells in the designated column.
This method is best suited for scenarios where you simply need to add a sequence of identifiers for each row of your data.
Before you try this feature, we need to be sure that it has been activated in your excel settings.
- If you are using an Excel version of 2010 and later, click on the File tab then select Options.
For Excel 2007, simply click on the Microsoft Office Button, and then click the Excel Options
- An ‘Excel Options’ box appears
- Click on the Advanced option
- Navigate to the Editing Options, and check the Enable Fill handle and cell drag-and-drop check box to display the Fill Handle feature
- Click OK to save the changes made
Great! Let’s proceed
Step 1: In Cell A2, manually type in number 1
Step 2: In Cell A3 type in number 2
Step 3: Select both Cell A2 & A3.
If you carefully observe your selection, you will notice a small square at the bottom right. This square is called the fill handle. It changes to a Plus (+) sign whenever you hover your cursor over it.
Step 4: Hover your cursor over the fill handle and double click. It automatically fills all the cells in the range.
Alternatively, you could Click on the Fill Handle and drag it to the end of the range you want to fill.
Limitations of the Fill Handle Feature
- The Fill Handle Feature requires the adjacent columns to be filled already
- It does not automatically renumber cells if a new row is added/ deleted. In our example, if we insert a new row between S/N 6 and S/N 7, to capture a Television set purchased in 2002 which was initially omitted, you will notice that the new cell’s S/N remains blank.
Adding the Step Value to the Previous Row Number
The ideology behind this method is to add the difference between consecutive values in the series to the previous cell number above.
Applying this to our example, we will simply follow the steps outlined below:
Step 1: Identify the Step value between consecutive numbers. In our example, this is ‘1’
Step 2: In Cell A2, enter the start Value (Since we want to assign serial numbers to the assets, our start value is ‘1’)
Step 3: In Cell A3 enter this formula =Previous cell number above +Step Value. In our case, this will be =A2+1
Step 3: Press the tabs/ENTER key on your keyboard.
Step 4: Select Cell A3. You will notice a square at the bottom right (i.e., the fill handle)
Step 5: Drag down the square box to the last record.
You will notice that it automatically assigns the sequence value to all the cells in the range.
If you click on any of the cells in the range along that column, you will discover that a linear increment Has been applied at each step.
Limitation of this method
- Just like the Fill Handle method, if a new row is added the numbering does not change. In our example, if we insert a new row between S/N 6 and S/N 7, to capture a Television set purchased in 2002 which was initially omitted, you will notice that the new cell’s S/N remains blank.
- If an existing row gets deleted, all cells beneath the deleted row will display a reference error. So, if we decide to delete Row number 5 in our sample table, all S/N Cell below 5 will get affected.
Fill Series Method
This method gives you more control over how the sequential numbers is entered in your excel sheet.
It is best suited for scenarios where the Start and Stop values of your sequence are known.
Unlike the Fill Handle method where the adjacent columns must be filled, the Fill series allows all cells to be filled not considering adjacent columns
To use the Fill series method, kindly follow the steps below:
Step 1: In Cell A2, manually type your Start Value (In our example, this is ‘1’) then highlight where you want your sequential numbers displayed.
Step 2: Click on the home tab
Step 3: Navigate to the Editing sub-option
Step 4: Click on the Fill drop-down
Step 5: From the drop-down, Select Series
A series dialogue box pops up. This dialogue box allows you to define key parameters as it pertains to your sequence.
Step 6: Select where you want your series to be displayed in the Series in option. In our example, we selected the Columns because we want our sequence displayed in column A.
Step 7: Select the desired series pattern.
Linear: For a sequence with a constant Step value
Growth: For a Sequence with a geometric Step Value
Date: For a sequence with a date pattern
Step 8: Input the step Value
Step 9: Input the stop Value
Step 10: Click OK
You will observe that the numbers are automatically filled in the highlighted section in your excel.
Limitations of this method
- Just like the Fill Handle method, if a new row is added/deleted the numbering does not change. In our example, if we insert a new row between S/N 6 and S/N 7, to capture a Television set purchased in 2002 which was initially omitted, you will notice that the new cell’s S/N remains blank.
The ROW Function
The ROW functions address the limitations of the 3 methods previously discussed because it does not reference any cell.
Thus, even if we insert a new row, delete an existing row or sort the data, the sequence order will dynamically change to reflect the new row position.
This function is given by: ROW()
If the ROW function is typed in the first cell (A1), it returns the number 1.
In our example, because we want the S/N numbering to commence from Cell A2 our ROW function will be given as:
ROW()-1 (this directs excel to identify the current row numbers and then take one step backward. So, if the cell number is A4, excel returns 3
TO use the ROW function simply:
Step 1: In Cell A2, type in this formula ‘=ROW()-1’
Step 2: Press the ENTER key on your keyboard
Step 3: Select A2. You will notice a square at the bottom right (i.e., the fill handle)
Step 4: Drag down the square box to the end of the range where you wish to fill
The sequence value automatically populates the cells.
Limitation of this method
- When rows are added, moved, or deleted, it interrupts the sequence. Hence, we need to manually update the numbering by selecting two numbers that are in the right sequence and then dragging the fill handle to the end of the range.
Sequence Function
This function is used to generate sequential numbers in the form of a dynamic array.
It was recently added to excel.
Hence only newer versions like Office 365 and Excel 2021 can perform the Sequence action.
To use the Sequence function, you must follow the syntax below:
SEQUENCE(rows, columns, start, step)
Where:
- Rows – represents the number of rows that you wish returned in the array
- Columns – represents the number of columns that you wish returned in the array. This parameter is optional
- Start – This is the start value. This parameter is optional
- Step – This is the step value. By default, it stays at 1 and the parameter is optional
You may have noticed that only the row parameter is compulsory.
This means while using the Sequence function, you may decide to use any of the optional parameters or not.
This will not in any way affect your result.
In fact, by default all optional parameters are set as 1.
Back to our example!
Since we wish to display the Serial Numbers (S/N) across 11 rows in just one column, THE row value will be set as 11, Column as 1, Start value as 1, and step value as 1.
Now let’s highlight the steps to be taken:
Step 1: In Cell A2, type in SEQUENCE(11,1,1,1)
Step 2: Press the ENTER key on your keyboard
This will return an array of 11 sequential numbers in column A.
Converting Dataset to Excel Table
When working with tabular data, Excel tables come in very handy.
It makes using and managing data a lot easier.
This is possible because the excel table groups data into a single object.
Some of the benefits of using Excel tables include:
- Table formulas are automatically updated when a row is inserted or deleted.
- When a new row is added to the end of the table, it automatically expands and assigns the right sequence number. Thus, eliminating the stress of copying the formula across the range.
To convert your dataset into an excel table, simply follow the steps shown below:
Step 1: Select all cells of the dataset.
Step 2: Click on the Insert Tab
Step 3: Select Table
Note: Alternatively, you can press CTRL + T on your keyboard
Step 4: A Create Table dialogue box pops up
Step 5: Cross-check the range highlighted in the ‘Where is the data for your table?’ field and ensure it is correct
Step 6: Check the ‘My table has header’ box if your selection includes a header. Else leave the box unchecked.
STEP 7: Click OK
Step 8: Your selected range is automatically converted to an Excel Table.
You will notice some changes in the formatting of the table. Let’s try and mention some of these noticeable changes
- A Filter arrow appears after the header on each column
- The unique box is used to enclose the selected dataset
- The styling of the selected range is different from the others in the sheet
- When you click on any of the cells in Excel Table, a Table Design tab appears in the main menu
Conclusion
In this tutorial, we have seen how to enter sequential numbers in excel using six (6) methods.
- Method 1, 2, and 3: Are quite unstable and the sequencing can easily get messed up whenever you alter rows of the dataset.
- Method 2: Is more stable than Method 1 but it can also get messed up whenever you alter rows of the dataset.
- Method 4: Does away with this issue but you will still need to re-sequence every time you add, delete, or insert a row.
- Method 5: is quite robust as it lets you keep the original formatting of your dataset. However, the function used in this method (the SEQUENCE function) is only available in newer Excel versions like Office 365 and Excel 2021.
- Method 6: much more robust, but it involves completely reformatting your dataset into an Excel table.
In conclusion, your choice of method to be employed may depend on the quantity of data as well as the data needs at that instant.
