
This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

Information functions
Click one of the links in the following list to see detailed help about the function.
Function

Description

CELL

Returns information about the formatting, location, or contents of a cell

ERROR.TYPE

Returns a number corresponding to an error type

INFO

Returns information about the current operating environment

ISBLANK

Returns TRUE if the value is blank

ISERR

Returns TRUE if the value is any error value except #N/A

ISERROR

Returns TRUE if the value is any error value

ISEVEN

Returns TRUE if the number is even

ISLOGICAL

Returns TRUE if the value is a logical value

ISNA

Returns TRUE if the value is the #N/A error value

ISNONTEXT

Returns TRUE if the value is not text

ISNUMBER

Returns TRUE if the value is a number

ISODD

Returns TRUE if the number is odd

ISREF

Returns TRUE if the value is a reference

ISTEXT

Returns TRUE if the value is text

N

Returns a value converted to a number

NA

Returns the error value #N/A

TYPE

Returns a number indicating the data type of a value

See Also
Excel > Function reference > Information
CELL function
Excel 2007
This article describes the formula syntax and usage of the CELL function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel. Find links to more information about formatting the data in cells and applying cell styles in the See Also section.
Description
The CELL function returns information about the formatting, location, or contents of a cell. For example, if you want to verify that a cell contains a numeric value instead of text before you perform a calculation on it, you can use the following formula:
=IF(CELL("type", A1) = "v", A1 * 2, 0)
This formula calculates A1*2 only if cell A1 contains a numeric value, and returns 0 if A1 contains text or is blank.
Syntax
CELL(info_type, [reference])
The CELL function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

info_type Required. A text value that specifies what type of cell information you want to return. The following list shows the possible values of the info_type argument and the corresponding results.
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).

"contents"

Value of the upperleft 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.

"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.

"parentheses"

The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0.

"prefix"

