Over 2 million + professionals use CFI to learn accounting, financial analysis, modeling and more. Unlock the essentials of corporate finance with our free resources and get an exclusive sneak peek at the first module of each course.
Start Free
What is the CELL Function?
The CELL Function[1] is an Excel Information function that will extract information about a cell’s location, contents, or formatting. The CELL function takes two arguments, one that determines the type of information to be extracted and the other that 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:
Info_type (required argument) – This is a text value specifying the type of cell information that we want to return. It can either be of the following:
Info_type
Description
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 as shown below:
Format code returned
Format code meaning
G
General
F0
0
,0
#,##0
F2
0
,2
#,##0.00
C0
$#,##0_);($#,##0)
C0-
$#,##0_);[Red]($#,##0)
C2
$#,##0.00_);($#,##0.00)
C2-
$#,##0.00_);[Red]($#,##0.00)
P0
0%
P2
0.00%
S2
0.00E+00
G
# ?/? or # ??/??
D1
d-mmm-yy or dd-mmm-yy
D2
d-mmm or dd-mmm
D3
mmm-yy
D4
m/d/yy or m/d/yy h:mm or mm/dd/yy
D5
Mm/dd
D6
h:mm:ss AM/PM
D7
h:mm AM/PM
D8
h:mm:ss
Reference (optional argument) – This 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’s 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
#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 CFI resources:
To master the art of Excel, check out CFI’s 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 Excel Crash Course now to take your career to the next level and move up the ladder!
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.