CELL Excel Function

Updated on December 26, 2023
Article bySuganthi S
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is CELL Function In Excel?

The CELL function in Excel is an inbuilt Information function. It accepts a cell reference and returns the specified cell’s cited information type, such as its location, content, or format.

Users can use the CELL function when they must analyze massive datasets for errors and the various facets of the information included in them.

For example, the following dataset contains one value in cell A2.

Cell Function Intro Example - output blank

Column B lists the information types we aim to gather about the value given in the source dataset and display them as output in column C.

Then, considering the CELL function definition cited before, we can apply the CELL() in each target cell and obtain the required information.

Cell Function Intro Example

We must find the address of the given source data to display the output as a cell reference in the target cell C2. So, using CELL function in Excel cell C2, with the references to cells containing the required information type and the source data value as inputs, will fetch us the required output. Thus, the cell C2 CELL functionreturns the absolute reference to the cell containing the source data value as the required address, $A$2.

Likewise, cell C3 CELL() returns the source data value column number 1, and in cell C4, the function returns the source data content, MS Excel.

Next, the cell C5 CELL() checks if the cell containing the source data value is formatted with parentheses for all values. And since it is not so, the function returns 0.

On the other hand, the CELL() in cells C6 and C7 determine the source data value row number and data type. Since the source data value is in the second row in the sheet and the data type is text, the function in the corresponding target cells returns 2 and I as the output.

Key Takeaways

  • The CELL function accepts the information type we aim to obtain about a cell and the reference to the specific cell to return the required information pertaining to the cited cell.
  • Users can utilize the CELL Excel function to analyze the data in a cell or range for aspects such as data locations, errors, contents, formats, data types and colors.
  • The CELL Excel function accepts one mandatory argument value, info_type, and an optional argument value, reference.
  • While we can utilize the CELL Excel function as an individual function, using it with other inbuilt functions, such as IF, INDEX and MATCH, yields practical results.

Syntax

The syntax to apply while using CELL function in Excel is the following:

Cell Function Syntax

Where,

  • info_type: The argument is a text value specifying the information type we want the CELL function to return.
  • reference: The cell about which we aim to gather the required information.

While the first argument in the CELL functionis mandatory, the second is optional.

Furthermore, the table below lists the valid text values we can supply as the info_type argument value.

info_typeCELL() Output
“address”The function returns the reference to the top cell in the reference as a text.
“col”The function returns the first cell’s column number in the reference.
“color”The function returns the value of 1 when the first cell in the reference is formatted using color for negative values. Otherwise, the function returns 0.
“contents”The function returns the value the upper-left cell in the reference contains. Please note that the function does not return the formula but the formula output.
“filename”The function returns the file name (the full path) of the file containing the reference as a text value. However, it returns an empty text when the sheet containing the reference is not yet saved.
“format”The function returns the number format of the specified cell as a text. It returns a Hyphen after the text value for the cell formatted in color for negative values. The function output will have the “()” after the text value for the cell formatted with parentheses for positive or all other values.
“parentheses”The function returns the value 1 when the specified cell is formatted, which includes parentheses for positive or all values. Otherwise, the function returns 0.
“prefix”The function returns the corresponding text value of the “label prefix” of the cited cell. It returns “” when the cell has left-aligned text, “” when the cell has right-aligned text, “^” when the cell contains centered text, “\” when the cell has a fill-aligned text, and empty text when the cell has anything else.
“protect”The function returns the value 0 for an unlocked cell. Otherwise, the function output is 1, indicating a locked cell.
“row”The function returns the first cell’s row number in the reference.
“type”The function returns the data type in the cited cell as a text value.
“width”The function output is an array with two elements. The first element is the cited cell’s column width, rounded off to an integer. Every column width unit equals one character width in the default font size. The second element is a Boolean value. While the value is TRUE when the column width is the default value, the value is FALSE if the user specifies the width explicitly.

Ensure that when supplying the info_type argument value directly, the value must be in double quotes. Otherwise, the value can be a reference to the cell containing the info_type argument value.

Also, please note that all the info_type argument values help obtain information regarding the first (or the upper-left) cell in the reference argument. On the other hand, the info_type argument values color, filename, format, parentheses, prefix, protect, and width do not work in Excel Web, Excel Mobile, and Excel Starter. The reason is that they do not support these values.

Further, the tables below show the values the CELL function will return in different scenarios when the info_type argument value is “format” and “type”.

