How to use the CELL function

Author: Oscar Cronquist Article last updated on May 23, 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.

5.1 VBA code

Sub TwoCellRanges()

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

End Sub

5.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 this article

CELL TEXTJOIN TRUE FILTER

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.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

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

5.1 VBA code

5.2 Where to put the code?

'CELL' function examples

Functions in this article

Functions in 'Information' category

Excel function categories

Excel categories

Leave a Reply

How to comment