CELL Function

Get information about a cell

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.

 

Formula

=CELL(info_type, [reference])

 

The CELL function uses the following arguments:

  1. 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:

 

Info_typeDescription
AddressIt will return the address of the first cell in a reference as text.
ColIt will return the column number of the first cell in a reference.
ColorIt will return the value 1 if the first cell in a reference is formatted using color for negative values, or zero if not.
ContentsIt will return the value of the upper-left cell in a reference. Formulas are not returned. Instead, the result of the formula is returned.
FilenameIt 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.
FormatIt will return a code that corresponds to the number format of the cell.
ParenthesesIt will return 1 if the first cell in the reference is formatted with parentheses and 0 if not.
PrefixIt will return a text value corresponding to the 'label prefix' of the cell.
ProtectIt will return 1 if the cell is locked, otherwise, 0.
RowIt will return the row number of a cell.
TypeIt 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).
WidthIt will return the column’s width.

 

For the info_type format, the number format codes are below:

 

Format code returnedFormat code meaning
GGeneral
F00
,0#,##0
F20
,2#,##0.00
C0$#,##0_);($#,##0)
C0-$#,##0_);[Red]($#,##0)
C2$#,##0.00_);($#,##0.00)
C2-$#,##0.00_);[Red]($#,##0.00)
P00%
P20.00%
S20.00E+00
G# ?/? or # ??/??
D1d-mmm-yy or dd-mmm-yy
D2d-mmm or dd-mmm
D3mmm-yy
D4m/d/yy or m/d/yy h:mm or mm/dd/yy
D5Mm/dd
D6h:mm:ss AM/PM
D7h:mm AM/PM
D8h:mm:ss

 

  1. 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.

 

CELL Function

 

The formula to use will be:

 

CELL Function - Example 1

 

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:

 

CELL Function - Example 1a

 

Things to remember about the CELL Function

  1. #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.
  1. #NAME? error – Occurs when the value arguments are text values that are not enclosed in quotes and are not valid cell references.

 

Click here to download the sample Excel file

 

Additional resources

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:

Free Excel Tutorial

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!