The CELL is an Information function that returns information about various properties of a cell. You can learn things like file name with full folder path, location of a cell or formatting properties. In this guide, we’re going to show you how to use the CELL function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Syntax
CELL Function Arguments
| info_type | The type information you need |
| [reference] | The reference from the cell you want information |
Info_type
The info_type argument can take predefined string values that indicate the information.
| info_type | Returns |
| "address" | Reference of the first cell in reference, as text. |
| "col" | Column number of the cell in reference. |
| "color" | The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero). Not supported in Excel for the web, Excel Mobile, and Excel Starter. |
| "contents" | Value of the upper-left cell in reference; not a formula. |
| "filename" | Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved. Not supported in Excel for the web, Excel Mobile, and Excel Starter. |
| "format" | Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns "-" at the end of the text value if the cell is formatted in color for negative values. Returns "()" at the end of the text value if the cell is formatted with parentheses for positive or all values. Not supported in Excel for the web, Excel Mobile, and Excel Starter. |
| "parentheses" | The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0. Not supported in Excel for the web, Excel Mobile, and Excel Starter. |
| "prefix" | Text value corresponding to the "label prefix" of the cell. Returns single quotation mark (') if the cell contains left-aligned text, double quotation mark (") if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text ("") if the cell contains anything else. Not supported in Excel for the web, Excel Mobile, and Excel Starter. |
| "protect" | The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked. Not supported in Excel for the web, Excel Mobile, and Excel Starter. |
| "row" | Row number of the cell in reference. |
| "type" | Text value corresponding to the type of data in the cell. Returns "b" for blank if the cell is empty, "l" for label if the cell contains a text constant, and "v" for value if the cell contains anything else. |
| "width" | Returns an array with 2 items. The 1st item in the array is the column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size. The 2nd item in the array is a Boolean value, the value is TRUE if the column width is the default or FALSE if the width has been explicitly set by the user. Not supported in Excel for the web, Excel Mobile, and Excel Starter. |
CELL Function Example
Basic Usage
The CELL function requires info_type argument to work correctly. Select the information you need from the given list of strings. Although, [reference] argument is optional and can be omitted; supplying is a recommended action. Otherwise, the CELL function returns information from the active cell or active workbook.
=CELL(“address”,C3)
=CELL(“filename”)
Width information
The CELL function returns a single cell value for every information type except “width”. If you select the “width” for infor_type, the CELL function returns a 2-value array.
While the first value is the column width of the cell, rounded off to an integer; the other one is a Boolean value that represents if the width is or width has been explicitly set by the user.
To get 2-value result, select 2 horizontally adjacent cells and press Ctrl + Shift + Enter while submitting the formula.
Tips
- If you omit the reference, the function evaluates the last cell that was changed.
- The CELL has language-specific argument values. As a result, a different language version of Excel causes incompatibility issues.
- You need to recalculate (press F9) the worksheet to update the results of the CELL
CELL Function Issues
#VALUE!
- If info_type is not one of the items in the predefined list, the CELL function returns #VALUE! error.
