The **CELL** function is categorized under the information function in Excel. The CELL function returns information about the cell contents, formatting, or location. This article will guide you with some easy examples and proper illustrations to use the CELL function.

The above image is the overview of the article that represents the application of the CELL function. Now you will learn the basic and other applications of CELL function in detail with clear steps.

## Introduction to the CELL Function in Excel

**Function Objective:**

The **CELL** Function Returns information about a cell color, filename, contents, format, row, etc.

**Syntax:**

`CELL(info_type, [reference])`

**Arguments Explanation:**

ARGUMENTS |
REQUIRED/OPTIONAL |
EXPLANATION |

type | Required | The type of information that you’d like to retrieve for the cell. |

range | Optional | It is the cell (or range) that you wish to retrieve information for. If the range parameter is omitted, the CELL function will assume that you are retrieving information for the last cell that was changed. |

**Type Can be One of the Following Values:**

VALUE |
EXPLANATION |

“address” | Subject(as text) of the foremost cell in reference. |

“col” |
Returns the column number of the selected cell in reference. |

“color” |
Returns 1 when the color is a minus value and otherwise, it turns back 0. |

“contents” |
Values of the higher-left cell. |

“filename” |
The filename of the file that holds a reference. |

“format” |
The number format of the specific cell. See the given table below for formats. |

“parentheses” |
Turns back 1 when the cell is formatted with parentheses and if not then it turns back 0. |

“prefix” |
The label prefix for the specific cell. – When the cell is left-aligned then it returns a single quote (‘). -When the cell is right-aligned then it returns a double quote (“). -When the cell is center-aligned then it returns a caret (^). – When the cell is fill-aligned then it returns a backslash (\). -For all others, it turns back an empty text value. |

“protect” |
Turns back 1 when the cell is locked. And when the cell is not locked then it returns 0. |

“row” |
The row number of the specific cell. |

“type” |
It turns back “b” when the cell is vacant. It turns back “l” when the cell holds a text constant. For all others, It turns back “v”. |

“width” |
The rounded nearest integer which is the width of the column of the cell. |

**CELL Format Codes:
**

If the Format is | The cell function 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” |

## How to Use the Excel CELL Function: 5 Suitable Examples

Let’s get introduced to our dataset first. I have placed some salespersons’ names and their sales in my dataset.

### Example 1: Return Column Number with Excel CELL Function

In our very first example, I’ll show a basic application of the **CELL** function to find the column number of a cell. We’ll find the column number of **Cell B5.**

**Steps:**

- Type the following formula in
**Cell C11:**

`=CELL("Col",B5)`

- Then just hit the
**Enter**button.

Now you will see the output like the image below-

### Example 2: Combine Excel CELL Function with HYPERLINK, INDEX, MATCH Functions to Create Hyperlink for a Lookup Value

We can use the **CELL** function along with **HYPERLINK** and **INDEX, MATCH** functions to hyperlink to a lookup result. The **HYPERLINK** function is used to create a shortcut that jumps to another location in the current workbook. And the **INDEX** function returns a value or the reference to a value from within a table or range. The **MATCH** function is used to search for a specified item in a range of cells and then returns the relative position of that item in the range. Here, we’ll set the hyperlink for Dora.

**Steps:**

- Activate
**Cell C12.** - Write the given formula in it:

`=HYPERLINK("#"&CELL("address",INDEX(C5:C9,MATCH("Dora",B5:B9,0))),INDEX(C5:C9,MATCH("Dora",B5:B9,0)))`

- Later, just press the
**Enter**button.

Now if you press on the output then it will jump to the lookup result as shown in the image below-

⏬ **Formula Breakdown:**

**➥ MATCH(“Dora”,B5:B9,0)****
**The

**MATCH**function will search the item ‘Dora’ through the range

**B5:B9**and will return its position relative to the selected array. So it will return-

**2**

**➥ INDEX(C5:C9,MATCH(“Dora”,B5:B9,0))****
**Then the

**INDEX**function will return the corresponding output according to that position from the range

**C5:C9**

**–**

**6732**

**➥** **CELL(“address”,INDEX(C5:C9,MATCH(“Dora”,B5:B9,0)))****
**The

**CELL**function will return the cell address of the output

**6732**

**,**that is-

**“$C$6”**