Excel FormatCELL() Return Value
General“G”
# ?/? or # ??/??“G”
#,##0“,0”
0“F0”
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”
d-mmm-yy or dd-mmm-yy“D1”
d-mmm or dd-mmm“D2”
mmm-yy“D3”
m/d/yy or m/d/yy h:mm or mm/dd/yy“D4”
mm/dd“D5”
h:mm:ss AM/PM“D6”
h:mm AM/PM“D7”
h:mm:ss“D8”
h:mm“D9”
Data TypeCELL() Return Value
Blank“b”
Text“l”
Others“v”

Please note that if the info_type argument value in the CELL() is “format“. Next, when we apply a different format to the specific cell in question, we must re-evaluate the sheet (F9) to update the CELL() output.

Furthermore, the reference argument value is usually a cell reference. However, if the supplied reference argument value is a range, the CELL() output is the information about the upper-left cell of the range.

On the other hand, if we omit the reference argument value, the information based on the info_type argument is returned for the last modified cell.

The Excel CELL function examples explained later in the article will help understand the CELL() argument values settings better.

However, the suggestion is to avoid ignoring the second argument value. Otherwise, the CELL() output can be affected due to the following reasons:

  • In the case of automatic calculation mode, when a user modifies a cell, the calculation triggers before or after the selection has commenced. It depends on the platform we use for Excel.
  • When we Co-Author with another user who edits the worksheet, the CELL() will report our active cell rather than the editor’s.
  • In the case of recalculation, for instance, pressing F9 will make the function return a new output even though we do not edit any cell.

Please note that the abovementioned syntax and the CELL() facets are applicable for the CELL function Google Sheets as well.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use CELL Excel Function?

We can utilize the CELL function in two ways:

  1. Access the function from the Excel ribbon.
  2. Enter the function into the worksheet manually.

Method #1 – Access The Function From The Excel Ribbon

Choose a target cell for output – the Formulas tab – the More Functions down arrow – The Information function group right arrow – CELL.

Cell Function Excel - Method 1

The Function Arguments window will appear. Enter the argument in the Info_type and Reference fields based on the CELL function definition and syntax.

Method 1 - Function Arguments.jpg

Finally, clicking OK in the Function Arguments window will show the CELL() output in the target cell.

