How To Use The Excel Functions
CELL And ADDRESS

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In this post, we are going to look at two different built-in functions in Microsoft Excel. First, we will look at CELL and then we will move onto ADDRESS.

We will first look at their syntax and then work through some examples.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

The CELL function

=CELL(info_type, [reference])

The CELL function returns information about a cell such as formatting, location, and contents.

The formula syntax requires the info_type argument. This argument is a text value that tells the function which type of information you want to return.

There is also the reference argument. It is an optional argument.

This argument is the cell that you want information about. It can also be a range of cells. In that case, the function will return information about the cell in the upper left corner.

If you omit the reference argument, the information for the last changed cell is returned.

There are 12 available information types you can use as the info_type argument.

info-types

The “format” info_type returns any of several potential results. These are all the possible CELL format codes:

format-codes

Let’s look at some of these info types used in a few different. We start with nine rows of data of different types.

data-types

Now we add our formulas.

cell-formulas

With this set of examples, you can see the varying information you can return about a cell.

Kasper Langmann, Co-founder of Spreadsheeto

The ADDRESS function

The ADDRESS function is a lookup type function. You can use it to create a cell address from some given row and column. The syntax is as follows:

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

  • row_num – This is the row number of the cell address. It is a required argument.
  • col_num – This is the column number of the cell address. It is also a required argument.
  • abs_num – This is the address type: absolute or relative. This is an optional argument. If you omit is, the result defaults to absolute. This can be any of the following values:
    • 1 – absolute row and absolute column reference. Example: $A$1
    • 2 – absolute row and relative column reference. Example: A$1
    • 3 – relative row and absolute column reference. Example: $A1
    • 4 – relative row and relative column reference. Example: A1
  • a1 – This is the reference style for the returned cell address: A1 or R1C1. This is an optional argument and the if omitted, the returned address defaults to A1. For example, ‘=ADDRESS(1, 1)’ returns ‘A1’, while ‘=ADDRESS(1, 1, 1)’ returns ‘$A$1’. Note that ‘=ADDRESS(1, 1, 4)’ also returns ‘A1’.
  • sheet – This is the name of the worksheet to return the cell address from. It is optional and defaults to the current sheet if omitted.

Now let’s dive into some examples of the ‘ADDRESS’ function.

address-function

In the preceding examples, we have created some columns. These are for each of the arguments of the ADDRESS function.

Then we have inserted number values into those columns to serve as those arguments.

Note the difference in the result of each formula. They depend on the arguments omitted or included as well as the values themselves.

Before we move onto something a bit more complex, let’s review.

Kasper Langmann, Co-founder of Spreadsheeto
cell-function

The CELL function returns information about a given cell. In this example, we get format information.

address-example

The ADDRESS function returns the cell address of the intersection of the row_num and col_num supplied to the formula.

Ideas for combining

We have seen how each of these two functions work on their own. Now we turn our attention to a simple example where we combine them.

cell-address

In this example, we have wrapped a formula using ADDRESS in a larger CELL formula. Note that we have also integrated a function we have not discussed: INDIRECT.

Recall from our previous example that =ADDRESS(5,2) returns the cell address $B$5.

This means that the reference argument in the CELL formula becomes =INDIRECT($B$5). This formula returns the contents of cell B5.

Knowing that, our formula now becomes =CELL(“format”, $B$5) which we already know returns ‘G’.

So, there is a quick breakdown of how the outcome of one formula becomes the argument in another.

Conclusion

Now you have seen how the CELL and ADDRESS functions work. They are simple functions but can be useful in their own right depending on the situation.

Now it’s your turn to consider ways to use these in your own work.

Kasper Langmann, Co-founder of Spreadsheeto