Unleash the Power of Excel’s CELL Function

1 Flares Filament.io 1 Flares ×

A very powerful function in Excel that is unknown by many is the Cell function. Use the Cell function to find information about a cell. This can include the cell’s number format, row number or whether it is locked or protected.

There are times when this kind of functionality becomes extremely useful. Especially whilst Excel does not provide many other means of finding the number format of a cell, or whether it has been locked.

The Excel Cell Function

The Cell function is an Information function and requires two items of information to be able to work.

=CELL(info_type, reference)

info_type – The type of information you would like to return about a cell.

reference – The cell that you want to return the information for. If omitted, the Cell function will retrieve information from the last cell that was changed.

What Information can you Return about a Cell

The Cell function can return any of the information listed below;

info_typeReturns
“address”Retrieves the cell’s reference as a text value
“col”The column number of the cell
“color”Retrieves 1 if the cell is formatted in colour for negative values, otherwise returns 0
“contents”Retrieves the value in the cell, not a formula
“coord”Returns the absolute reference of the cell
“filename”The full filename and path of the file that contains the cell. If the file has not yet been saved, empty text (“”) is returned
“format”The number format of the cell returned as a text value
“parenthesis”Returns 1 if the cell is formatted with parenthesis, and 0 if not
“prefix”Returns the label prefix of a cell as a text value. Returns a single quotation mark (‘) if the text is left aligned, a double quotation mark (“) if the text is right aligned, caret (^) if the text is centred, a backslash (\) if the text is fill-aligned and empty text (“”) if it contains anything else
“protect”Returns 0 if the cell is not locked and 1 if it is locked
“row”Row number of the cell
“type”Returns the type of data in the cell as a text value. Returns “b” if the cell is blank, “l” if it contains a text constant and “v” if it contains a value
“width”The column width of the cell rounded of to an integer

Cell Function Examples

Let’s see some examples of the Cell function in action based on the data below.

Cell function examples

=CELL(“address”, A2)$A$2
=CELL(“format”, A2)C2 (text value returned for the number format £#,##0.00)
=CELL(“type”, A1)L (L is returned because A1 contains text)
=CELL(“format”, A3)D1 (text value returned for the number format d-mmm-yy or dd-mmm-yy)

Leave a Reply

Your email address will not be published. Required fields are marked *