How to Get Specific Information About Cells in Excel 2013

The CELL function in Excel 2013 is the basic information function for getting all sorts of data about the current contents and formatting of a cell. The syntax of the CELL function is

CELL(info_type,[reference])

The info_type argument is a text value that specifies the type of cell information you want returned. The optional reference argument is the reference of the cell range for which you want information. When you omit this argument, Excel specifies the type of information specified by the info_type argument for the last cell that was changed in the worksheet.

When you specify a cell range as the reference argument, Excel returns the type of information specified by the info_type argument for the first cell in the range (that is, the one in the upper-left corner, which may or may not be the active cell of the range).

The table shows the various info_type arguments that you can specify when using the CELL function. Remember that you must enclose each info_type argument in the CELL function in double-quotes (to enter them as text values) to prevent Excel from returning the #NAME? error value to the cell containing the CELL function formula.

So, for example, if you want to return the contents of the first cell in the range B10:E80, you enter the following formula:

=CELL("contents",B10:E80)
The CELL Functions info_type Arguments
CELL Function info_type Argument Returns This Information
"address" Cell address of the first cell in the reference as text using absolute cell references
"col" Column number of the first cell in the reference
"color" 1 when the cell is formatted in color for negative values; otherwise returns 0 (zero)
"contents" Value of the upper-left cell in the reference
"filename" Filename (including the full pathname) of the file containing the cell reference: returns empty text ("") when the workbook containing the reference has not yet been saved
"format" Text value of the number format of the cell: Returns "-" at the end of the text value when the cell is formatted in color for negative values and "()" when the value is formatted with parentheses for positive values or for all values
"parentheses" 1 when the cell is formatted with parentheses for positive values or for all values
"prefix" Text value of the label prefix used in the cell: Single quote (') when text is left-aligned; double quote (") when text is right-aligned; caret (^) when text is centered; backslash (\) when text is fill-aligned; and empty text ("") when the cell contains any other type of entry
"protect" 0 when the cell is unlocked and 1 when the cell is locked
"row" Row number of the first cell in the reference
"type" Text value of the type of data in the cell: "b" for blank when cell is empty; "l" for label when cell contains text constant; and "v" for value when cell contains any other entry
"width" Column width of the cell rounded off to the next highest integer (each unit of column width is equal to the width of one character in Excel’s default font size)

The following table shows the different text values along with their number formats (codes) that can be returned when you specify “format” as the info_type argument in a CELL function.

Text Values Returned by the “format” info_type
Text Value Number Formatting
"G" General
"F0" 0
",0" #,##0
"F2" 0.00
",2" #,##0.00
"C0" $#,##0_);($#,##0)
"C0-" $#,##0_);[Red]($#,##0)
"C2" $#,##0.00_);($#,##0.00)
"C2-" $#,##0.00_);[Red]($#,##0.00)
"P0" 0%
"P2" 0.00%
"S2" 0.00E+00
"G" # ?/? or # ??/??
"D4" m/d/yy or m/d/yy h:mm or mm/dd/yy
"D1" d-mmm-yy or dd-mmm-yy
"D2" d-mmm or dd-mmm
"D3" mmm-yy
"D5" mm/dd
"D7" h:mm AM/PM
"D6" h:mm:ss AM/PM
"D9" h:mm
"D8" h:mm:ss

For example, if you use the CELL function that specifies “format” as the info_type argument on cell range A10:C28 (which you’ve formatted with the Comma style button on the Formula bar), as in the following formula

=CELL("format”,A10:C28)

Excel returns the text value ",2-" (without the quotation marks) in the cell where you enter this formula signifying that the first cell uses the Comma style format with two decimal places and that negative values are displayed in color (red) and enclosed in parentheses.

blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.