Sometimes it’s handy to be able to get information about a spreadsheet cell other than just the value it contains. In Google Sheets that can be done with the CELL function. The function’s purpose is to return information about a cell’s formatting, size, location, or the type of data it contains.
[The Google Sheets CELL function is a cut-down version of the one found in Excel and LibreOffice Calc, among others. It unfortunately doesn’t yet support some of their functionality such as the filename, format and protect info_type values].
Here’s the simple syntax:
info_type is the information you want to return about the cell. Always wrap info_type in double quotes unless you’re calling it by reference to another cell. info_type can be one of the following values:
address – an absolute reference as plain text of the top left cell in reference.
col – the number of the column of the cell in reference.
contents – the value contained in the top left cell in reference.
color – returns 1 or 0. If reference is configurated to show negative numbers in color, the function returns 1. Otherwise, the function returns 0 [but see caveat at foot of post].
prefix – a text value based on the horizontal text alignment in the cell in reference. A single quotation mark (‘) is used for left-aligned text, a double quotation mark (“) for right-aligned text, a carat (^) for centered text, and empty for everything else [but see caveat at foot of post].
row – the row number of the top left cell in reference.
type – the type of data in the cell in reference. Returns “b” for a blank cell, “l” (for label) if the cell contains plain text, and “v” (for value) if the cell contains any other type of data.
width – the column width in terms of number of characters that can fit in the cell provided in reference. The number returned is determined based on the width of the zero (0) character at the default font size. Note that this is different the cell width that Google Sheets uses elsewhere, which is defined in terms of pixels.
For example, if you want to check that a cell contains a numeric value (as opposed to, for example, text) before performing a calculation on it, you can use CELL:
=IF(CELL("type", A1) = "v", A1 + 2, 0)
This formula calculates A1 + 2 only if cell A1 contains something numeric, and returns 0 if A1 contains text or is blank.
Here’s the CELL function in action, with columns C and G displaying CELL information about cells A1 and E1 respectively:
Some interesting things here:
– I could not get prefix to work consistently. Please let me know in the comments if you have better luck.
– While color is not listed in Google’s documentation, it does appear in the error message when an incorrect info_type parameter is used:
It doesn’t appear to work, though, when setting up different colours for negative numbers using conditional formatting (as shown in the first figure). Does this mean we can expect some new native number formats with different colours for negatives to appear sometime soon? Again, let me know if your results are different. Meanwhile, here’s the same formula used in LibreOffice Calc. Note where I’ve highlighted differences: