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.


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.

Using Manual Formula to Create a Formula for Serial Number in Excel

  • Then insert the following formula in Cell B6
=B5+1
  • Later, just hit the ENTER button.

Using Manual Formula to Create a Formula for Serial Number in Excel

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

Using ROW Function to Create a Formula for Serial Number

  • After that, apply the Fill Handle tool to copy the formula to get the other serials.

Using ROW Function to Create a Formula for Serial Number

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.

Using ROW Function to Create a Formula for Serial Number

  • Next, use the Fill Handle tool to apply the same formula for the other cells.

Using ROW Function to Create a Formula for Serial Number

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.

Formula with COUNTA Function for Making Serial Number

  • To get the other serial numbers, just drag down the Fill Handle icon then.

Formula with COUNTA Function for Making Serial Number

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.

Using SUM Function to Create a Formula for Serial Number

  • later, use the Fill Handle tool to copy the formula to get the serials for the rest of the cells.

Using SUM Function to Create a Formula for Serial Number

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.

SUBTOTAL Function to Create a Formula for Serial Number

  • then apply the Fill Handle tool to copy the formula.

SUBTOTAL Function to Create a Formula for Serial Number

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.

Using SEQUENCE Function to Create a Formula for Serial Number

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.

Using SEQUENCE Function to Create a Formula for Serial Number

  • later, apply the Fill Handle tool to copy the formula to get the other serials.

Using SEQUENCE Function to Create a Formula for Serial Number

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.

How to Create a Formula for Serial Number Increased by a Specific Number

  • Then insert the given formula in Cell B6
=B5+3
  • Later, press the ENTER button to get the output.

How to Create a Formula for Serial Number Increased by a Specific Number

  • Finally, apply the Fill Handle tool to copy the formula for the other serials.

How to Create a Formula for Serial Number Increased by a Specific Number

Now see, the gap between any adjacent serial numbers is 3.

How to Create a Formula for Serial Number Increased by a Specific Number


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.

How to Create a Formula for Serial Number Increased by a Specific Number

  • Lastly, use the Fill Handle tool to apply the formula to get the other serial numbers.

How to Create a Formula for Serial Number Increased by a Specific Number

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

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

Leave a reply

ExcelDemy
Logo