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.

Similar Readings

• VBA to Convert Column Number to Letter in Excel (3 Methods)
• How to Return Column Number of Match in Excel (5 Useful Ways)

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.

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: Set Range by Row and Column Number (3 Examples)

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

• How to Change Column Name from ABC to 1 2 3 in Excel
• [Fixed] Excel Column Numbers Instead of Letters (2 Solutions)
• VBA to Use Range Based on Column Number in Excel (4 Methods)
• How to Convert Column Number to Letter in Excel (3 Ways)