Numbering in Excel is no longer a complicated operation but one of the most basic operations when working with Excel. However, numbering in Excel does not have to be done manually, but you will have a way to automatically number in Excel, saving more time and also showing professionalism when working. Below are some ways to number sequentially in Excel for you to choose the appropriate method.
1. Numbering in Excel with the mouse
This is a very simple way to help you type numbers automatically in Excel with just one mouse click, and you can type as many numbers as you want.
First you will Number the first 2 boxesLater delineate the first 2 cells there and see the plus symbol in the bottom right corner of the blacked out area.
Then you just need scroll down Go to the serial number you want to use and you're done.
In case you Odd-even numbering then do the same. Enter the number for the first 2 boxes according to the rule you want to play, then highlight those 2 boxes and drag them down. The result of the automatically entered serial number still follows the order principle in the first 2 cells.
2. How to number Excel numbers using formulas
We will use it formula =First order cell position + n according to the numbering rule you want. If numbering is consecutive, n=1 will be set.
For example, according to the image below, there will be the formula =C1+3 to automatically number in Excel according to the formula with n=3. Press Enter and the second cell is 4.
Now you will Drag from the 2nd box to the remaining boxes to number them according to the rules you have established.
3. Use the SEQUENCE function to number in Excel
When you use the SEQUENCE function in Excel to number sequentially, it is very simple and not complicated. Note, the SEQUENCE Excel function only applies to Office 365 and Excel online versions.
You just need to follow along formula =SEQUENCE(Number of rows to number) then press Enter.
Immediately the row is returned with the value you entered for the SEQUENCE function in Excel.
4. How to use the ROW function for sequential numbering in Excel
To use the ROW function for sequential numbering in Excel, you will need to follow these steps ROW() formula. For example, in the image below, you will start entering number 1 from row number 5, then you will need to enter the formula =ROW() – 4. The result of cell number 1 will be displayed.
Now you just need to scroll down from number 1 below to enter the next number.
When using the ROW numbering function, if rows are added or deleted, the numbering will be automatically adjusted.
5. How to number Excel sequentially except for blank rows
There will be cases where you need to number the Excel data table, when there are blank rows if we do not delete Excel blank lines. Then you will have to implement the SUBTOTAL function nested with the IF function.
We will do this with the data table below when we need sequential numbering, but minus the blank lines.
We have the box function syntax as =IF(logical_test; [value_if_true]; [value_if_false]; SUBTOTAL(function_num; ref1;…). In there:
- logical_test: Condition of IF function.
- [value_if_true]: Return value if condition is true.
- [value_if_false]: Return value if condition is false.
- function_num: The function is understood as a numeric value (Enter 3 is equivalent to the COUNTA function).
- ref1;…: Area containing sequence numbers.
Applying to the table, we will have the formula =IF(B2=””,””,SUBTOTAL(3,$B$2:B2)). Where SUBTOTAL(3,$B$2:B2)) means:
- 3 is function_num which is the COUNTA function in SUBTOTAL that counts the number of non-empty cells.
- $B$2:B2 is a numbered area, you must use the $ value to lock the first cell.
IF(B2=””,””,SUBTOTAL(3,$B$2:B2)) means:
- B2=””,”” If cell B2 is empty, it will not be numbered.
- SUBTOTAL(3,$B$2:B2)) If cell B2 has a value, the SUBTOTAL function will be used.
Now at first order cell A2 Enter the formula =IF(B2=””,””,SUBTOTAL(3,$B$2:B2)) and then press Enter.
Immediately after that, the number 1 will be displayed. You will continue to drag to the remaining cells to number them automatically in Excel.
As a result, we will see that the Excel numbers are ignored and empty Excel lines do not display numbers, and the cells with data are given the next number.
So you have many ways to number in Excel. Depending on how the data table you work with, we choose the appropriate numbering style.
6. Numbering in Excel using Fill
Step 1:
First, you enter all the data into the table, leaving the columns that need to be numbered sequentially. In the example table below, there will be sequential numbers in the STT column. Next enter the first cell as 1 in the STT column, then proceed Highlight the area that needs to be numbered starting from 1 as shown below.
Step 2:
Look up at the Ribbon and find it Home tab. Then look forward Editing section select Fill then select next Series…
Step 3:
In the Series dialog box that appears, users take turns customizing the following items:
- Series in: check Rows if numbered in rows, or Columns if numbered in columns.
- Type: Here there will be a number of options for sequential numbering including Linear to number sequentially in ascending style, Growth sequential numbering to be progressive, Date serial number to date, AutoFill to automatically fill in sequential numbers. .
- Step value: choose the distance between 2 ordinal numbers in the column.
- Stop value: number the line to which position.
According to the requirements of the article, section Series print I will check it Columns to number sequentially by column or section Type select Linear ascending number type, Step value Here will enter is 1 and Stop value The final position is 5. Press OK to save the settings.
Finally you get the result that the cells are numbered automatically.
7. Numbering in Excel using Fill Series
Another way is also used Fill Series feature on Excelbut should be applied in cases where you do not need to customize the numbering much like the method above. This method requires dragging the sequential number column. In the STT column, enter the first cell as 1 and then drag down the remaining cells. Thus, the remaining order cells will all be 1.
Click here plus icon as shown below to open the item Auto Fills Optionsthen select Fill Series. The order number will immediately be automatically filled in to the last order number position.
Instead of dragging the mouse to the position where the serial number is needed, through the Fill Series feature, the serial number will be entered automatically. Besides, users also have an additional custom number when numbering in columns such as sequential numbering, numbering for columns or rows,…
8. How to number Excel numbers with letters
In addition to the usual Excel letter numbering, many data also require you to number with letters. For example, you will see Excel numbering with letters like A1, A2, A3, etc. If used according to normal operations, it will not display the order with letters you want, but must have a few more. other operations.
Method 1: Number Excel numbers with letters via Format Cells
Step 1:
User first Number the data cells as usual using the above methods. Next, highlight the entire number box and press Right-click and select Format Cells in the displayed list.
Step 2:
Show new dialog box, user Click the Custom tabLater Type “A”# in the Type box Click OK to finish. If you add more letters to the numbered box, enter them in quotation marks.
Step 3:
As a result, we will get a series of numbered boxes with text as shown below.
You continue to scroll down to continue typing the serial number box with other letters for the data.
Method 2: Use the sequential numbering formula with Excel letters
First, you will manually type the order box with the letter B1 in cell E2 as shown below. Note that you cannot type the order cell in the first cell in each column. Then enter the formula =”B”&RIGHT(E2,LEN(E2)-1)+1. In there:
- Function RIGHT(text,[num_chars]).
- Text: The text string containing the characters you want to extract.
- Num_chars: Specify the number of characters you want the RIGHT function to extract.
- LEN function (text)
- Text: is the data cell you want to count
Then press Enter to display the results.
Then you will also get the next numbered box. We just need to drag from this cell down to the remaining cells to get a sequential numbering sequence with letters as shown below.