How to Use CELL Function in Excel (5 Easy Examples)

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.


Download Practice Workbook

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


Introduction to the CELL Function

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”

5 Examples of Using the Excel CELL Function

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

5 Examples of Using the Excel CELL Function: Sample 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.

Return Column Number with Excel CELL Function

Now you will see the output like the image below-

Return Column Number with Excel CELL Function: Output

Read More: How to Hyperlink to Cell in Excel (2 Simple Methods)


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.

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

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.

Join CELL Function with LEFT and FIND to Retrieve Excel File Path

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\”


Similar Readings


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.

Insert CELL Function with MID and FIND to Retrieve the Excel File Name
Then you will get your Excel file name like the image below:

MID, CELL, FIND Functions to Retrieve File Name

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.

MID, FIND, LEN, CELL Functions to Retrieve Sheet Name

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”


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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo