How to use the CELL function

Author: Oscar Cronquist Article last updated on April 08, 2022

The CELL function gets information about the formatting, location, or the contents of a cell.

The formula example above in cell C3 returns a value determined by the contents of the cell.

Return value	Cell content
l	text
b	blank
v	anything else

Formula in cell C3:

=CELL("type",B3)

CELL function Syntax
CELL function Arguments
CELL function - example
CELL function - How to find out which formatting a specific cell has
Calculate the intersection of two cell ranges
1. VBA Code
2. Where to put the code?

1. Cell Function Syntax

CELL(info_type, [reference])

2. Cell Function Arguments

info_type

Required. A value that determines the type of cell information the function returns.

List of Info_type arguments and what they do.

info_type	Returns
"address"	Reference of the first cell in reference, as text.
"col"	Column number of the cell in reference.
"color"	The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).
"contents"	Value of the upper-left cell in reference; not a formula.
"filename"	Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved.
"format"	Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns "-" at the end of the text value if the cell is formatted in color for negative values. Returns "()" at the end of the text value if the cell is formatted with parentheses for positive or all values.
"parentheses"	The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0.
"prefix"	Text value corresponding to the "label prefix" of the cell. Returns single quotation mark (') if the cell contains left-aligned text, double quotation mark (") if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text ("") if the cell contains anything else.
"protect"	The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked.
"row"	Row number of the cell in reference.
"type"	Text value corresponding to the type of data in the cell. Returns "b" for blank if the cell is empty, "l" for label if the cell contains a text constant, and "v" for value if the cell contains anything else.
"width"	Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.

The second argument in the CELL function is [Reference].

[Reference]

Optional. The cell that you want information about. If omitted, the information specified in the Info_type argument is returned for the last cell that was changed.

Info_type "format" returns the following codes for different cell formatting settings.

Excel format	Returns
General	"G"
0	"F0"
#,##0	",0"
0.00	"F2"
#,##0.00	",2"
$#,##0_);($#,##0)	"C0"
$#,##0_);[Red]($#,##0)	"C0-"
$#,##0.00_);($#,##0.00)	"C2"
$#,##0.00_);[Red]($#,##0.00)	"C2-"
0%	"P0"
0.00%	"P2"
0.00E+00	"S2"
# ?/? or # ??/??	"G"
m/d/yy or m/d/yy h:mm or mm/dd/yy	"D4"
d-mmm-yy or dd-mmm-yy	"D1"
d-mmm or dd-mmm	"D2"
mmm-yy	"D3"
mm/dd	"D5"
h:mm AM/PM	"D7"
h:mm:ss AM/PM	"D6"
h:mm	"D9"
h:mm:ss	"D8"

3. CELL function - example

CELL function example 1

This example demonstrates how to return a type value using the CELL function. The type value tells you if a cell is empty, contains text, or a number.

Formula in cell C3:

=CELL("type",B3)

CELL(info_type, [reference])

info_type - type
[reference] - B3

"b" - blank, cell is empty.
"l" - label (text)
"v" - value (number)

4. How to identify formatting of a specific cell

CELL function identify formatting 1

The image above shows the CELL function calculating a code based on a cell reference, in this example, cell B3.

Formula in cell C3:

=CELL("format",B3)

CELL(info_type, [reference])

You can verify the CELL function out by selecting cell B3. Press CTRL + 1, a dialog box appears.

CELL function format cells dialog box

The category is "General" which is the same as "G". I will describe below how to get the formatting code based ont the output from the CELL function.

CELL function translate formatting

The formula in cell D3 translates the code returned from the CELL function to something easier to understand. The table in cell range B9:C30 is used to match the code to the correct format.

Formula in cell D3:

=TEXTJOIN(", ",TRUE,FILTER($B$9:$B$30,$C$9:$C$30=C3))

4.1 Explaining formula in cell D3

Step 1 - Compare values to condition

The equal sign compares values in an Excel formula, the result is aboolean value TRUE or FALSE.

$C$9:$C$30=C3

becomes

{"G"; "F0"; ",0"; "F2"; ",2"; "C0"; "C0-"; "C2"; "C2-"; "P0"; "P2"; "S2"; "G"; "D4"; "D1"; "D2"; "D3"; "D5"; "D7"; "D6"; "D9"; "D8"}="G"

and returns

{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

Step 2 - Filter matching values

The FILTER function extract values/rows based on a condition or criteria.

FILTER(array, include, [if_empty])

FILTER($B$9:$B$30,$C$9:$C$30=C3)

becomes

FILTER({"General"; 0; "#,##0"; "0.000"; "#,##0.00"; "$#,##0_); ($#,##0)"; "$#,##0_); [Red]($#,##0)"; "$#,##0.00_); ($#,##0.00)"; "$#,##0.00_); [Red]($#,##0.00)"; "0%"; "0.00%"; "0.00E+00"; "# ?/? or # ??/??"; "m/d/yy or m/d/yy h:mm or mm/dd/yy"; "d-mmm-yy or dd-mmm-yy"; "d-mmm or dd-mmm"; "mmm-yy"; "mm/dd"; "h:mm AM/PM"; "h:mm:ss AM/PM"; "h:mm"; "h:mm:ss"}, {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})

and returns

{"General"; "# ?/? or # ??/??"}

Step 3 - Concatenate values

The TEXTJOIN function allows you to combine text strings from multiple cell ranges and also use delimiting characters.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(", ",TRUE,FILTER($B$9:$B$30,$C$9:$C$30=C3))

becomes

TEXTJOIN(", ",TRUE, {"General"; "# ?/? or # ??/??"})

and returns "General, # ?/? or # ??/??" in cell D3.

5. Calculate the intersection of two cell ranges

CELL function intersecting cell references

The CELL function is able to return the cell reference from a cell range based on two intersecting cell references.

Formula in cell B13:

=CELL("address",(B2:C7 C5:E10))

The space character can be used as an intersect operator in Excel. The formula in cell B13 returns the top-left cell address of the intersecting cell references.

Intersecting cell reference VBA macro

The image above shows how to calculate the address of two intersecting cell ranges using a VBA macro. The message box shows the address of the entire cell range.

4.1 VBA code

Sub TwoCellRanges()

MsgBox "Intersecting cell range: " &amp; Application.Intersect(Range("B2:C7"), Range("C5:E10")).Address

End Sub

4.2 Where to put the code?

Intersecting cell references

Copy above VBA code.
Press shortcut keys Alt + F11 to open the Visual Basic Editor.
Press with mouse on "Insert" on the menu.
Press with left mouse button on "Module".
Paste code to window.
Exit VB Editor and return to Excel.

Note, save your workbook with file extension *.xlsm (macro-enabled workbook) to keep the code attached to your workbook.

'CELL' Function examples

The following article has a formula that contains the CELL function.

Find last value in a column
This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]

Functions in 'Information' category

The CELL function function is one of many functions in the 'Information' category.

CELL function

Gets information about the formatting, location, or the contents of a cell.

ERROR.TYPE function

Returns a number that represents one of the error values in Excel.

INFO function

Returns information about the current operating environment, file path, number of active worksheets, Excel version etc.

ISBLANK function

Returns TRUE if the argument is an empty cell, returns FALSE if not.

ISERR function

Returns TRUE if a cell returns an error, except error value #N/A.

ISERROR function

Returns TRUE if a cell contains an error.

ISFORMULA function

Returns TRUE if a cell contains a formula, FALSE if text, number or boolean value.

ISLOGICAL function

Returns TRUE if value is boolean. A boolean value is either TRUE or FALSE.

ISNA function

Returns TRUE if value is a #N/A error.

ISNONTEXT function

Returns TRUE if value is not text, also returns TRUE if cell is empty.

ISNUMBER function

Checks if a value is a number, returns TRUE or FALSE.

ISODD function

Returns TRUE if a cell contains an odd number, FALSE if even number.

ISREF function

Returns TRUE if value refers to a reference.

ISTEXT function

Returns TRUE if argument is text.

N function

Returns a value converted into a number.

NA function

Returns the error value #N/A meaning "value is not available".

SHEET function

Returns the sheet number of the cell reference sheet.

SHEETS function

Returns the number of sheets in a reference.

TYPE function

Use TYPE to find out what type of data is returned by a function or formula.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

How to use the CELL function

Table of Contents

1. Cell Function Syntax

2. Cell Function Arguments

3. CELL function - example

4. How to identify formatting of a specific cell

4.1 Explaining formula in cell D3

Step 1 - Compare values to condition

Step 2 - Filter matching values

Step 3 - Concatenate values

5. Calculate the intersection of two cell ranges

4.1 VBA code

4.2 Where to put the code?

'CELL' Function examples

Functions in 'Information' category

Leave a Reply

How to comment