Musiha Mahfuza Mukta
When you have lots of data, in that case, if you need to get the row Number of a certain Cell then it is not a suitable way to find that row number manually. Because in Excel there are some easiest ways to get the row number of any cell. Thus, I will demonstrate how to get the row number of a current cell in Excel in this article.
How to Get Row Number of Current Cell in Excel: 4 Methods
Here, I’m going to explain to you four methods of how to get the Row Number of a Current cell in Excel. For your better understanding, I’m going to use an example of the following dataset. Which contains two columns. These are Month and Sales.
1. Use of ROW Function to Get Row Number of Current Cell
Here, I will use the ROW function to get the Row Number of a cell in Excel. Basically, the ROW function is a built-in function in Excel. In addition, you can easily find out the Row number with this function without inserting anything else as input data.
Now, suppose I want to get the Row number of the cell which contains a value of $1,000. The steps are given below.
Steps:
- Firstly, you should select a different cell D5 where you want to keep the Row Number. Here, the cell, where you want to keep the Row Number, should be horizontally adjacent to the target cell.
- Secondly, use the following formula in the D5 cell.
=ROW()Here, in this formula, the ROW function will return the Row number of the cell where you will write this formula.
- Subsequently, press ENTER to get the Row Number.
- After that, you may drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D11.
Finally, you will get all the corresponding Row numbers.
2. Employing ROW Function with Reference to Get Row Number of Current Cell
Here, I will use the ROW function with a cell reference to get the Row Number of a cell in Excel. Basically, the ROW function is a built-in function in Excel. In addition, you can easily find out the Row number with this function using the cell as a reference.
Now, suppose I want to get the Row number of the cell which contains a value $1,000. The steps are given below.
Steps:
- Firstly, you should select a different cell D5 where you want to keep the Row Number. Here, the cell, where you want to keep the Row Number, should be horizontally adjacent to the target cell.
- Secondly, use the following formula in the D5 cell.
=ROW(C5)In this formula, the ROW function will return the Row number of the reference cell. Here, C5 is the reference. Basically, the ROW function will return the numerical number of the reference as a Row number.
- Subsequently, press ENTER to get the Row Number.
- After that, you may double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D11.
Lastly, you will get all the corresponding Row numbers.
Similar Readings
- How to Use Range with Variable Row Number in Excel
- Excel VBA: Return Row Number of Value
- Find String in Column and Return Row Number in Excel
- How to Find Row Number Using VBA in Excel
3. Using Combination of Functions to Get Row Number of Current Cell
In this section, I will use a combination of functions for getting the Row number of Current cell in Excel. I’m going to use ROW, INDEX, and MATCH functions for the combination. Now, suppose I want to get the Row number of the cell which contains the value of January. The steps are given below.
Steps:
- Firstly, you should select a different cell D5 where you want to keep the Row Number.
- Secondly, use the following formula in the D5 cell.
=ROW(INDEX($B$5:$B$11, MATCH("January", $B$5:$B$11, 0)))- Thirdly, press ENTER to get the Row Number.
Formula Breakdown
- Here, the MATCH function will return the relative position of a cell value in a given array.
- Firstly, my Array is $B$5:$B$11. Here, the Dollar ($) sign denotes that the array is fixed.
- MATCH(“January”, $B$5:$B$11, 0)—> becomes 1.
- Secondly, the INDEX function will return the cell value from a given array using a specified position.
- INDEX($B$5:$B$11, 1)—> turns January.
- Finally, the ROW function will take January as reference.
- Output: 5.
- Now, to know the Row number holding another cell value you should insert that cell value within Inverted Comma.
Like, I want to know the Row number of the cell which contains the value of February.
- So, use the following formula in a different cell D6.
=ROW(INDEX($B$5:$B$11, MATCH("February", $B$5:$B$11, 0)))- Then, press ENTER to get the Row Number.
Similarly. do the same for the other cell values.
4. Applying VBA to Get Row Number of Current Cell in Excel
You can employ the VBA code to get the Row Number of a cell in Excel. The steps are given below.
Steps:
- Firstly, from the Developer tab >> you have to choose the Visual Basic command.
Here, you must save the Excel file as Excel Macro-Enabled Workbook (*xlsm).
- Now, from the Insert tab >> you have to select Module.
- After that, write down the following code in the Module1.
Sub Row_No_of_Current_Cell()
Dim my_Row_No As String
my_Row_No = ActiveCell.Row
MsgBox my_Row_No
End Sub
Code Breakdown
- Here, I have created a Sub Procedure named Row_No_of_Current_Cell.
- Also, I used the variable my_Row_No as a String.
- Then, I used Row to count the Row number.
- Finally, MsgBox will show the result.
- Now, you have to save the code.
- Then, you need to go to the Excel worksheet.
- After that, select the cell for which you want to get the Row number.
- Then, from the Developer tab >> you need to choose Macros.
- At this time, select Row_No_of_Current_Cell from the Macro name.
- Then, click on Run.
At this time, a dialog box named Microsoft Excel will appear. Where you will see the corresponding Row number.
Read More: Excel VBA: Find String in Column and Return Row Number
Use of ROWS Function to Get Number of Rows in a Range of Cells
There is another function in Excel to count the Total Rows number inside a given array. Which is the ROWS function. So, you can use the ROWS function to get the Total Row Numbers of an array in Excel. The steps are given below.
Steps:
- Firstly, you should select a different cell where you want to keep the Total Row Numbers. Here, I have merged the cells D5:D11.
- Secondly, you need to use the corresponding formula in the D5:D11 cells.
=ROWS(B5:B11)Here, in this formula, the ROWS function will return the Total Row numbers of the given array. Here, the data range B5:B11 is the array.
- Subsequently, press ENTER to get the Row Number.
Finally, you will see the result.
Read More: How to Get Row Number from Range with Excel VBA
💬 Things to Remember
- The 1st method is the easiest one. With that, you will easily get the Row number of the cell exactly where you will write down the formula.
- Furthermore, suppose you know the cell value and you want to get the Row number of that cell. In that case, you should use method 3.
- In addition, you may use the VBA code (method 4) because with this method you can get the Row number of a selected cell immediately.
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 4 methods to get the row number of a current cell in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.
