The CELL function in Excel is very powerful and relatively unknown to many. Use the Excel CELL function to return 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 while 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_type | Returns |
|---|---|
| “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 Excel CELL function in action based on the data below.
| =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