Md. Sourov Hossain Mithun
We often need to create serial numbers in excel for different purposes. Instead of inserting it every time manually, we can apply some amazing formulas that are faster and time-saving. In this article, I’ll show 9 quick formulas to create a formula for the serial number in Excel with easy steps and proper images.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
7 Ways to Create a Formula for Serial Number in Excel
First of all, get introduced to our dataset that represents the most ordered 5 smartphones of an online shop in 2021. We’ll show the ranking by inserting the serial numbers by using formulas.
1. Using Manual Formula to Create a Serial Number in Excel
First, we’ll apply a simple manual formula to create a serial number. The formula will just add 1 with the previous serial number. That’s why we’ll have to insert the first serial number manually.
Steps:
- Firstly, insert 1 in Cell B5.
- Then insert the following formula in Cell B6–
=B5+1- Later, just hit the ENTER button.
- Finally, drag down the Fill Handle icon to copy the formula for the other cells.
Soon after, you will get the serial numbers like the image below.
Read More: How to Increment Row Number in Excel Formula (6 Handy Ways)
2. Using ROW Function to Create a Formula for Serial Number
By using the ROW function, we can create serial numbers in English numbers or in Roman numbers.
2.1. Creating English Serial Number
To make the English numbers, only using the ROW function is enough.
Steps:
- Type the following formula in Cell B5–
=ROW()-4- Press the ENTER button to get the output.
- After that, apply the Fill Handle tool to copy the formula to get the other serials.
Here are all the serial numbers.
Read More: Auto Serial Number in Excel Based on Another Column
2.2. Creating Roman Serial Number
To get the Roman serial number, we’ll apply the ROW function with the ROMAN function.
Steps:
- In cell B5, write the following formula-
=ROMAN(ROW()-4)- Then hit the ENTER button for the output.
- Next, use the Fill Handle tool to apply the same formula for the other cells.
Now see, we have got the serial numbers in Roman numbers.
Read More: How to Create a Number Sequence with Formula in Excel
3. Formula with COUNTA Function for Making Serial Number
We can use the COUNTA function to make serial numbers too.
Steps:
- First, insert the following formula in Cell B5–
=COUNTA($C$5:C5)- Then press the ENTER button to finish.
- To get the other serial numbers, just drag down the Fill Handle icon then.
Soon you will see that all the serial numbers are created as like the image below.
Read More: Auto Numbering in Excel After Row Insert (5 Suitable Examples)
4. Serial Number Formula Using SUM Function
The SUM function can be used too to make serial numbers. We’ll have to apply it in a tricky way.
Steps:
- Type the following formula in Cell B5–
=SUM(B3,1)- Next, hit the ENTER button for the output.
- later, use the Fill Handle tool to copy the formula to get the serials for the rest of the cells.
Then you will get your desired output.
Read More: How to Add Automatic Serial Number with Formula in Excel
5. Formula with SUBTOTAL Function to Create Serial Number
The SUBTOTAL function can be used too for creating serial numbers. Let’s see how to apply it.
Steps:
- Write the following formula in Cell B5–
=SUBTOTAL(3,$C$5:C5)- Next, press the ENTER button.
- then apply the Fill Handle tool to copy the formula.
Soon after, you will see that the SUBTOTAL function has given the output as the SUM function.
6. Using SEQUENCE Function to Make Serial Number
Previously we have used the COUNTA function. But if we use it within the SEQUENCE function then we can make an array formula by which we can get the output at once.
Steps:
- First, type the following formula in Cell B5–
=SEQUENCE(COUNTA(C5:C9))- Later, just hit the ENTER button to get the output as an array.
Have a look, we got the output at once, no need to use the Fill Handle tool.
Formula Breakdown:
- COUNTA(C5:C9)
The COUNTA function will count the cells for the range C5:C9 and then it will return as-
5
- SEQUENCE(COUNTA(C5:C9))
Then the SEQUENCE function will make a sequence from 1 to 5 as an array-
{1;2;3;4;5}
Read More: How to Create a Number Sequence in Excel Without Dragging
7. Serial Number Formula Using OFFSET Function
To apply the OFFSET function we’ll need an empty cell before the cell where we’ll first apply the formula, that’s why I have added an empty row.
Steps:
- in Cell B5, type the following formula-
=OFFSET(B6,-1,0)+1- Then to get the output, hit the ENTER button.
- later, apply the Fill Handle tool to copy the formula to get the other serials.
Then you will get perfect serial numbers like the image below.
Read More: Auto Generate Serial Number in Excel VBA (4 Ways)
How to Create a Formula for Serial Number Increment
In the previous methods, we used formulas that increased the serial number by 1. But sometimes in some particular cases, we may need to find the serial numbers increased by a specific number. So now, we’ll learn that cases.
1. Formula for Serial Number Increment by Adding a Specific Number
First, we’ll learn to make a formula for serial numbers that will increase by adding up a specific number each time. I’ll add 3. This method is pretty much like the very first method.
Steps:
- Firstly, insert the first serial number in the first cell from where you want to start. I inserted 1.
- Then insert the given formula in Cell B6–
=B5+3- Later, press the ENTER button to get the output.
- Finally, apply the Fill Handle tool to copy the formula for the other serials.
Now see, the gap between any adjacent serial numbers is 3.
2. Increasing Serial Number by Multiplying a Specific Number
Now we’ll increase the serial number by multiplying a specific number with the previous serial number.
Steps:
- Again, insert the first serial number in the first cell from where you want to start. Here also, I inserted 1.
- Type the following formula in Cell B6–
=B5*3- Then just hit the ENTER button.
- Lastly, use the Fill Handle tool to apply the formula to get the other serial numbers.
Soon you will get the output as the image below.
Conclusion
That’s all for the article. I hope the above procedures will be good enough to create a formula for the serial number in Excel. Feel free to ask any questions in the comment section and give me feedback. Visit ExcelDemy to explore more.
