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.
See also: What are the special characters in Excel 2010, 2013, 2016? How to write and use them easily
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.
How to recover unsaved or overwritten Excel files effectively: a sure-fire method
Knowing how to recover unsaved Excel files can be extremely helpful in cases when your computer unexpectedly shuts down or you accidentally click on “Don’t save” when Excel asks if you want to save your changes before closing. Follow this article to learn the step-by-step process of how to accomplish it.
5+ Effective Ways to Convert PDF to Excel Quickly, Useful Office Tricks
Knowing the ways to convert PDF files to Excel below will enable you to easily edit and add content inside. Office workers and students definitely must know this trick if they want to simplify their tasks.