CELL function for Sheets

Help Center

AI Assistant

Help Center AI Assistant is now available

Got questions about Lark? Use our AI chat to find the answers.

Click and hold to drag

Got It

Try Now

CELL function for Sheets

2 min read

I. Intro

The CELL function returns the information of a given cell, such as its address, row number, column number, and so on, which can be used for analysis.

II. About the function

Formula: =CELL(info_type, [reference])

Arguments:

info_type: The type of information you want, referenced using double quotation marks. For instance, "address" returns the cell address, "col" returns the column number, "row" returns the row number, and so on.

reference: Optional; the cell whose information needs to be obtained. If omitted, the information of the current cell will be obtained by default. If you select a region, such as A1:D4, the system will only obtain the information of the cell in the upper-left corner of that region, that is, the information of A1.

Example: =CELL("address",A1) returns the address of A1, which is $A1$1.

III. Steps

Use the function

Open the spreadsheet, select a cell, and enter the formula =CELL(info_type, [reference]).

Press Enter to get the result.

250px|700px|reset

You can query different cell information using different info types. The following info types are supported:

Info type	Description	Example
address	Returns the cell location	CELL("address",A2) returns the cell location of A2, which is $A$2. Note: $ is used to fix the row and column numbers, indicating an absolute reference to the cell position.
col	Returns the cell column number	CELL("col",D2) returns the column number of D2.
row	Returns the cell row number	CELL("row",B5) returns the row number of B5.
contents	Returns the value in the cell	CELL("contents",A4) returns the content in A4.
prefix	Returns the alignment of the text in the cell: Single quotation marks (') indicates left alignment. Double quotation marks (") indicates right alignment. Caret (^) indicates center alignment. A blank indicates that the alignment has not been set.	CELL("prefix",A4) returns the alignment of the text in A4.
type	Returns the data type of the cell: b indicates that the cell is empty. l indicates that the cell contains text. v indicates other types of data.	CELL("type",A6) returns the data type in A6.
width	Returns the width of the cell, the return value contains two parts: The first value is an integer, indicating how many characters can be placed in the cell under the default font of the sheet. The second value is a Boolean value, indicating whether the current cell width is the default setting. TRUE indicates the default width, FALSE indicates that the width has been adjusted.	CELL("width",A9) returns the default width of A9, and whether it is the default width.
format	Returns the format of the numbers in the cell. The return value contains 1-3 characters, the specific meanings are shown in the table below. Note: Only supports number formats set using the toolbar.	CELL("format",B2) returns the format of the numbers in B2.

Number format	The first character in the return value	The second character in the return value	The third character in the return value
General/Plain text	G	None	None
Floating point number (number with decimal point)	F	Number of decimal places	When the cell's number format is set to represent positive values or all values with parentheses, () is returned
Percent	P	Number of decimal places
Currency	C	Number of decimal places
Scientific notation	S	Number of decimal places
Number with thousand separator	,	Number of decimal places
Date/Time	Not supported

Delete the function

Select the cell with the function and press Delete.

IV. FAQs

Are the following info types supported: color (determining whether to use color to mark negative values), parentheses (determining whether to use parentheses to represent negative values), and protect (determining whether it is a protected area)?

No. Using these info types may lead to unexpected results.