# How to Create a Formula for Serial Number in Excel (7 Methods)

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.

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. ### 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. #### 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. ### 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. ### 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. ### 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}

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

## Related Articles #### Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts 