The CELL function with example in Excel

The CELL function in Excel returns information about the formatting, location, or contents of a cell. This function can provide various types of information about a cell, depending on the info_type argument you specify.

Syntax

scss
CELL(info_type, [reference])

• info_type: A text value that specifies what type of cell information you want. Examples include "address", "col", "color", "contents", "filename", "format", "parentheses", "prefix", "protect", "row", "type", "width".
• reference: The cell you want to get information about. If omitted, it defaults to the cell where the formula is entered.

Common info_type Values

• "address": Returns the cell address as text.
• "col": Returns the column number of the cell.
• "contents": Returns the value of the cell.
• "filename": Returns the file name and path of the workbook that contains the cell.
• "format": Returns the number format of the cell.
• "row": Returns the row number of the cell.
• "type": Returns the type of data in the cell ("b" for blank, "l" for label, or "v" for value).

Example

Suppose you have some data in cell A1 and you want to get various pieces of information about this cell.

Data:

A
123.45

Steps to Use the CELL Function:

1. Enter the Data:

   • In cell A1, enter 123.45.

2. Use the CELL Function to Get Information:

   • To get the cell address:

     scss
     =CELL("address", A1)
     

     This returns "$A$1".

   • To get the column number:

     scss
     =CELL("col", A1)
     

     This returns 1.

   • To get the contents of the cell:

     scss
     =CELL("contents", A1)
     

     This returns 123.45.

   • To get the row number:

     scss
     =CELL("row", A1)
     

     This returns 1.

   • To get the type of data in the cell:

     scss
     =CELL("type", A1)
     

     This returns v (since it's a value).

Result:

Function	Result
=CELL("address", A1)	$A$1
=CELL("col", A1)	1
=CELL("contents", A1)	123.45
=CELL("row", A1)	1
=CELL("type", A1)	v

Explanation

• =CELL("address", A1) returns the absolute address of cell A1.
• =CELL("col", A1) returns the column number of cell A1, which is 1.
• =CELL("contents", A1) returns the value contained in cell A1, which is 123.45.
• =CELL("row", A1) returns the row number of cell A1, which is 1.
• =CELL("type", A1) returns the type of data in cell A1. Since A1 contains a numeric value, it returns v (value).

Practical Use Case

Suppose you want to check if a cell is empty and display a message accordingly:

1. Enter Data:

   • In cell B1, enter a value (or leave it empty for testing).

2. Use the CELL Function with IF:

   • In cell C1, enter the following formula to check if B1 is empty:

     less
     =IF(CELL("type", B1) = "b", "Cell is empty", "Cell is not empty")
     

Result:

B	C
[empty]	Cell is empty
123	Cell is not empty

This formula checks the type of data in cell B1. If it's blank ("b"), it returns "Cell is empty". Otherwise, it returns "Cell is not empty".

The CELL function in Excel is versatile and can be used in various scenarios to extract detailed information about cells, enhancing your ability to create dynamic and informative spreadsheets.