What is the CELL Function?
The CELL function is an Information function, that will extract information about a cells location, contents or formatting. The CELL function takes two arguments, one that determines the type of information to be extracted, and the other is which cell it will be checking.
As a financial analyst, the CELL function is useful as it can help verify if a cell contains a numeric value instead of text before we perform a calculation on it. If we import data from an external source, we can verify that all cells with numbers are used for calculations.
The CELL function uses the following arguments:
- Info_type (required argument) – It is a text value specifying the type of cell information that we want to return. It can either be of the following:
|Address||It will return the address of the first cell in a reference as text.|
|Col||It will return the column number of the first cell in a reference.|
|Color||It will return the value 1 if the first cell in a reference is formatted using color for negative values, or zero if not.|
|Contents||It will return the value of the upper-left cell in a reference. Formulas are not returned. Instead, the result of the formula is returned.|
|Filename||It will return the file name and full path as text. If the worksheet that contains reference has not yet been saved, an empty string is returned.|
|Format||It will return a code that corresponds to the number format of the cell.|
|Parentheses||It will return 1 if the first cell in the reference is formatted with parentheses and 0 if not.|
|Prefix||It will return a text value corresponding to the 'label prefix' of the cell.|
|Protect||It will return 1 if the cell is locked, otherwise, 0.|
|Row||It will return the row number of a cell.|
|Type||It will return a text value corresponding to the type of data in the cell. It can either be "b" for blank (or empty); "l" for label (i.e. text constant), or "v" for value (for any other data type).|
|Width||It will return the column’s width.|
For the info_type format, the number format codes are below:
|Format code returned||Format code meaning|
|G||# ?/? or # ??/??|
|D1||d-mmm-yy or dd-mmm-yy|
|D2||d-mmm or dd-mmm|
|D4||m/d/yy or m/d/yy h:mm or mm/dd/yy|
- Reference (optional argument) – It is the cell with the information to be returned for.
- If a range of cells is supplied, the returned information relates to the top left cell of the range.
- If the reference is omitted, the returned information relates to the last cell that was changed.
How to use the CELL function in Excel?
To understand the uses of the CELL function, let us consider a few examples:
Example – Finding a certain value
Suppose we are given the following data on stock price highs and lows. To get the address of a lookup result obtained with the INDEX function, we can use the CELL function.
The formula to use will be:
The INDEX function will display the value of a cell at a given index, but the function underneath it actually returns a reference. So, by wrapping INDEX into the ADDRESS function, we can see the address of the cell returned by the lookup.
We get the result below:
Things to remember about the CELL Function
- #VALUE! error – Occurs when the given info_type argument is not one of the recognized types.
- The given index_num is less than 1 or is greater than the given number of values.
- The given index_num argument is non-numeric.
- #NAME? error – Occurs when the value arguments are text values that are not enclosed in quotes and are not valid cell references.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources: