Documentation/Calc Functions/CELL

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎Nederlands



Function name:

CELL

Category:

Information

Summary:

Provides requested information on location, formatting properties, or contents of a cell.

Syntax:

CELL(Info Type[; Reference])

Returns:

Returns a value whose type depends on the nature of the information requested.

Arguments:

Info Type is a text string, or a reference to a cell containing a text string, that determines the type of information to be returned. Info Type is case-insensitive and must correspond to one of the following permitted options – "ADDRESS", "COL", "COLOR", "CONTENTS", "COORD", "FILENAME", "FORMAT", "PARENTHESES", "PREFIX", "PROTECT", "ROW", "SHEET", "TYPE", or "WIDTH". More detail about each of these options is given in the table below.

Reference is a reference to the cell for which information is to be returned. If Reference is omitted, information is returned for the cell in which the formula is located. Reference may be any of the following:

  • A reference to a single cell.
  • A reference to a cell range (for example, D6:E12).
  • The name of a named range.
  • The name of a database range.

Where Reference specifies multiple cells, CELL returns information for the top left of those cells.

Note pin.svg

Note:
The content of the following two tables includes aspects that are dependent on locale, such as number and date formats. All examples on this page have been tested in the English (USA) locale but behaviour may be different in other locale settings.

Description of valid Info Type argument strings
Info Type string Meaning
ADDRESS Returns the absolute address of the cell. For example:
  • With the formula located in cell A1, =CELL("address") returns $A$1.
  • =CELL("address"; D7) returns $D$7.
  • =CELL("address"; Sheet2.E7) returns $Sheet2.$E$7.
  • =CELL("address"; 'file:///C:/spreadsheets/test.ods'#$Sheet1.D10) returns 'file:///C:/spreadsheets/test.ods'#$Sheet1.D10.

The above examples assume the default setting of Calc A1 for the Formula syntax menu in the Tools ▸ Options ▸ LibreOffice Calc ▸ Formula dialog (LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Formula on macOS).

COL Returns the column number of the cell (column A is 1, column B is 2, and so on). For example:
  • With the formula located in cell A1, =CELL("col") returns 1.
  • =CELL("col"; D7) returns 4.
  • =CELL("col"; Sheet2.E7) returns 5.
COLOR Returns a value indicating whether negative values are formatted in color in the cell (1=formatted in color, 0=not formatted in color). For example:
  • The formula =CELL("color") returns 1 in a cell that has been formatted such that the Negative numbers red option has been selected on the Numbers tab of the Format ▸ Cells dialog.
  • The formula =CELL("color"; D7) returns 0 if cell D7 has been formatted such that the Negative numbers red option has been deselected on the Numbers tab of the Format ▸ Cells dialog.
CONTENTS Returns the contents of the cell, without any formatting. For example:
  • Cell E2 contains the number -1234.567, with the cell formatted to show no decimal places and negative numbers as red (format code 0;[RED]-0). The formula =CELL("contents"; E2) returns -1234.567, without the formatting applied to cell E2.
  • Cell E3 contains the text "Hello world!", formatted with bold, italic, and underline. The formula =CELL("contents"; E3) returns " Hello world!", without the formatting applied to cell E3.
  • Cell E4 contains the date 2021-12-05. The formula =CELL("contents"; E4) returns the number 44535, the date-time serial number of 2021-12-05 (assuming that the start date for the internal conversion from days to numbers is set to the default of 1899-12-30).
  • Cell E5 contains the Boolean value TRUE. The formula =CELL("contents"; E5) returns 1.
  • If the formula =CELL("contents") is placed in a cell, then CELL reports a circular reference error (Err:522).
COORD Returns the complete cell address using a legacy notation, in which the sheet position is indicated by a letter (A indicates the spreadsheet at the left of the sheet tab area, B the next sheet to the right, and so on). For example:
  • With the formula located in cell A1 of the eighth sheet along, =CELL("coord") returns $H:$A$1.
  • With the formula located in the sixth sheet along, =CELL("coord"; D7) returns $F:$D$7.

The "COORD" option for the Info Type argument is not a requirement of ODF 1.2.

FILENAME Returns the file name and the name of the sheet in which the cell is located. For example:
  • With the formula in cell D7 of Sheet1 in the file C:/spreadsheets/test.ods, =CELL("filename") returns 'file:///C:/spreadsheets/test.ods'#$Sheet1.
  • In the same file, =CELL("filename"; $Sheet3.F5) would return 'file:///C:/spreadsheets/test.ods'#$Sheet3.
  • With the formula in cell D7 of Sheet1 in a new (unsaved) spreadsheet, =CELL("filename") returns "#$Sheet1.
FORMAT Returns a character string that indicates the cell’s number format. The possible return values are defined in a separate table below.
PARENTHESES Returns 1 if the cell's format code contains a left parenthesis and returns 0 otherwise. For example:
  • If the format code #,##0_);(#,##0) is applied to cell D3, then =CELL("parentheses"; D3) returns 1.
PREFIX Returns a single character string indicating the alignment of text in the cell, coded as follows:
  • ' (apostrophe) = left aligned
  • " (quotation mark) = right aligned
  • ^ (caret) = centered
  • \ (backslash) = filled alignment

For example:

  • If cell D3 is right-aligned, the formula =CELL("prefix"; D3) returns a quotation mark (").

Returns the null character (Unicode U+0000) if the cell does not contain a string.

PROTECT Returns the status of cell protection for the cell. 1 indicates that the cell is protected, while 0 indicates that the cell is not protected. For example:
  • If cell B1 is protected, then =CELL("protect"; B1) returns 1.

Note that cell protection is only effective after the current sheet has been protected. Cell protection may be enabled while a sheet is not protected.

ROW Returns the row number of the cell. For example:
  • With the formula located in cell A1, =CELL("row") returns 1.
  • =CELL("row"; D7) returns 7.
  • =CELL("row"; Sheet2.E12) returns 12.
SHEET Returns the number of the referenced sheet, 1 indicates the sheet at the left of the sheet tab area, 2 the next sheet along to the right, and so on. For example,
  • With the formula located in cell A1 of the sheet Receipts (2nd from the left in the sheet tab area), =CELL("sheet") returns 2.
  • =CELL("sheet"; Accounts.G7) returns 3 in the case when the sheet Accounts is 3rd from the left in the sheet tab area.
TYPE Returns the type of data in the cell. One of three possible single‑character strings – b (for blank) for an empty cell; l (for label) if the cell contains text; v (for value) otherwise. For example:
  • If cell A1 contains the text "Hello world!", then the formula =CELL("type"; A1) returns l.
  • If cell A2 contains the number 2, then the formula =CELL("type"; A2) returns v.
  • If cell A3 is empty, then the formula =CELL("type"; A3) returns b.

The formula =CELL("type") always returns v.

WIDTH Returns the width of the column in which the cell lies. The unit is the number of zero (0) characters that fit into the column based on the default font. For example:
  • Suppose the formula =CELL("width"; C2) currently returns the value 11.
  • Changing the width of column C will affect the result of the formula. If you increase the width of the column, then the value returned will increase. If you reduce the width of the column, then the value returned will decrease. You may need to use Data ▸ Calculate ▸ Recalculate Hard after changing the column width.


Description of return values for the FORMAT Info Type argument
Return value Meaning
Numbers
, (comma) Indicates that the cell is formatted to display numbers with thousands separators. The number of decimal places specified by the cell format is appended after the comma. For example:
  • With the number 123,456.7890 displayed in cell A3 (format code #,###.0000), the formula =CELL("format"; A3) returns ,4.
F Indicates that the cell is formatted to display numbers without thousands separators. The number of decimal places specified by the cell format is appended after the letter F. For example:
  • With the number 123456.79 displayed in cell A4 (format code 0.00), the formula =CELL("format"; A4) returns F2.
  • With the number 123456.789 displayed in cell A5 (format code General), the formula =CELL("format"; A5) returns F0. Note that the Decimal places field on the Numbers tab of the Format Cells dialog is blank when the General format is selected.
C Indicates that the cell is formatted to display numbers in a currency format. The number of decimal places specified by the cell format is appended after the letter C. For example:
  • With the value $2,345.68 displayed in cell A6 (format code [$$-409]#,##0.00;-[$$-409]#,##0.00), the formula =CELL("format"; A6) returns C2.
S Indicates that the cell is formatted to display numbers in scientific (exponential) representation. The number of decimal places specified by the cell format is appended after the letter S. For example:
  • With the value 1.23E+45 displayed in cell A7 (format code 0.00E+00), the formula =CELL("format"; A7) returns S2.
P Indicates that the cell is formatted to display percentages. The number of decimal places specified by the cell format is appended after the letter P. For example:
  • With the value 56.789% displayed in cell A8 (format code 0.000%), the formula =CELL("format"; A8) returns P3.
Dates
D1 Date formats comprising day, month, and year values. For example:
  • With the date 12/8/21 displayed in cell A12 (format code M/D/YY), the formula =CELL("format"; A12) returns D1.
D2 Date formats comprising day and month (abbreviated alphabetical name) values. For example:
  • With the date Dec 08 displayed in cell A13 (format code MMM DD), the formula =CELL("format"; A13) returns D2.
D3 Date formats comprising month and year values. For example:
  • With the date 12/21 displayed in cell A14 (format code MM/YY), the formula =CELL("format"; A14) returns D3.
D4 Date and time formats comprising second, minute, hour, day, month, and year values (without AM/PM indicator). For example:
  • With the date/time 12/08/2021 20:25:46 displayed in cell A15 (format code MM/DD/YYYY HH:MM:SS), the formula =CELL("format"; A15) returns D4.
D5 Date format comprising day and month (numerical) values. For example:
  • With the date 12-08 displayed in cell A16 (format code MM-DD), the formula =CELL("format"; A16) returns D5.
D6 Time formats including second, minute, and hour values, with AM/PM indicator. For example:
  • With the time 08:33:35 PM displayed in cell A17 (format code HH:MM:SS AM/PM), the formula =CELL("format"; A17) returns D6.
D7 Time formats including minute and hour values, with AM/PM indicator. For example:
  • With the time 08:33 PM displayed in cell A18 (format code HH:MM AM/PM), the formula =CELL("format"; A18) returns D7.
D8 Time formats including second, minute, and hour values, with no AM/PM indicator. For example:
  • With the time 20:33:35 displayed in cell A19 (format code HH:MM:SS), the formula =CELL("format", A19) returns D8.
D9 Time formats including minute and hour values, with no AM/PM indicator. For example:
  • With the time 20:33 displayed in cell A20 (format code HH:MM), the formula =CELL("format"; A20) returns D9.
G All other date and time formats. For example:
  • With the date/time 2021-12-08 20:33:35 displayed in cell A21 (format code YYYY-MM-DD HH:MM:SS), the formula =CELL("format"; A21) returns G.
Additional notes

A - (minus) is appended at the end of the returned string when the cell is formatted to display negative numbers in colour. For example:

  • With the value $12.34 displayed in cell A9 (format code [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00), the formula =CELL("format"; A9) returns C2-.

() (parentheses) are appended at the end of the returned string when the cell’s format code includes a left parenthesis. For example:

  • With the value (789) displayed in cell A10 (format code 0_);(0)), the formula =CELL("format"; A10) returns F0().
  • With the value 789.012 displayed in cell A11 (format code #,###.000_);[RED](#,###.000)), the formula =CELL("format"; A11) returns ,3-(). This complex format code shows negative values in red, surrounded by parentheses.
  • If Info Type does not correspond to one of the expected strings, then CELL reports an invalid argument error (Err:502).
  • If Reference does not correspond to a valid reference, the CELL reports a #REF! error.
  • If Info Type is set to “CONTENTS” and Reference is omitted, then CELL reports a circular reference error (Err:522).

Additional details:

None

Examples:

See examples within the tables in the #Arguments: section above.

Related LibreOffice functions:

None.

ODF standard:

Section 6.13.3, part 2

Equivalent Excel functions:

CELL