CELL formula returns infomation about the various attributes of a cell such as address, color, width, column / row postitions etc.
CELL Formula Syntax in Excel
The CELL Formula has two parts:
CELL (attribute_type, cell_reference)
attribute_type specifies the attribute that you are trying to find. It can be color, column / row position, width, format or a number of other attributes (see below). Only one attribute can be specified in a single formula.
The cell_reference is the particular cell (or a range) for which the attribute value need to be returned. If a range is used, the attribute value for the top-left cell is returned.
Here is a list of all the values that can be specified as attribute values for the CELL Formula:
Example of a CELL Formula
Let’s look at an example of the CELL formula. Suppose we had a cell, say A10 and we wanted to know its row or column position, we could write something similar to:
=CELL(“row”, A10) or
Let’s look at some other examples:
As you can see, the CELL formula is pretty versatile. You can do a number of this with this formula that you would not be able to do otherwise such as return the format of the cell. It can even return the name and path of the workbook if its has been saved.
How to enter the CELL formula in an Excel Sheet
1. Select the cell in which you want to place the formula
2. Type the formula as =CELL(
3. Type the attribute name within quotes. For exmaple for address, type “address”
4. Press the comma key (,)
5. Move the cursor using the up-down or left-right arrow keys and take it to the cell for which you would like the attribute value to be returned.
6. Close the formula bracket by typing in ).
(In the end your formula should look something like this =CELL(“row”, A10) )
Possible Errors with the CELL Formula
The CELL formula can result in the following error values:
CELL #VALUE! Error
#VALUE! Error in CELL can result from wrongly specifying the attribute type. Say for example you wanted to write =CELL(“address”,A10) but instead erroneously entered =CELL(“addreess”,A10) (one extra ‘e’). As a result of the wrong attribute type, the CELL Formula will return a #VALUE! error value
CELL #NAME? Error
#NAME? Error in CELL can result from wrongly specifying address of the cell for which the attribute value needs to be returned. Say for example you wanted to write =CELL(“col”, AA134) but instead erroneously entered =CELL(“col”, AAAA134) (two extra A’s). Now the cell AAAA134 does not exist (not even in versions beyond Excel 2007 ) anywhere in the sheet and as a result the CELL function throws up the #NAME? error value. The #NAME? can also result from wrongly typing in the “CELL” in the formula, for example ‘CELLL’ instead of simply ‘CELL’.
You can know more about hiding errors in a worksheet here.
You can download an example of CELL formula here or click on the button below: