4 Easy Ways to Create "Automatic" Sequence Numbers With Microsoft Excel

Making numbering on reports is certainly fairly common, but if in large quantities, of course, it will take time, especially if you have to fill in the number manually. The serial number on the job report serves to make it easier for us to identify the contents of the existing report on the worksheet. In addition, the report will look neatly organized.

In Microsoft Excel, there are several facilities for creating numbers sequentially or in large multiples. There are several commands to quickly number worksheets.

1. Creating Manual Sequence Numbers

How to make the first number is by writing the sequence of numbers one by one manually. Manually typing numbers like this is arguably the most basic and easy way to do it. However, this method is one that I often avoid. Why? Just imagine if the serial number of the data that we create is in the thousands.

How to make serial numbers Manually like this, you should leave. The alternative is the second method below.

2. Create Automatic Numbering With Excel Autofill

The second way is to use the autofill feature in Microsoft Excel. Creating automatic serial numbers by utilizing the excel autofill feature is quite easy. We just need to make one or two numbers as a prefix and then we just drag them down or in the direction we want.


This method is often used by Excel users to make automatic numbers in Microsoft Excel. Using excel autofill is very effective if the serial number that we make is only around tens or hundreds. If the amount of data we are going to give serial numbers is in the thousands, I think this method is less effective. If you intend to make serial numbers in the thousands, use method number three.

3. Create Automatic Numbering With Excel's Fill Menu

If the records or rows of the data table that we are going to create are in the thousands, I suggest using this method to do the automatic numbering. Then what are the steps to make automatic numbering with this excel fill menu? follow how to make automatic numbering with the following fill menu:

1. Manually write down the initial (first) number that we will make. In this example, I wrote the number 1 (one) in cell A1. Then select cell A1.

2. Select "Tab Home" then select "Fill" and then next select "Series".


3. The "Series" Option Box appears.

4. If we want to make automatic numbering downwards or in one column, then select "Columns". Instead, choose "Rows" if the number we are going to make it to the right. In this example, I selected "Columns".

5. In the type, select "Linear".

6. In the "Step Value" section, fill in the additions for each numbering sequence. The default is 1.

7. In the "Step Value" section, fill in the last number you want, for example, 1000.


8. Click "OK" and see the result.

In this way, numbering up to thousands or millions, of course, is very easy. Certainly, it is more effective than using autofill like the second way.

4. Create Automatic Numbering With Excel Formulas

In addition to the three methods above, there are other ways to do automatic numbering in Microsoft Excel. The fourth way is to use excel formulas.

Excel formulas that are often used for automatic numbering include the IF, COUNT, COUNTA, and ROW functions as needed.

These Excel functions can be used to create an excel formula to create automatic numbering based on certain criteria. For example, the number in column A will appear in column B contains content.

For example, enter the following excel formula in cell A2:

Then copy-paste the formula into other cells below it.


= IF(B2="";"";ROW()-1)

With the above formula, column A will be automatically filled with serial numbers if column B contains content or we write certain data, for example, someone's name in the example above.

That's how to make automatic serial numbers using formulas in Excel quickly and easily. Hope it can help you.

Lebih baru Lebih lama