Skip to content

How to Make Automatic Numbers in Excel Practical

How to Make Automatic Numbers in Excel Practical

How to give automatic numbers in Microsoft Excel can be done with various tricks, one of the most frequently used is using the mouse drag and drop function.

In addition, to automatically create serial numbers in Microsoft Excel, we can also use formula functions, which this method is rarely used for.

We discuss this method because if we fill in serial numbers in Excel manually one by one, it takes a long time and is definitely tiring.

Therefore, we need a little trick to enter the serial number in Excel to make it easier and faster, of course.

For those of you who happen to often create tables in Ms Excel both in notes or receipts to making a table of contents, you really need these tips to speed up your performance in adding serial numbers in Excel worksheets (Worksheet).

Immediately, the following tips make automatic numbering in Excel

how to make automatic serial number in microsoft excel

Well, to sort numbers in Excel from small to large is very easy. Moreover, if we use this method, we can minimize the error rate when inputting or entering numbers into the table.

You can do this trick to make automatic serial numbers using Microsoft Excel for all versions of Microsoft Office starting from Ms Excel in 2007, 2010, 2013, 2016, and 2019. This is because basically the method used is not much different.

Steps to Automatically Number Sequences in Excel

Creating Automatic Numbers Sequentially from Small to Large Numbers

1. Open the Ms Excel worksheet first.

2. Enter the numbers sequentially starting from the smallest number, for example I enter the number 1 for the first row and the number 2 for the second row.

how to make automatic serial number in excel

Well, for the location of the cell itself, you are free to put it where you need it.

3. After that you block from the top cell to the bottom cell.

4. If you have hovered the cursor right at the bottom right corner of the cell, usually the mouse pointer will change to a black plus sign.

how to make serial numbers in excel automatically

5. Now you can drag (Drag) down until the required line.

6. Then the numbers will be sequentially automatically.

how to give serial number in excel automatically

At this stage you have successfully typed the automatic number in Excel.

Next we will add consecutive numbers with a certain number multiple for each number.

Check out other articles about how to make a table in excel with the same size must be neat.

Generating Sequential Automatic Numbers (Multiple Numbers)

1. Just as before, we open the Ms Excel worksheet.

2. After that enter the numbers in the number column, for example, we want to sort the numbers like this: 2, 4, 6, 8, etc..

how to make automatic serial number using microsoft excel

3. Then just type for the first line number 2 and the second line number 4.

4. After that you block the two cells and then right in the lower right corner you drag down to activate the Excel Autofill function.

how to auto-fill numbers in excel

5. The numbers will automatically be sequential with a certain number of multiples.

how to auto-enter number in excel

Not only for multiples of two (even numbers), you can also sort them by multiples of 3 (odd numbers).

Different from the above method, we will create an automatic number in Excel continuously from the numbers we have created previously.

Also read tips how to make a table in Ms Word so neat and attractive with a modern design.

Generating Continuous Automatic Numbers

1. Open a Microsoft Excel worksheet.

2. Enter the numbers in the order that already exists, for example in the table there are already numbers 1, 2, 3, 4, and 5.

how to sort excel auto number

3. So here we only need to select or block rows of numbers 5 and 6 only.

5. Then you drag as usual from the bottom right corner vertically to sort the numbers automatically.

how to make excel automatic numbers

6. You can also sort from the numbers as you like, for example for the numbers prefixed with 11 and 12. You can simply block the numbers 11 and 12 and then drag them down to sort them.

Apart from these methods, there is still a technique to automatically generate serial numbers in Excel with the number of rows up to thousands. Here’s Excel’s automatic number setting menu that you can try to sort numbers in a column or row.

More tips about how to make a table in Microsoft PowerPoint for a more attractive presentation.

Generate Sequential Automatic Numbers Up to Thousands of Rows

1. Open the Microsoft Excel application.

2. Finish creating the table, you can type the number 1 at the top in the numbering column.

how to make automatic serial number in excel 2016

3. After that you open the menu Home then select menu Fill in the Editing.

how to make automatic serial number in excel 2013

4. In the Fill menu, select the submenu Series (Fill Series).

how to type auto number in excel

5. A Fill Series pop up appears where there are many options that you can use to set automatic numbers in Ms Excel, here are some of them.

  • Tab Series in: Please choose Columns (Column) so that the numbering is from top to bottom (Vertical).
  • Tab Type: You just choose the option Linear.
  • Step Value: You enter the number 1 for each distance between numbers, you can enter a nominal value of more than 1 for multiples.
  • Stop Value: Just enter the last number you want to make, for example the number for 1 – 10 You enter the number 10 in this column.

6. Finally don’t forget to press the button OK.

Finally, we will create an automatic number in Excel with a simple formula, because if we create an automatic serial number with the if function, it is more complicated and certainly makes you dizzy.

Other related tutorials how to group data in excel automatically to be faster.

Generate Excel Auto Numbers with Formulas

1. Prepare the Excel table that you created earlier.

2. You hover right below the text Number.

how to make automatic serial number with formula

3. Enter the following formula for the ROW function in the box.

Auto number formulas in Excel


After entering your formula press the button Enter on the keyboard.

Because this is where the number 1 is in the 3rd row, I changed the number -1 to -2 as shown in the picture.

Just adjust it to the location of the previous line so that the results are not ambiguous.

4. After that as usual you look for the plus button at the bottom right corner and then drag it down so that the results come out.

how to make automatic serial number in excel with formula

5. Well, for the results themselves are very satisfying serial numbers automatically using this formula.

how to make automatic serial number with if fungsi function

One of the advantages when sorting numbers in Excel using a formula is that when we delete several rows in the table, the sequence of numbers will automatically adjust to the number of existing rows.

So the data will be more valid because the correct number sequence does not jump between one number and another.

More tips about groups subtraction formula in multi-column excel which you must try.

Actually there are other formulas that we can use to sort numbers automatically in Ms Excel, for example using the Subtotal function, but I think this is enough if it’s just for arranging numbering in Excel worksheets.

Hopefully, this guide on how to create automatic serial numbers in Microsoft Excel can help you sort numbers quickly and practically in an Excel worksheet table (Worksheet).


#Automatic #Numbers #Excel #Practical

❤ XOXO ❤

Leave a Reply

Your email address will not be published. Required fields are marked *