Purpose
Return value
Syntax
=CELL(info_type,[reference])
- info_type - The type of information to return about the reference.
- reference - [optional] The reference from which to extract information.
Using the CELL function
Use the CELL function to return a wide range of information about a reference . The type of information returned is given as info_type , which must be enclosed in double quotes (""). CELL can return a cell’s address, the filename and path for a workbook, and information about the formatting used in the cell. See below for a full list of info types and format codes .
CELL is a volatile function , and can cause performance issues in large or complex worksheets.
The CELL function takes two arguments: info_type and reference . Info_type is a text string that indicates the type of information requested. See the table below for a full list of info types. Reference is a cell reference. Reference is typically a single cell. If reference refers to more than one cell, CELL returns information about the first cell in reference. For certain kinds of information (like filename) the cell address used for reference is optional and can be omitted. However, if reference is not supplied, CELL will return the name of the current “active sheet” which may or may not be the sheet where the formula exists, and might even be in a different workbook. To avoid confusion, use A1 for reference.
Note: the CELL function is a volatile function and may cause performance issues in large or complex worksheets.
Examples
For example, to get the column number for C10:
=CELL("col", C10) // returns 3
To get the address of A1 as text:
=CELL("address",A1) // returns "$A$1"
To get the full path and workbook name for the current worksheet:
=CELL("filename",A1) // path + filename
CELL can also return format code information. For example, if A1 contains the number 100 with the currency number format applied, the CELL function will return “C2”:
=CELL("format",A1) // returns "C2"
When requesting the info_type “format” or “parentheses”, a set of empty parentheses “()” is appended to the format returned if the number format uses parentheses for all values or for positive values. For example, if A1 uses the custom number format (0), then:
=CELL("format",A1) // returns "F0()"
Info types
The following info_types can be used with the CELL function:
| Info_type | Description |
|---|---|
| address | returns the address of the first cell in reference (as text). |
| col | returns the column number of the first cell in reference . |
| color | returns the value 1 if the first cell in reference is formatted using color for negative values; or zero if not. |
| contents | returns the value of the upper-left cell in reference . Formulas are not returned. Instead, the result of the formula is returned. |
| filename | returns the file name and full path as text. If the worksheet that contains reference has not yet been saved, an empty string is returned. |
| format | returns a code that corresponds to the number format of the cell. See below for a list of number format codes. If the first cell in reference is formatted with color for values < 0, then “-” is appended to the code. If the cell is formatted with parentheses, returns “() - at the end of the code value. |
| parentheses | returns 1 if the first cell in reference is formatted with parentheses and 0 if not. |
| prefix | returns a text value that corresponds to the label prefix - of the cell: a single quotation mark (’) if the cell text is left-aligned, a double quotation mark (”) if the cell text is right-aligned, a caret (^) if the cell text is centered text, a backslash () if the cell text is fill-aligned, and an empty string if the label prefix is anything else. |
| protect | returns 1 if the first cell in reference is locked or 0 if not. |
| row | returns the row number of the first cell in reference . |
| type | returns a text value that corresponds to the type of data in the first cell in reference : “b” for blank when 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 the column width of the cell, rounded to the nearest integer. A unit of column width is equal to the width of one character in the default font size. Note: this value comes back as an array with two values {width,default} where width is the column width and default is a boolean value that indicates if the width is the default column width. |
Format codes
The table below shows the text codes returned by CELL when “format” is used for info_type .
| Format code returned | Format code meaning |
|---|---|
| G | General |
| F0 | 0 |
| ,0 | #,##0 |
| F2 | 0 |
| ,2 | #,##0.00 |
| C0 | $#,##0_);($#,##0) |
| C0- | $#,##0_);Red |
| C2 | $#,##0.00_);($#,##0.00) |
| C2- | $#,##0.00_);Red |
| P0 | 0% |
| P2 | 0.00% |
| S2 | 0.00E+00 |
| G | # ?/? or # ??/?? |
| D1 | d-mmm-yy or dd-mmm-yy |
| D2 | d-mmm or dd-mmm |
| D3 | mmm-yy |
| D4 | m/d/yy or m/d/yy h:mm or mm/dd/yy |
| D5 | mm/dd |
| D6 | h:mm:ss AM/PM |
| D7 | h:mm AM/PM |
| D8 | h:mm:ss |
Notes
- The CELL function is a volatile function and may cause performance issues in large or complex worksheets.
- Reference is optional for some info types, but use an address like A1 to avoid unexpected behavior.
Purpose
Return value
Syntax
=ERROR.TYPE(error_val)
- error_val - The error for which to get an error code.
Using the ERROR.TYPE function
The Excel ERROR.TYPE function returns a number that corresponds to a specific error value. You can use ERROR.TYPE to test for specific kinds of errors. If no error exists, ERROR.TYPE returns #N/A. See the table below for a key to the error codes returned by ERROR.TYPE.
The ERROR.TYPE function takes just one argument, error_val , which is expected to be an Excel error like #VALUE!, #DIV/0!, #NAME!, etc. When error_val is an error, ERROR.TYPE returns a numeric code. If error_val is not an error, ERROR.TYPE returns an error itself: the #N/A error. In most cases, error_val will be supplied as a reference to a cell that may contain an error value.
Examples
If cell A1 contains displays the #DIV/0 error, then ERROR.TYPE will return 2:
=ERROR.TYPE(A1) // returns 2
If cell A1 displays the #N/A error, ERROR.TYPE returns 7
=ERROR.TYPE(A1) // returns 7
If cell A1 displays no error , ERROR.TYPE returns #N/A
=ERROR.TYPE(A1) // returns #N/A
One way to use ERROR.TYPE is to test for specific errors and display a custom message when certain error conditions exist. For example, to test for a #DIV/0! error in cell A1 and display a custom message when present, you can use a formula like this:
=IF(ISERROR(A1),IF(ERROR.TYPE(A1)=2,"Missing value",A1),"")
This formula returns an empty string ("") when no error is present, and the message “Missing value” when A1 contains #DIV/0!. Other errors are displayed normally.
Errors and codes
| Error | Code |
|---|---|
| #NULL! | 1 |
| #DIV/0! | 2 |
| #VALUE! | 3 |
| #REF! | 4 |
| #NAME? | 5 |
| #NUM! | 6 |
| #N/A | 7 |
| #GETTING_DATA | 8 |
| #SPILL! | 9 |
| #BLOCKED! | 11 |
| #CALC! | 14 |
Other error functions
Excel provides a number of error-related functions, each with a different behavior:
- The ISERR function returns TRUE for any error type except the #N/A error.
- The ISERROR function returns TRUE for any error.
- The ISNA function returns TRUE for #N/A errors only.
- The ERROR.TYPE function returns the numeric code for a given error.
- The IFERROR function traps errors and provides an alternative result.
- The IFNA function traps #N/A errors and provides an alternative result.