Text value corresponding to the "label prefix" of the cell. Returns single quotation mark (') if the cell contains leftaligned text, double quotation mark (") if the cell contains rightaligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fillaligned text, and empty text ("") if the cell contains anything else.

"protect"

The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked.

"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"

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.


reference Optional. The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.
CELL format codes
The following list describes the text values that the CELL function returns when the info_type argument is "format" and the reference argument is a cell that is formatted with a builtin number format.
If the Excel format is

The CELL function returns

General

"G"

0

"F0"

#,##0

",0"

0.00

"F2"

#,##0.00

",2"

$#,##0_);($#,##0)

"C0"

$#,##0_);[Red]($#,##0)

"C0"

$#,##0.00_);($#,##0.00)

"C2"

$#,##0.00_);[Red]($#,##0.00)

"C2"

0%

"P0"

0.00%

"P2"

0.00E+00

"S2"

# ?/? or # ??/??

"G"

m/d/yy or m/d/yy h:mm or mm/dd/yy

"D4"

dmmmyy or ddmmmyy

"D1"

dmmm or ddmmm

"D2"

mmmyy

"D3"

mm/dd

"D5"

h:mm AM/PM

"D7"

h:mm:ss AM/PM

"D6"

h:mm

"D9"

h:mm:ss

"D8"

Note If the info_type argument in the CELL function is "format" and you later apply a different format to the referenced cell, you must recalculate the worksheet to update the results of the CELL function.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?

Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help

Press CTRL+C.

In Excel, create a blank workbook or worksheet.

In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

Data



5Mar



TOTAL



Formula

Description

Result

=CELL("row", A20)

The row number of cell A20

20

=CELL("format", A2)

The format code of cell A2

D2 (dmmm)

=CELL("contents", A3)

The content of cell A3

TOTAL

=CELL("type", A2)

The data type of cell A2

v (value)


See Also
Excel > Function reference > Information
ERROR.TYPE function
Excel 2007
Returns a number corresponding to one of the error values in Microsoft Excel or returns the #N/A error if no error exists. You can use ERROR.TYPE in an IF function to test for an error value and return a text string, such as a message, instead of the error value.
Syntax
ERROR.TYPE(error_val)
Error_val is the error value whose identifying number you want to find. Although error_val can be the actual error value, it will usually be a reference to a cell containing a formula that you want to test.
If error_val is

ERROR.TYPE returns

#NULL!

1

#DIV/0!

2

#VALUE!

3

#REF!

4

#NAME?

5

#NUM!

6

#N/A

7

#GETTING_DATA

8

Anything else

#N/A

Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example

Create a blank workbook or worksheet.

Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help

Press CTRL+C.

In the worksheet, select cell A1, and press CTRL+V.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A


Data

#NULL!

=1/0

Formula

Description (Result)

=ERROR.TYPE(A2)

Number of the #NULL! error (1)

=IF(ERROR.TYPE(A3)<3,CHOOSE(ERROR.TYPE(A3),"Ranges do not intersect","The divisor is zero"))

Checks cell A3 to see whether the cell contains either the #NULL! error value or the #DIV/0! error value. If it does, then the number for the error value is used in the CHOOSE worksheet function to display one of two messages; otherwise, the #N/A error value is returned. (The divisor is zero.)


See Also
Excel > Function reference > Information
INFO function
Excel 2007
Security Use this feature with caution. Sensitive or confidential information could be revealed to other users.
Returns information about the current operating environment.
Syntax
INFO(type_text)
Type_text is text that specifies what type of information you want returned.
Type_text

Returns

"directory"

Path of the current directory or folder.

"numfile"

Number of active worksheets in the open workbooks.

"origin"

Returns the absolute cell reference of the top and leftmost cell visible in the window, based on the current scrolling position, as text prepended with "$A:". This value is intended for for Lotus 123 release 3.x compatibility. The actual value returned depends on the current reference style setting. Using D9 as an example, the return value would be:

A1 reference style "$A:$D$9".

R1C1 reference style "$A:R9C4"

"osversion"

Current operating system version, as text.

"recalc"

Current recalculation mode; returns "Automatic" or "Manual".

"release"

Version of Microsoft Excel, as text.

"system"

Name of the operating environment:
Macintosh = "mac"
Windows = "pcdos"

Important In previous versions of Microsoft Office Excel, the "memavail", "memused", and "totmem" type_text values, returned memory information. These type_text values are no longer supported and now return a #N/A error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example

Create a blank workbook or worksheet.

Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help

Press CTRL+C.

In the worksheet, select cell A1, and press CTRL+V.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=INFO("numfile")

Number of active worksheets (varies)

=INFO("recalc")

Recalculation mode for the workbook (Automatic or Manual)


See Also
Excel > Function reference > Information
ISEVEN function
Excel 2007
Returns TRUE if number is even, or FALSE if number is odd.
Syntax
ISEVEN(number)
Number is the value to test. If number is not an integer, it is truncated.
Remark
If number is nonnumeric, ISEVEN returns the #VALUE! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example

Create a blank workbook or worksheet.

Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help

Press CTRL+C.

In the worksheet, select cell A1, and press CTRL+V.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ISEVEN(1)

Checks whether 1 is even (FALSE)

=ISEVEN(2.5)

Checks whether 2.5 is even (TRUE)

=ISEVEN(5)

Checks whether 5 is even (FALSE)


See Also
Excel > Function reference > Information
IS functions
Excel 2007
This article describes the formula syntax and usage of the IS functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.
Description
Each of these functions, referred to collectively as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if the value argument is a reference to an empty cell; otherwise it returns FALSE.
You can use an IS function to get information about a value before performing a calculation or other action with it. For example, you can use the ISERROR function in conjunction with the IF function to perform a different action if an error occurs:
=IF(ISERROR(A1), "An error occurred.", A1 * 2)
This formula checks to see if an error condition exists in A1. If so, the IF function returns the message "An error occurred." If no error exists, the IF function performs the calculation A1*2.
Syntax
ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)
The IS function syntax has the following argument (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

value Required. The value that you want tested. The value argument can be a blank (empty cell), error, logical value, text, number, or reference value, or a name referring to any of these.
Function

Returns TRUE if

ISBLANK

Value refers to an empty cell.

ISERR

Value refers to any error value except #N/A.

ISERROR

Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

ISLOGICAL

Value refers to a logical value.

ISNA

Value refers to the #N/A (value not available) error value.

ISNONTEXT

Value refers to any item that is not text. (Note that this function returns TRUE if the value refers to a blank cell.)

ISNUMBER

Value refers to a number.

ISREF

Value refers to a reference.

ISTEXT

Value refers to text.

Remarks

The value arguments of the IS functions are not converted. Any numeric values that are enclosed in double quotation marks are treated as text. For example, in most other functions where a number is required, the text value "19" is converted to the number 19. However, in the formula ISNUMBER("19"), "19" is not converted from a text value to a number value, and the ISNUMBER function returns FALSE.

The IS functions are useful in formulas for testing the outcome of a calculation. When combined with the IF function, these functions provide a method for locating errors in formulas (see the following examples).
Example 1
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?

Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help

Press CTRL+C.

In Excel, create a blank workbook or worksheet.

In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

C

Formula

Description

Result

=ISLOGICAL(TRUE)

Checks whether TRUE is a logical value

TRUE

=ISLOGICAL("TRUE")

Checks whether "TRUE" is a logical value

FALSE

=ISNUMBER(4)

Checks whether 4 is a number

TRUE

=ISREF(G8)

Checks whether G8 is a valid reference

TRUE

=ISREF(XYZ1)

Checks whether XYZ1 is a valid reference

FALSE


Example 2
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?

Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help

Press CTRL+C.

In Excel, create a blank workbook or worksheet.

In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

3

4

5

6

7

8

9

10

11

12

13

14


A

B

C

Data



Gold



Region1



#REF!



330.92



#N/A



Formula

Description

Result

=ISBLANK(A2)

Checks whether cell A2 is blank.

FALSE

=ISERROR(A4)

Checks whether the value in cell A4, #REF!, is an error.

TRUE

=ISNA(A4)

Checks whether the value in cell A4, #REF!, is the #N/A error.

FALSE

=ISNA(A6)

Checks whether the value in cell A6, #N/A, is the #N/A error.

TRUE

=ISERR(A6)

Checks whether the value in cell A6, #N/A, is an error.

FALSE

=ISNUMBER(A5)

Checks whether the value in cell A5, 330.92, is a number.

TRUE

=ISTEXT(A3)

Checks whether the value in cell A3, Region1, is text.

TRUE


See Also
Excel > Function reference > Information
ISODD function
Excel 2007
Returns TRUE if number is odd, or FALSE if number is even.
Syntax
ISODD(number)
Number is the value to test. If number is not an integer, it is truncated.
Remark
If number is nonnumeric, ISODD returns the #VALUE! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example

Create a blank workbook or worksheet.

Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help

Press CTRL+C.

In the worksheet, select cell A1, and press CTRL+V.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A

B

Formula

Description (Result)

=ISODD(1)

Checks whether 1 is odd (TRUE)

=ISODD(2.5)

Checks whether 2.5 is odd (FALSE)

=ISODD(5)

Checks whether 5 is odd (TRUE)


See Also
Excel > Function reference > Information
N function
Excel 2007
Returns a value converted to a number.
Syntax
N(value)
Value is the value you want converted. N converts values listed in the following table.
If value is or refers to

N returns

A number

That number

A date, in one of the builtin date formats available in Microsoft Excel

The serial number of that date

TRUE

1

FALSE

0

An error value, such as #DIV/0!

The error value

Anything else

0

Remarks

It is not generally necessary to use the N function in a formula, because Excel automatically converts values as necessary. This function is provided for compatibility with other spreadsheet programs.

Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel for the Macintosh uses a different date system as its default.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example

Create a blank workbook or worksheet.

Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help

Press CTRL+C.

In the worksheet, select cell A1, and press CTRL+V.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A


Data

7

Even

TRUE

4/17/2008

Formula

Description (Result)

=N(A2)

Because A2 contains a number, it is returned (7)

=N(A3)

Because A3 contains text, 0 is returned (0, see above)

=N(A4)

Because A4 is the logical value TRUE, 1 is returned (1, see above)

=N(A5)

Because A5 is a date, the serial number is returned (varies with the date system used)

=N("7")

Because "7" is text, 0 is returned (0, see above)


See Also
Excel > Function reference > Information
NA function
Returns the error value #N/A. #N/A is the error value that means "no value is available." Use NA to mark empty cells. By entering #N/A in cells where you are missing information, you can avoid the problem of unintentionally including empty cells in your calculations. (When a formula refers to a cell containing #N/A, the formula returns the #N/A error value.)
Syntax
NA( )
Remarks

You must include the empty parentheses with the function name. Otherwise, Microsoft Excel will not recognize it as a function.

You can also type the value #N/A directly into a cell. The NA function is provided for compatibility with other spreadsheet programs.
See Also
Excel > Function reference > Information
TYPE function
Excel 2007
Returns the type of value. Use TYPE when the behavior of another function depends on the type of value in a particular cell.
Syntax
TYPE(value)
Value can be any Microsoft Excel value, such as a number, text, logical value, and so on.
Remarks

TYPE is most useful when you are using functions that can accept different types of data, such as ARGUMENT and INPUT. Use TYPE to find out what type of data is returned by a function or formula.

You cannot use TYPE to determine whether a cell contains a formula. TYPE only determines the type of the resulting, or displayed, value. If value is a cell reference to a cell that contains a formula, TYPE returns the type of the formula's resulting value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example

Create a blank workbook or worksheet.

Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help

Press CTRL+C.

In the worksheet, select cell A1, and press CTRL+V.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A


Data

Smith

Formula

Description (Result)

=TYPE(A2)

Checks the type of the value above (2)

=TYPE("Mr. "&A2)

Checks the type of "Mr. Smith" (2)

=TYPE(2+A2)

Checks the type of the formula, which returns the error #VALUE! (16)

=TYPE({1,2;3,4})

Checks the type of an array constant (64)

