Ace My Paper - get your Excel tasks done by experts. - makes your homework shine.


Excel tutorials

Automatic Numbering of Rows or Cells by using Formulas in Excel

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 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 ROW function is often used as a substitute for 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 IF function 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 an 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 E column can be entered only in the cell that is in row one (1). So, here inside the ROW() function 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 numbering rows or cells that are non adjacent 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 non adjacent 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 numbering 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.

Post related for Quick Numbering in Excel is next Quickly Numbering Rows or Cells.