The ADDRESS function in Google Sheets will return a cell reference as a string.
This is not a very well-known function, but when you work with spreadsheets long enough, you will find that it has its uses.
In this tutorial, I will show you how to use the ADDRESS function in Google Sheets
Table of Contents
ADDRESS Function Syntax
The ADDRESS function will return a cell reference according to the arguments entered into the function.
The syntax of ADDRESS is:
=ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])
- row – this is the row number of the cell reference
- column – this is the column number of the cell reference (column A is 1, B is 2, 3 is C, etc…)
- absolute_relative_mode – this is an optional argument that will be set to 1 by default. 1 is absolute row and column ($A$1), 2 is row absolute, and column relative (A$1), 3 is row relative and column absolute ($A1), and 4 is row and column relative (A1)
- use_a1_notation – this is an optional argument that is set to TRUE by default. If the argument is TRUE A1 notation will be used for the cell references. If it is false R1c1 notation will be used
- sheet – this is an optional argument that will be blank by default. This is a string with the name of the sheet that the address belongs to
Using the ADDRESS Function
Now that we have learned the syntax of this function, let’s take a look at how ADDRESS can be used in a spreadsheet:
Let’s break down each line of the formula so that we can understand why the result is what it is:
- =ADDRESS(1,1) – this formula results in $A$1 because it is returning the cell reference of the cell in the 1st row, and the 1st column. The formula does not specify absolute_relative_mode so it is set to 1 by default which is row and column absolute
- =ADDRESS(1,2,2) – this formula results in B$1 because it is returning the 1st row and the 2nd column. The third argument in the function is 2 which means row absolute and column relative
- =ADDRESS(1,3,3) – this formula results in $C1 because it is returning the 1st row and the 3rd column. The third argument in the function is 3, which means row relative and column absolute
- =ADDRESS(1,4,4) – this formula results in D1 because it is returning the 1st row and 4th column. The third argument in the function (4) means row and column relative
- =ADDRESS(2,3) – this formula results in $C$2 because it is returning the 2nd row and 3rd column. It is returning an absolute row and column because the third argument is set to 1 by default
Closing Thoughts
In this tutorial, I have covered the basics of this function, so if you have followed along with the whole thing you should have an understanding of how this function works.
This function begins to become useful when you start to combine it with other functions. There are many neat things you can do, but that is beyond the scope of this post.
I will cover so me of the cool things you can do with ADDRESS at a later time.
More Google Sheets Tutorials:
How to Get the Column Letter