Unlike other Microsoft Office programs, Excel does not provide a button to auto number in excel.

Adding serial numbers manually is a pain. Moreover, they will be static and will not renumber automatically by adding or removing records.

Product IDs, serial numbers for transactions require a unique identifier, that skips blanks & renumbers automatically when adding or deleting records.

You can download the exercise file and follow along by clicking on the button here below

in this worksheet I want to create a column of incremental numbers

and I’m going to start with a very basic example.

1- Using the AutoFill handle.

Select cell A4 and type number 1, then hit enter.
Now if I hover over the lower right corner (mouse pointer changes to a tiny plus sign) the autofill handle, I press control while dragging all the way down And I would have created a column of incremental numbers.

Unfortunately, this column has lots of problems:

• The first thing it is counting the blanks.
• If I delete a record or if I add a new record nothing changes in the numbers.
• If I apply sorting all the numbers will be messed up.

so, this static technique doesn’t work and it’s not my best option

2- Using Fill Series command

I’m selecting cell K1 And I typed number 1 and then

I go to the right side of the home tab I click on the down arrow of the Fill command And I select field Series…

In the series dialog box I specified that I want the series in a column the step value will be 1 because I want to increment by 1 and then for the stop value I want to stop at 25,000 when I hit OK

I would have created a column of incremental number from one to 25,000.

3- Using the ROW() function.

In cell D4 I’ll be typing =ROW and I open bracket, ROW of what? I’ll use cell A1 (the ROW function is returning to number of the row and because A1 is in row number one then it will be returning one) and then I close the bracket and hit CTRL + Enter.

Then I click and drag.

This technique is a little bit better because if you apply any kind of sorting ALT,A,S,A to sort, everything is automatically renumbering but if you delete or if you add a value nothing renumbers. So, although it’s a little bit better than the previous one but still, it’s not the best option.

4- Creating a column of Roman numbers

I can also create a column of Roman numbers, so if I type equal row and then I open bracket, type A1 and close bracket; and then I wrap the ROW function in a Roman function, I close the bracket and hit enter. When I do this, I get the Roman number,

I can copy it all the way down and you can see it is left align denoting that this is text.

If you want to simple method auto number in excel, we recommend you read this article here

5- Creating a column of Decremental Numbers

We can also create a column of decremental number and in this case, I’ll be using the ROWS function (with an S)

let’s see how we do that in the next worksheet I’m selecting cell B4

And I type =ROWS(B4:$B$20) I select the entire range ► I closed the bracket and I put my blinking cursor in the second cell reference and I lock it by hitting F4 so the second cell reference is not going to increment while the first one will keep incrementing. I hit Control + Enter and then I can copy it all the way down and I would have created a column of decremental number.

6- Creating a Column of 4 Digits

I can also start counting at 1001 so if I type =ROW(A1001) and then hit Enter► Click and drag down► then I’m starting to count from 1001 .

7- Column of 3 Digits with leading Zeros

To create a column of incremental number with a leading zero, I need to join a function to text I’ll be typing equal and in double quotes I’ll be typing
=“000” & ROW(A1) ► Enter.

As you can see it’s returning a number with leading zeros but what happens if I copy down? when I copy all the way down► I have different sizes of numbers I want to unify them I want to make all of them 3 digits.

Then I’m going to wrap this function in a RIGHT function.
Let’s it F2 and then type

=RIGHT(“000” & ROW(A1),3)

Hit Enter ► Drag down

All these techniques are static or partially dynamic we need to create something much more dynamic

Now let’s move to the next worksheet and in the next worksheet in our exercise file I am in cell A4 and I would like before creating a function that counts, I want to evaluate column B to my right so if it is blank I don’t want anything but if it has contents I want to start counting.

8- Incrementing Conditionally and Dynamically using IF & MAX

I type in cell A4

=IF(B4=”” , “” , MAX($A$3:A3)+1)

