6 Excel Formulas To Create Sequence Numbers/ Automatic Numbering - MicrosoftExcel (2023)

Automatic Sequence Number Formula – Previously I already discussed about 4 ways to create an automatic sequence number in excel. This excel tutorial article is a continuation of the article. Before learning more about this tutorial, it is recommended that you read the article.

If you need excel formulas to create sequence numbers, of course you have a little problem with making sequence numbers in excel, generally because you want to make excel numbering based on certain criteria.

If it's just making sequence numbers, we can do it relatively easily as I explained in the previous article. Either by writing manually, using the autofill function or by utilizing the FILL menu feature that has been provided by Microsoft exel.

Well this time I will explain some techniques for using excel formulas to make sequence numbers according to certain conditions or conditions.

Perhaps one of them is what you need, considering that the need for numbering with excel formulas will vary greatly according to each need. If the numbering formula you need is not there, don't hesitate to convey it in the comments column. Who knew I could help solve your problem.

**You can download the discussion sample file at the end of the discussion.

Create Automatic Sequence Numbers Based On The Contents Of Other Columns With IF Function And Excel ROW Function

The first criterion is that the sequence number only appears if a particular column that is one row with a numbering cell has its contents. For cases like this we need the If Excel function.

Pay attention and please try it directly on your excel worksheet as shown in the following image:

From the picture above, we want if the NAME column has its contents, the NO sequence column will be automatically filled in.

To overcome this, write the excel formula below in cell A2, then drag or copy paste it down to the specific cell you want, for example, in this example to cell A11.

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

From the formula above the sequence number will appear if the name column in cell B2 and so on down until B11 will be filled if the name column is not empty ("").

The numbering with the formula uses the ROW function which is used to find out the position of the row. Because the first line of numbering is in the position of the 2nd line, I subtract 1 (one) so that the first number is written 1 (one) so that it corresponds to the proper order.

If the first number is not on the second row then adjust the codition of the checked column (B2) and subtract the ROW()-1 function of the formula. e.g. if the first number is on the 10th line then the formula adjusts to:

=IF(B10="";""; ROW()-9)

As seen, using the excel formula above still appears a little problem. i.e. the sequence number is still statically adjusting the row number so that if there is an empty cell in the name column in the middle, the sequence number will no longer be a sequence. Take another look at the image above for more details.

Lah, the cog sequence number is not in order? @#$%^&

If you want dynamic line numbers then use the next excel numbering formula.

Create Automatic Sequence Numbers Based On The Contents Of Other Columns With Excel's IF And COUNTA Functions

The second condition is still as in the first criterion, namely the row number is only raised if a certain column has its contents. however, the numbering adjusts the number of contents of the column examined. So that the sequence number does not jump around if there is a cell in the name column that is empty.

For the type of numbering with such conditions enter the following excel formula in cell A2 then copy paste or drag it down to the specific cell you want.

In this example, I copy and paste this formula until cell A11.

=IF(B2="";""; COUNTA($B$2:B2))

From the formula above, what you need to pay attention to is the use of the $ sign. In this case we use a semi-absolute cell reference.

By using the formula above, if the cells in the name column are empty or you delete them, the numbering will adjust automatically.

If you feel the need to explore the COUNTA function, please read the discussion of the function in the previous article.

The excel formula above will not take effect if column B of the table is generated from another excel formula. If column B contains an excel formula then use the following formula in column A

=IF(B2="";""; COUNTIF($B$2:B2;"? *"))

In this formula we use the Countif Formula to detect the presence of 1 or more characters in column B.

Formula To Create A Repeating Number Maximum of 5 (Five)

In certain excel numbering cases sometimes we need repeated sequence numbers. For example, you want the sequence numbers to be only 1-5 and repeated up to several times.

For this kind of numbering case use the following excel formula:

=MOD(ROW()-2;5)+1

In the excel formula, adjust the number -2 to the position of the first row of numbering. If you want a repeat of 10 then change the number 5 to 10.

For example if the first numbering is in cell A10 and you want a repeat of 10 then adjust the formula to be like this:

=MOD(ROW()-10;10)+1

Easy isn't it?

To create repeating numbers 1,1,1,2,2,2,3,3,3, etc., you can learn on this page: How to Create Repeating Serial Numbers With Excel Formulas

Formula Creates Repeating Sequence Numbers If The Reference Column Is Different

Another case of numbering that is often needed is that the sequence number will repeat from number 1 if the data in the reference cell is different from the previous data. if it is the same it will continue the existing sequence number.

For cases like the above, the excel numbering formula used is as follows:

=IF(B2=B1; A1+1;1)

Write the excel formula above in cell A2, then paste it down as needed.

If you look at the 10th line of the numbering, it turns out to be repeated as well even though the previous mango fruit data already exists. If you want the numbering for the same data to continue the existing numbering use the next excel numbering method.

Formulas Create Sequence Numbers To Continue For Each Reference Data

If you want different numbering for each data and continue if the data appears again then you can take advantage of the Countif function for this kind of excel numbering case.

For the data above, in cell A2 write the following excel formula

=COUNTIF($B$2:B2; B2)

In addition to the Countif function, you can also create model numbering with the following Sumproduct function:

=SUMPRODUCT(–($B$2:B2=B2))

Note that the above formula uses semi-absolute cell references such as theomroan in section B which uses the COUNTA function.

Formula Creates a Combined Sequence Number With Text

For certain purposes sometimes we want a combined sequence number. Namely the combination of text and sequence numbers. Usually the purpose is not actually for sequence numbers to be used as a certain unique code to distinguish between data records.

How to create text merge sequence numbers is actually very easy all you need is a text & only operator. In this example I want the sequence number to be a combination of the text "ABCD" and the sequence number. Then the excel formula used is.

="ABCD"&ROW()-1

Again, because it uses the ROW function, row()-1 in the formula must be adjusted to the location or position of the first numbering.

If you want other conditions, for example, such as the first condition, then just adjust it like the following formula:

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

If it is like condition B then adjust it as follows.

=IF(B2="";"";" ABCD"&COUNTA($B$2:B2))

Well I guess these 6 excel formulas to create automatic sequence numbers are sufficient.

For the discussion of each excel function that I use to compile excel formulas in this article, I have discussed the details in previous excel learning articles.

Download The Exercise File

For an example file for this discussion, it is available at the link below:

Hope it is useful.

Don't forget to Subscribe or subscribe via email or join via our social media to update other excel learning articles.