Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education


Auto-number Rows or Cells using formula

Automatic Numbering of Rows or Cells by using Formulas in Excel

The super-simple automatic numbering of rows or cells in Excel using the ROW function in Excel.
If you enter the formula =ROW(A1) in any cell and copy it down Excel will return a sequence of numbers starting with number one. So this is a quick creation of a series of sequential numbers. If you enter into any cell of greater than 7 formula =ROW(A7) then Excel will return the number 7 and be copying down a series of numbers to continue ascending. If you omit the address of the cell is in function, then the Excel return the number of rows, in which there is formula =ROW()

If you want to start numbering, with a minus number, eg. from -10 to + 10 then the first Excel Cell A1, type =ROW()-11, and copy down. Now you have the numbering as the number line that begins -10, -9, -8, ... 0 ... 8, 9, 10 ...

Excel function ROW is often used as a substitute for the specific argument in another function in the formula if the given formula uses the number of rows as the argument. Here are some examples

In the picture below note 4 columns in which he presented a series of automatic numbering of rows of the numbers. In cell H2, I2, J2, K2 is a condition that is the number of rows as they are to be numbered. This requirement is not necessary, instead of the very formulas can enter the respective number or value. Note that the numbering is started in different rows and therefore the formula with the desired sequence of the numbers. Below the image, see the formula for columns A, C, E and G.

How to automatically by a formula specified numbered rows or cells in Excel

Column A.


Copy the formula down to the desired row. You notice in the above formula that is used ROW function as an argument in the function IF the condition number in cell H2. Instead of absolute cell $H$2 can enter the number 10. Explanation formula looks like this: If the number of rows in which this formula is less than or equal to the number in cell H2, Excel will return the number of rows in which there is this formula, if not a requirement fulfilled the result will be empty.

Column B.


In columns, C and E are two similar formulas that can return serial number range which contains a point after the number. Then this cell is formatted as text. You notice in the above formula by using the concatenate function, added the dot after the number. This formula is located in cell C6 but begins a series of sequential numbers from 1 because the function used ROW(C1) in the first logical argument of the IF function. The same ROW(C1) function is used as an argument to the function concatenate which is again the second argument of the IF function. If you want to know more about how to add Excel functions within other Excel functions, see the tutorial on the respective link.

Because the used function ROW(C1), when copying down Excel returns a series ordinal numbers from 1 upward increase in numbers no matter what we first enter the formula in cell C6.

Column E.


If you look at the formula in column C, then you will understand why this formula in column E can be entered only in the cell that is in row one (1). So, here inside the function ROW() we do not have defined the argument that the address of the cell. If you want to avoid the point (dot) after the number then simply remove the concatenate function and leave only the ROW function so the formula would look like this as follows: =IF(ROW()<=$J$2;ROW();"")

Column F.

In cell G9 entered the first number of the numbering
In cell G10 entered a formula is copied down


This formula I am not going to explain, because I believe that you understood and if not look arguments Excel IF function.

Automatic numbering of non-adjacent rows or cells in Excel

If you need number rows or cells that are nonadjacent then you can use this example as a guideline for solving your problems.

Column A.

In cell A2 of the formula =IF(TRIM(B2)<>"";COUNTA($B$1:B2)-1&".";"") which is to be copied down to the desired row. This formula will make automatic numbering a succession of ordinal numbers of nonadjacent rows or non-adjacent cells that contain any data. Of course if you want to avoid a point after the number then remove this part of the formula & "."

Column D.

Cell D2 contains the formula =COUNTA(E$2:E2) you need to be copied down to the desired row. After copying the formula you need to wipe off excess located in cells D4, D11, D12, etc.. Cells in each group enter the required information. Here the condition filled cell in column E. To avoid confusing for you, text 'Company 1, 2 and 3' are found in the cells of the column D but they are merged with the cells in column E which does not affect the occupancy column E.

How to numbered in non-adjacent cells using a formula in Excel

If you want to learn quickly copy formulas to hundreds or thousands of rows then look tutorial Quickly copy formulas in Excel.