**➥** **HYPERLINK(“#”&CELL(“address”,INDEX(C5:C9,MATCH(“Dora”,B5:B9,0))),INDEX(C5:C9,MATCH(“Dora”,B5:B9,0)))****
**Finally, the HYPERLINK function will create a link with the address

**$C$6**and that will return-

**6732**

### Example 3: Join CELL Function with LEFT and FIND to Retrieve Excel File Path

We can get the file path where the particular file is stored by using the **LEFT****,** **FIND** functions along with the **CELL** function. The **LEFT** function extracts text from the left side of a text string and the **FIND** function in Excel returns the position of a specific character or substring within a text string.

**Steps:**

Type the following formula in **Cell C11:**

`=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)`

Finally, hit the **Enter** button to get the result.

Here’s our file path:

⏬ **Formula Breakdown:**

**➥** **CELL(“filename”)****
**The

**CELL**function will return the full path, filename, and extension in square brackets, and sheet name-

**“C:\Users\DELL\OneDrive\Desktop\mithun\45\[Excel_CELL_Function.xlsx]File**

**Path”**

**➥** **FIND(“[“,CELL(“filename”))****
**Then the FIND function will return the character position of “

**[**” that is-

**42**

**➥** **LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1)****
**Finally, the

**LEFT**function will return 41 characters from the left to exclude “

**[**” that’s why 1 is subtracted from the previous output. So it will return as-

**“C:\Users\DELL\OneDrive\Desktop\mithun\45\”**

### Example 4: Insert CELL Function with MID and FIND to Retrieve the Excel File Name

We can get the file name also by using the **MID****,** **CELL****,** **FIND** Functions together. The **MID** function is used to return a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

**Steps:**

Write the formula given below in **Cell C11:**

`=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)`

Click the **Enter** button.

Then you will get your Excel file name like the image below:

⏬ **Formula Breakdown:**

**➥** **CELL(“filename”)****
**The

**CELL**function will return the full path, filename, and extension in square brackets, and sheet name-

**“C:\Users\DELL\OneDrive\Desktop\mithun\45\[Excel_CELL_Function.xlsx]File**

**Name”**

**➥** **FIND(“[“,CELL(“filename”))****
**Then the

**FIND**function will return the character position of “

**[**” that is-

**42**

**➥** **FIND(“]”,CELL(“filename”))****
**Here, the FIND function will return the character position of “

**]**” that is-

**67**

**➥** **FIND(“]”,CELL(“filename”))-FIND(“[“,CELL(“filename”))-1****
**Now it will return the sum and which is-

**24**

**➥** **FIND(“[“,CELL(“filename”))+1****
**Again the

**FIND**function will return the character position of “

**[**” and will add one so it will return as-

**43**

**➥** **MID(CELL(“filename”),FIND(“[“,CELL(“filename”))+1,FIND(“]”,CELL(“filename”))-FIND(“[“,CELL(“filename”))-1)****
**Finally, the

**MID**function will keep

**24**characters starting from position

**43**and will return as-

**“Excel_CELL_Function.xlsx”**

### Example 5: Join MID, FIND, LEN & CELL Functions to Retrieve Sheet Name

In our last example, we’ll find out the sheet name by using the combination of the **MID****,** **FIND****,** **LEN****,** **CELL** Functions. The **LEN** function is used to return the number of characters in a given text string.

**Steps:**

In **Cell C11** write the following formula-

`=MID(CELL("filename"),FIND("]",CELL("filename"))+1,LEN(CELL("filename")))`

Hit the **Enter** button for the output.

Now you will observe that the sheet name is retrieved.

⏬ **Formula Breakdown:**

**➥** **CELL(“filename”)****
**The

**CELL**function will return the full path, filename, and extension in square brackets, and sheet name-

**“C:\Users\DELL\OneDrive\Desktop\mithun\45\[Excel_CELL_Function.xlsx]Sheet**

**Name”**

**➥** **LEN(CELL(“filename”))****
**The

**LEN**function will count the text length of the output of the

**CELL**function that will return as-

**77**

**➥** **FIND(“]”,CELL(“filename”))+1****
**Then the

**FIND**function will find the position of “

**]**” and after adding

**1**it will return as-

**68**

**➥** **MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,LEN(CELL(“filename”)))****
**Finally, the

**MID**function will return the characters starting from position

**68**–

**“Sheet Name”**

**Download Practice Workbook**

You can download the free Excel template from here and practice on your own.

**Conclusion**

I hope all of the methods described above will be good enough to use the **CELL** function. Feel free to ask any question in the comment section and please give me feedback.

**<< Go Back to Excel Functions | Learn Excel**

Can I apply this to filtered table?

Hello

ROSEMARIE OLIVERA,Thanks for your response. Yes, you can apply the

CELLfunction to the filtered table.Regards

MD Naimul Hasan