It’s quite common to use a cell reference in Excel. But except the traditional way, there are many ways by which you can reference cells by row and column number in Excel. This article will show you those 4 effective ways to reference cell by row and column number in Excel with sharp steps and vivid illustration.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
4 Ways to Reference Cell by Row and Column Number in Excel
Let’s get introduced to our dataset first which represents some fruits’ prices.
1. Use INDIRECT and ADDRESS Functions to Reference Cell by Row and Column Number
- Activate Cell C13.
- Type the following formula in it-
- Finally, just hit the Enter button to get the output.
⏬ Formula Breakdown:
The ADDRESS function will return the default cell reference for row number 8 and column number 2. So it will return as-
Finally, the INDIRECT function will return the value of that cell according to the cell reference and that is-
2. Use INDEX Function to Reference Cell by Row and Column Number
To get a value you can use the INDEX function to refer a cell by row and column number.
- Write the following formula in Cell C13–
- Then to get the result, press the Enter button.
- How to Return Cell Address of Match in Excel (3 Easy Ways)
- VBA to Convert Column Number to Letter in Excel (3 Methods)
- How to Return Column Number of Match in Excel (5 Useful Ways)
- What Is a Cell Address in Excel (Types with Example)
- Different Types of Cell References in Excel (With Examples)
3. Use Text Reference Within the INDIRECT Function to Reference Cell by Row and Column Number
Again we’ll use the INDIRECT function here. But here we’ll give input the row number and column number as text reference. Let’s see how to do it.
- In Cell C13, type the following formula–
=INDIRECT("R" & 8 & "C" & 2,FALSE)
- Later, hit the Enter button for the result.
4. Reference Cell by Row and Column Number Applying User Defined Function
In this method, we’ll do the task in a bit different way. First, we’ll make a User Defined Function named UseReference using VBA and then we’ll apply it to our sheet.
- Right-click on the sheet title.
- After that, select View Code from the Context menu.
A VBA window will open up. Or you can press Alt+F11 to open the VBA window directly.
- Now click Insert > Module.
- At this moment, type the following codes in the module-
Option Explicit Function UseReference(row As Integer, col As Integer) UseReference = ActiveSheet.Cells(row, col) End Function
- Then no need to run the codes, just minimize the VBA window and go back to your sheet.
Now have a look that our function is ready to use. We’ll have to give just the row number and column number and it will return the value according to that reference.
- To get the value from Cell B8, type the following formula in Cell C13-
- Finally, just press the Enter button to finish.
And have a look, we have got the correct value.
I hope the procedures described above will be good enough to reference cell by row and column number in Excel. Feel free to ask any question in the comment section and please give me feedback.
- Excel VBA: Set Range by Row and Column Number (3 Examples)
- How to Use Variable Row Number as Cell Reference in Excel
- [Fixed] Excel Column Numbers Instead of Letters (2 Solutions)
- How to Get Cell Value by Address in Excel (6 Simple Methods)
- Example of Cell Address in Excel (5 Ideal Cases)
- Relative and Absolute Cell Address in the Spreadsheet
- Cell Reference in Excel VBA (8 Examples)