# CELL Excel Function

Last Updated :

21 Aug, 2024

Blog Author :

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

## What Is CELL Function In Excel?

The

CELLfunction in Excel is an inbuiltInformationfunction. 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

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

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.

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.

##### Table of contents

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

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_type | CELL() 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 Format | CELL() Return Value |
---|---|

General | "G" |

# ?/? or # ??/?? | "G" |

#,##0 | ",0" |

0 | "F0" |

0.00 | "F2" |

#,##0.00 | ",2" |

$#,##0_);($#,##0) | "C0" |

$#,##0_);($#,##0) | "C0-" |

$#,##0.00_);($#,##0.00) | "C2" |

$#,##0.00_);($#,##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 Type | CELL() 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.

### How To Use CELL Excel Function?

We can utilize the **CELL **function in two ways:

**Access the function from the Excel ribbon.****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**.

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

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

- Choose a target cell to show the output.
- Type
**=CELL(**in the cell. - Enter the arguments as values or cell references and close the brackets.
- 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.

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)))*

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.

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"))**

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

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.

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")**

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.

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.

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)***Step 2: **Using the fill handle, enter the formula in the remaining target cells.

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.

### Recommended Articles

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 –