Get information about a cell
Get information about a cell
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:
|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|
To understand the uses of the CELL function, let us consider a few examples:
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:
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:
To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!