• The IF function evaluates the presence of contents in Column B: If it is Blank ► it returns nothing. Otherwise it returns the MAX of Column A.
• Locking the first cell reference of Column A creates an Incremental number.
• Because the MAX function of A3:A3 is zero, we add 1, to start counting at 1.

When I put it in the edit mode (F2) and I add 1 and now it starts at one if I click and drag that creates a column of incremental number which is dynamic it excludes the blank cells if I delete the value by hitting Delete everything re-numbers if I type any name I’m going to type my name Nabil Mourad and automatically it will be included in the numbering system that’s a dynamic technique.

9- Using COUNTA Function

let’s see another one all the dynamic techniques will be evaluating the cell to the right before creating the numbers

So I’ll be typing in cell D4

=IF(E4=””,””,COUNTA($E$4:E4))

=IF and then I hit tab if the cell to my right equals blank then I want nothing otherwise I need to count I’ll be selecting COUNTA and I’ll be counting the cell to my right and hit SHIFT + Colon to create an expandable range and I close the bracket. I need to lock the first reference (F4) to create an expandable range and then I hit Enter .

Now I can copy it all the way down and like the previous one deleting a value will renumber typing another value will renumber as well.

10- Using the SUBTOTAL Function

I can also do it with the subtotal function , in cell G4 type

=IF(H4=””,””,SUBTOTAL(3,$H$4:H4))

equal IF and then, I hit TAB, if the cell to my right is blank equals double quote double quote then, I want double quote double quote, otherwise I need to create a SUBTOTAL function.
The subtotal function is a replacement to 11 functions. I want to use it as a COUNTA so I select #3 and then I hit comma where is the ranged you want to count? it’s one cell to my right and SHIFT + Colon and I close the bracket for the two functions. Do not forget we need to lock the first reference (F4) and then I hit enter that’s exactly the same thing like the previous function.

11- Using the Table functionality

I moved to the next worksheet I can create a column of incremental number by converting my setup into a table.

To convert a list into a table I hit CTRL + T and then hit Enter.

I’ll be creating a function in cell B4

=IF([@Names]=””,””,ROW(A1))

l type equal IF and then I hit TAB, if the cell to my right equals a blank then double quote double quote, otherwise I need to create a let’s say a ROW function that counts from cell A1 and then I closed the bracket for the ROW And I closed the bracket for the IF when I hit enter I have renumbered and everything is working fine so if I add any name, it automatically numbers and increments.

That’s another technique but, it’s not my preferred one I go to the next example where I want to create a modular division

12- Using the MOD Function with ROW function

in the next worksheet I want to create a set of numbers with a pattern (Say 3 or 5) so I want to create number 12345 and then I repeat 12345 and keeps repeating 12345 and I’ll be using the MOD function.

The MOD function returns the remainder of a division so I’ll be typing equal MOD(ROW(A1), and I closed the bracket for ROW(A1) ► that’s number one what’s your divisor? I hit comma and I want to divide by the number in Cell F4 and that means 1 / 5 then we have 0 and the remainder is 1. if I closed the bracket and then hit enter it returning 1. I must lock cell F4 (by hitting F4).

What happens when I copy down? when I copy down I get 1234 and then it starts over from 01234 while I want to get 12345 and I want to re-count from 12345 and to do this I’m going to add 1 so I say plus one and then I hit enter.

=MOD(ROW(A1),$F$4)+1

Now I’m counting 2345 at the first set, but I adjusted the second occurrence.

let’s adjust the first one by typing minus 1 and that will fix the issue .

=MOD(ROW(A1)-1,$F$4)+1

Now I have 12345 let’s understand the logic by opening the Evaluate Formula dialog box in the evaluate formula will see the steps of this calculation and how Excel processes this calculation step by step.

13- Using the WEEKDAY Function

I can also create a modular pattern of 7 days simulating the weekdays and for this I’ll be using a WEEKDAY function.

=WEEKDA(ROW(A1))

Equal weekday and then I hit TAB, the WEEKDAY function expects a number and a return type. The return type is optional. So, I’m going to say I want to start at day number one (the 1st of January 1900) So, if I type ROW(A1) ► and then I closed the bracket for the ROW and I closed the bracket for the WEEKDAY function.

When I hit enter it returns number one and then the next day is day number 2,3, 4, 5, 6, 7 and so on. Look when I reached day #7 if I continue dragging what I’m getting I’m getting a pattern counting from one to 7

14- Using the QUOTIENT Function

Sometimes you need to repeat the same number and for this pattern I’ll be using a QUOTIENT function. I’m in the next worksheet and I want to test the QUOTIENT function what does the QUOTIENT function do.

When you divide a number by a divisor the result is the integer part, that is returned by the QUOTIENT function and a remainder, returned by a MOD function.

So I’ll be typing equal QUOTIENT of ROW (A1) ► That’s the numerator.

I type a comma, and I say my denominator will be whatever comes from cell E1 and I lock it (F4) ► when I hit enter 1 / 5 returns zero. So I add 1 to start numbering at 1.

What if I copy down? I get 1, repeated 4 times only then each subsequent number is repeated 5 times. That is very close to what we want.

Because the first occurrence has only 4 so I want to subtract from the ROW ► I want to start from row #0.

=QUOTIENT(ROW(A1)-1,$E$1)+1

Now when I hit CTRL+ Enter ►I get the required pattern.

let’s evaluate the formula in the formula evaluator.

If we change the number in cell E1 (Say 3) ► we get the same pattern and each number repeats three times, then increments.

15- Combining IF, MOD and QUOTIENT functions

If you want to maintain the previous pattern but you don’t want to show duplicates then I have to make it a little bit more complicated by combining the MOD, QUOTIENT and IF functions.

I go to the next worksheet and I start by recreating the same QUOTIENT function as before.

=QUOTIENT(ROW(A1)-1,$E$1)+1

I don’t want duplicates as I copy down because when I copy down it’s repeating 5 times and if I change cell E1 to 3, then it will be repeating 3 times

In this case I use the previous MOD & ROW functions to evaluate the cell ► If it returns Zero ► I run my Quotient function. Otherwise I need a blank.

=IF(MOD(ROW(A1)-1,$E$1)=0,QUOTIENT(ROW(A1)-1,$E$1)+1,””)

The MOD portion (which is the logical test of the IF) will Only return a Zero with the first occurrence of each number ► Then the Quotient function runs (Value if True of the IF). Otherwise we get a blank.

That’s the pattern we need.

It might be a good practice to precede each number by a tet that does not change the data type (from number to text). To do that I open the Format Cell dialog box CTRL +1 ► Click on the Custom category ► Under “Type” ► click before general and type “WK “ then hit OK.

I get WK 1, WK 2 and so on. When you select any cell and look in the Formula bar you just see the formula not the text, since it is a custom format.

That’s wonderful

I can also create a column of incremental number by using a sequence function that’s a dynamic array function. It’s only available in Office 365.

Using Dynamic Arrays:

16- SEQUENCE function to Increment

In the next worksheet in cell B1 ► I type

=SEQUENCE(20,1,1,1)

The SEQUENCE function requires 4 arguments:

• The number of Rows you want
• The number of Columns you want
• A Start number to count
• A Step for incrementing

17- SEQUENCE function to Decrement

I can use the SEQUENCE function to decrement, as follows:

=SEQUENCE(20,1,20,-1)

18- SEQUENCE function to Increment with pattern

I can use the SEQUENCE function to increment with a pattern, as follows:

=SEQUENCE (20,1,1,3)

that increments with a Step of 3

Finally, you can also create a column of incremental number by sending your data to Access. In Access, you have a data type AutoNumber that creates automatically an incremental number. You can also at any time send your numbered list back to Excel. These applications talk to each other very smoothly.

You can watch the video tutorial on incrementing numbers by clicking  here below: