How to Reference Cell by Row and Column Number in Excel (4 Methods)

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

While using the ADDRESS function within the INDIRECT function, we can reference a cell by row and column number to get a value.

Steps:

• Activate Cell C13.
• Type the following formula in it-

• Finally, just hit the Enter button to get the output.

⏬ Formula Breakdown:

➥ ADDRESS(C11,C12)
The ADDRESS function will return the default cell reference for row number 8 and column number 2. So it will return as-
“$B$8”

➥ INDIRECT(ADDRESS(C11,C12))
Finally, the INDIRECT function will return the value of that cell according to the cell reference and that is-
“Watch”

Read More: Excel VBA: Get Row and Column Number from Cell Address (4 Methods)

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.

Steps:

• Write the following formula in Cell C13–

• Then to get the result, press the Enter button.

Read More: How to Return Cell Address Instead of Value in Excel (5 Ways)

Similar Readings

• 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.

Steps:

• In Cell C13, type the following formula–

• Later, hit the Enter button for the result.

Read More: How to Reference Text in Another Cell in Excel (14 Ways)

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.

Steps:

• 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.

Read More: Excel VBA Examples with Cell Reference by Row and Column Number

Conclusion

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.

Similar Readings

• 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)