Technology

Update the 7 most efficient and fast Excel numbering functions in detail for 2023

Are you looking to use the ranking function in Excel to create spreadsheets but don't know how? This process is actually quite simple and the following article by FPT Shop will guide you through 7 ranking functions in Excel.

Administrator

26 December, 2023

199

A spreadsheet in Excel often contains many different types of information, in which the ordinal number of each row plays a very important role so that users can search, differentiate, and sort according to their preferences. To learn how to number rows in Excel, let’s explore in the article below with FPT Shop.

How to number rows in Excel using the mouse

Step 1: In the Excel sheet, you number 1, 2 in the first two cells. After that, you select these 2 cells and place the mouse at the bottom right corner. You will see a black “+” sign. At this point, you just need to drag down the cells below or double click on the “+” icon.

Step 2: Check the result

Note: If you enter values at B1 and B2 as 1 and 2 respectively, Excel will understand that you want to number in increasing order. If you enter 1 and 3, Excel will understand that the ordinal number will be calculated in increasing odd number

How to use the ROW function to number rows in Excel

These are ways to automatically fill the ordinal number without having to enter the first 2 values. You only need to enter 1 cell and the 2nd cell will be replaced by functions in Excel

2.1. Numbering rows in Excel using formulas

Step 1: Enter the first ordinal number in the worksheet, here you choose number 1 and cell B4. Then, at cell B2, you type the formula: =B4+1 and press Enter.

Step 2: Click on the second cell and place the mouse at the bottom right corner. You will see a black “+” sign. At this point, you just need to click the left mouse button on the “+” icon and drag down to number the remaining cells.

Step 3: The result is as follows:

2.2. ROW Function to number rows in Excel

Step 1: In the Excel sheet, you enter the ROW function in the first cell with the following syntax: = ROW () – (ordinal number of the cell to be filled – 1) and press Enter. In the example below, the ordinal number of the cell to be filled is 4, so the used ROW functions have the following syntax: = ROW (3)

Like the example below, the ordinal number of the cell to be filled is 4, so the ROW function used is: = ROW () – 3.

Step 2: You place the mouse at the bottom right corner of the cell, you will see a black “+” sign. At this point, you just need to use the left mouse button to drag down the cells below to number the cells.

Step 3: This is the result

2.3. SEQUENCE function to number rows in Excel

Step 1: You enter the “= SEQUENCE (Number of rows to be numbered)” function in the first cell in Excel, and then press Enter. In the example below, the Excel sheet has 7 rows, so the necessary formula to enter is “= SEQUENCE (7)”.

Step 2: The Excel sheet will be quickly and easily numbered.

2.4. Using the SUBTOTAL function nested with the IF function to number rows in Excel

Using the SUBTOTAL function with the IF function will help you number only rows with data in Excel. Empty rows without data will be left blank, not numbered.

The formula for the SUBTOTAL function nested with the IF function is as follows:

= IF (logical_test; [value_if_true]; [value_if_false]; SUBTOTAL (function_num; ref1; …))

Where:

Logical_test: The condition of the IF function.
Value_if_true: The value to be returned if the condition is true.
Value_if_false: The value to be returned if the condition is false.
Function_num: The value of the function (here you use 103, equivalent to the COUNTA function).
Ref1: The range contains the ordinal numbers that you want to fill.

For example, you have a data table as follows:

Step 1: You enter the formula as follows: = IF (D5 = “”; “”; SUBTOTAL (3; $ D $ 5: D5))

Logical_test: D5 = “”.
Value_if_true: “”.
Value_if_false: SUBTOTAL (3; D5: D5).
Function_num: 3.
Ref1: $ D $ 5: D5 (Note: You must lock the first value D5 with Command + T on Mac or F4 on Windows. If not, the function will be an error for the cells below).

If cell D5 is empty, the result returned is empty, otherwise the SUBTOTAL function is used.

Step 2: You place the mouse at the bottom right corner of the first cell, and you will see a black “+” sign. At this point, you just need to use the left mouse button to drag down the cells to fill in the ordinal numbers for the Excel sheet.

Step 3: Check the result

How to number rows in Excel using the Fill handle

This is a very fast and professional way to number rows in Excel. You can proceed as follows.

Step 1: Enter the number 1 in the first cell where you want to start numbering. Click on the Fill icon on the Home tab and choose Series

Step 2: A new window opens, you select the options as instructed in the illustrated image. According to the illustrated image, we will number in ascending order vertically and the maximum number is 10. After selecting, click OK to finish.

Step 3: The result after performing the settings in Fill/ Series

Conclusion

Above are simple ways and functions to number rows in Excel. Hope this article provides you with more useful information about how to number rows in Excel. After reading, please try it yourself on your home computer to see the result.