Method #2 – Enter The Function Into The Worksheet Manually

  1. Choose a target cell to show the output.
  2. Type =CELL( in the cell. [ Alternatively, type =C and then double-click the CELL Excel function from the listed suggestions to select it.]
  3. Enter the arguments as values or cell references and close the brackets.
  4. Press Enter to view the CELL() output.

Please note that the above methods will work for CELL function Google Sheets as well.

Examples

Check out the following Excel CELL function examples to use it effectively.

Example #1 – Finding A Certain Value

The first dataset lists sales representatives and the sales they generated in January.

Example 1

The second dataset shows a sales representative and the sales they generated in cells B9:B10. The aim is to find the sales-generated value of the sales representative specified in the second dataset in the first dataset as a cell address. Assume the target cell is B11.

Then, here is how to use the CELL function with the Excel INDEX function and MATCH function in the target cell to obtain the desired output.

Step 1: Choose cell B11, enter the CELL() containing the INDEX and MATCH functions, and press Enter.

=CELL(“address”,INDEX(B2:B6,MATCH(B9,A2:A6,0)))

Cell Function Excel - Example 1 - Step 1.jpg

[Alternatively, choose cell B11, and select Formulas More Functions Information CELL.

Example 1 - Step 1 - Alternative

Once the Function Arguments window opens, update the Info_type and Reference fields, as depicted below.

Cell Function Excel - Example 1 - Step 1 - Alternative - Arguments.jpg

Finally, click OK to view the CELL() output in the target cell.]

First, the MATCH() finds the relative position of the specified sales figure of $5,500 in the cited array A2:A6. Since the given value is in the third position in the array, the MATCH() returns the value 3.

Next, the INDEX() returns the reference or the value at the intersection of the specified column B2:B6 and the row, which the MATCH() returned, 3. Thus, the INDEX() returns the absolute reference of the cell at the intersection, $B$4.

Finally, since we must find a value and display the output as a cell address, we supply the info_type argument value in the CELL() as “address”. Thus, the CELL() returns the address of the specified cell reference, $B$4, as the required output.

Example #2

The following dataset shows the results of five experiments.

Example 2.jpg

The aim is to determine the category of each experiment result based on the categories specified in cell C1. Assume the target cells are in column C.

Then, we can use the CELL function in Excel IF function in each target cell to obtain the required outcome.

Step 1: Choose cell C2, enter the IF() containing the CELL(), and press Enter.

=IF(CELL(“contents”,B2)>10,”Cat 3″,IF(CELL(“contents”,B2)>5,”Cat 2″,”Cat 1″))

Cell Function Excel - Example 2 - Step 1.jpg

Step 2: Using the Excel fill handle, implement the formula in the remaining target cells.

Cell Function Excel - Example 2 - Step 2.jpg

Let us check the cell C6 formula to understand its logic.

The CELL function in the outer IF() condition returns the cell B6 content, 12. Next, the formula checks if the CELL() output, 12, is greater than 10. Since the IF condition is true, the IF() returns the TRUE value, Cat 3.

Example #3

The following image shows a source dataset containing a list of employees and their designations and company email IDs.

Cell Function Excel - Example 3.jpg

The second dataset shows a specific employee and their designation.

The aim is to create a hyperlink to the specified employee’s email ID to jump to the exact match in the source dataset by clicking the link. Assume the target cell is G3.

Then, we can use the CELL function with the Excel HYPERLINK function, INDEX, and MATCH functions in the target cell to get the required hyperlink.

Step 1: Choose cell G3, enter the following formula, and press Enter.

=HYPERLINK(“#”&CELL(“address”, INDEX(C2:C11, MATCH(G1,A2:A11,0))), “Click Here”)

Cell Function Excel - Example 3 - Step 1.jpg

Now, we can click the link in cell G3 to directly jump to the cell containing the specified employee’s email ID in the source dataset.

Cell Function Excel - Example 3 - Step 1 - Output.jpg

First, the MATCH() determines the relative position of the specified employee name,  Mildred Nelson, in the cited array A2:A11. Since the given value is in the sixth position in the array, the MATCH() returns the value 6.

Next, the INDEX() returns the reference or the value at the intersection of the specified column C2:C11 and the row, which the MATCH() returned, 6. Thus, the INDEX() returns the absolute reference of the cell at the intersection, $C$7.

Next, since we must determine the output as a cell address, we supply the info_type argument value in the CELL() as “address”. Thus, the CELL() returns the address of the specified cell reference, $C$7, as the required output.

After that, the formula concatenates the ‘#’ symbol and the absolute cell reference we obtained as the CELL() output. The result becomes the HYPERLINK()’s first argument, link_location, with the phrase “Click Here” being the second argument value, friendly_name.

Thus, the HYPERLINK() creates the link to jump to the required value directly, which is the specific employee’s email ID in the source dataset.

Important Things To Note

  • Ensure to supply an info_type argument value that the CELL function recognizes. Otherwise, the function return value will be the #VALUE! error in Excel.
  • Ensure the info_type argument value is in double quotes when supplying the value directly to the CELL Excel function. Otherwise, the function output will be the error value #NAME?.
  • The suggestion is to supply the second argument, reference, to the CELL Excel function, though the argument is optional. Otherwise, we may face issues when using the automatic calculation mode, co-authoring the worksheet with another user, and during recalculation.

Frequently Asked Question (FAQs)

1. What is format CELL function in Excel?

Format CELL function in Excel is a way to return a text associated with the number format of the specified cell.

For example, the following dataset shows a date formatted in different formats according to the date formats listed in column C.

Cell Function Excel - FAQ 1

The aim is to determine the number format of each cell in column B and display the output in the corresponding column D cells as text values.

Then, here is how to use the format as the first argument value in the CELL() in each target cell to achieve the required output.

Step 1: Choose cell D2, enter the CELL(), and press Enter.

=CELL(“format”,B2)

FAQ 1 - Step 1

Step 2: Using the fill handle, enter the formula in the remaining target cells.

FAQ 1 - Step 2

Let us see the cell D6 CELL() to check how it works.

The CELL() accepts “format” as the first argument value, info_type, and the reference to the corresponding cell B6 containing the formatted date value.

So, the CELL() returns the number format of the cell B6 date value as a text value, D5.

2. Is CELL formula in Excel color possible?

The CELL formula in Excel color is possible using the following expression:

=CELL(“color”, reference)

The formula returns the value 1 when the specified cell is color-formatted for a negative value. Otherwise, the function output is 0.

3. Why is CELL function not working in Excel online?

The CELL function is not working in Excel online because the following values, which we supply as the info_type argument value, are not supported in Excel web.

color
filename
format
parentheses
prefix
protect
width

Otherwise, the following reasons may also lead to the CELL() not working online:

The formula has syntax or typo errors.
Missing double quotes when supplying the info_type argument value directly.
The supplied cell reference is invalid.

Download Template

This article must be helpful to understand the CELL Function, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to What Is CELL Excel Function. Here we learn the CELL function syntax and how to use it in Excel with examples and points to remember. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *