1. CELL Function
The CELL function returns some specific information about the cell. You can select (which you can specify in the function) from the multiple types of information to get in the result.
Syntax
CELL(info_type, [reference])
Arguments
- info_type: Type of information you want to check about a cell. You have a drop-down to select which type of information you required about the reference.
- [reference]: Cell for which you want to get the information.
Notes
- If you change the format of a cell to update the result in the function then you need to recalculate the worksheet.
Example
In the below example, we have used all the parameters to get information about a cell. You can use this function with other functions where you need to use information about a cell.
2. INFO Function
The INFO function returns information about the current operating environment. You have seven different information types that you can get by using the INFO function.
Syntax
INFO(type_text)
Arguments
- type_text: Information that you need as a result.
Notes
- You have 7 different parameters to get info about your current operating environment.
Example
In the below example, we have used all the parameters to get info about the current operating environment.
3. ISBLANK Function
The ISBLANK function returns TRUE if a cell is blank. In simple words, with the ISBLANK function, you can refer to a cell and check if they are blank or not and if it’s blank then it returns TRUE.
Syntax
ISBLANK(value)
Arguments
- value: A cell or a value that you want to test.
Notes
- ISERROR verifies an error in both of the conditions, either that error is in absolute value or as a result of another formula.
- It will evaluate all types of errors from a cell. #N/A, #DIV/0!, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.
Example
In the below example, we have used ISBLANK with IF to deliver a message to the user if cell F1 is blank.
4. ISERR Function
The ISERR Function returns TRUE if a value is an error other than #N/A. Unlike ISERROR, you can check it considers all the errors except #N/A and if there’s an error then it returns TRUE, else FALSE.
Syntax
ISERR(value)
Arguments
- value: A cell reference or a value from which you want to check the error.
Notes
- ISERR will verify an error in both of the conditions, either that error is in absolute value or as a result of another formula.
- It evaluates all types of error from a cell #DIV/0! #NAME? #NULL! #NUM! #REF!, and #VALUE! but not #N/A. To check #N/A, you can use ISNA and ISERROR.
Example
In the below example, we have used ISERR with IF to get a specific text if there is an error in the cell.
In the below example, we have used ISERR with an array range to check the errors from a range of cells.
5. ISERROR Function
The ISERROR function returns TRUE if a value is an error. You can check it considers all the errors and if there’s an error then it returns TRUE, else FALSE.
Syntax
ISERROR(value)
Arguments
- value: A cell reference or a value from which you want to check the error.
Notes
- ISERROR will verify an error in both conditions, either that error is in absolute value or as a result of another formula.
- It will evaluate all types of errors from a cell. #N/A, #DIV/0!, #NAME?, #NULL!, #NUM!, #REF! & #VALUE!.
Example
In the below example, we have used ISERROR with IF to get a specific text if there is an error in a cell.
In the below example, we have used ISERROR with an array range to check the error from a range of cells.
6. ISEVEN Function
The ISEVEN Function returns TRUE if the supplied value is an EVEN number. In simple words, with ISEVEN you can check that value is an even number or not.
Syntax
ISEVEN(number)
Arguments
- number: The value that you want to evaluate.
Notes
- You can also insert a number into function directly by using double quotes or even without it.
- If you specify a non-numeric value it will return the #VALUE! error value.
Example
Below we have used different arguments:
- It returns TRUE if the given number is even.
- It evaluates negative values in the same way.
- It treats 0 as an even number.
- If you specify a number with decimal points it ignores decimals and evaluates the integer. In this example, it has truncate .5 and evaluated 2.
- As Excel stores date as a serial number, it evaluates them in the same manner.
7. ISFORMULA Function
The ISFORMULA function returns TRUE if the value supplied (or the referred cell) has a formula and if their not formula in the cell it returns a FALSE.
Syntax
ISFORMULA(reference)
Arguments
- reference: A cell reference that you want to evaluate.
Notes
- If cell reference is not a valid reference it will return #VALUE!.
- You can also use the shortcut key Control + ~ to display all the formula in a worksheet.
Example
Below we have used different arguments:
- A simple addition calculation and it returns TRUE.
- A volatile function and it returns TRUE.
- One thing you have to note that ISFORMULA is concerned with the formula in a cell not with the result of a formula. It will return TRUE even if the result of the formula is empty or an error.
- If there is any value other than formula in a cell it will return FALSE.
8. ISLOGICAL Function
The ISLOGICAL function returns TRUE if the value supplied (or the value in the cell referred) is a logical value. With a logical value, means TRUE or FALSE. That means if the value is TRUE or FALSE it returns a TRUE else FALSE.
Syntax
ISLOGICAL(value)
Arguments
- value: The value that you want to evaluate.
Notes
- If cell reference is not a valid reference it will return #VALUE!.
Example
Below we have used different arguments:
- In the FIRST cell, we have entered a simple TRUE value and in the SECOND cell, we have used TRUE function. It has returned TRUE for both of the values as both of the values are logical values.
- In the THIRD and FOURTH cells, we have used FALSE value and FALSE function respectively and it has returned the same result for both of the values.
- In FIFTH and SIXTH, as logical values TRUE and FALSE have numeric values 1 and 0 as well but ISLOGICAL will not treat these number logical values.
- In SEVENTH and EIGHTH, we used text values to evaluate and it returns FALSE.
- In the NINTH, we have used blank cell it returns FALSE and in the TENTH, if value contains an error it will return a #N/A value.
9. ISNA Function
The ISNA function returns TRUE if the value supplied (or the value in the cell referred) is an #N/A error. In simple words, it only considers the #N/A and returns TRUE, and FALSE for everything.
Syntax
ISNA(value)
Arguments
- value: The cell reference or a value that you want to test.
Notes
- It will only consider #N/A, ignore other error values.
Example
In the below example, we have used ISNA to check different error values and we have got TRUE only in the case of #N/A error value.
And in the below example, we have used IF and VLOOKUP with ISNA to deliver a meaningful message to the user.
10. ISNONTEXT Function
The ISNONTEXT function returns TRUE if the value supplied (or the value in the cell referred) is a non-text value. With non-text means, means a number, date, a symbol, etc.
Syntax
ISNONTEXT(value)
Arguments
- value: A cell or a value that you want to test.
Notes
- If a number is enclosed in double quotation marks will be treated as text and formula will return FALSE.
Example
In the below example, we have used it with IF to deliver a message to the user if a non-text value will enter into the cell.
11. ISNUMBER Function
The ISNUMBER function returns TRUE if the value supplied (or the value in the cell referred) is a number. In simple words, it only considers the numeric value and ignores the rest.
Syntax
ISNUMBER(value)
Arguments
- value: A numeric value that you want to check.
Notes
- Numbers that are enclosed in double quotation marks will be treated as text.
Example
In the below example, we have used ISNUMBER with IF to deliver an alert message if a user enters a non-number value in cell F1.
12. ISODD Function
The ISODD function returns TRUE if the value supplied (or the value in the cell referred) is an ODD number. In simple words, if the value is a number which is divisible with 2 it returns TRUE, else FALSE.
Syntax
ISODD(value)
Arguments
- value: Number which you want to test for the odd number.
Notes
- If the value is not a number it will return a #VALUE error.
- If a number is enclosed in double quotation marks it will treat that number as a text and return FALSE.
Example
In the below example, we have used ISODD with IF to deliver an alert message to the user if a number nested in the cell other than an odd number.
13. ISREF Function
The ISREF function returns TRUE is the referred value is a valid reference, else FALSE. In simple words, you can use ISREF to check if a text value is a valid reference or not.
Syntax
ISREF(value)
Arguments
- value: The value for which you want to check for a valid reference.
Notes
- If a valid reference address enclosed in double quotation marks ISREF will not able to test that reference and return FALSE even that reference is valid.
Example
In the below example, the fruit is a valid named range but when you have used double quotation marks, ISREF is unable to test that reference.
14. ISTEXT Function
The ISTEXT function returns TRUE if the value supplied (or the value in the cell referred) is a text. In simple words, it only considers the text and ignores all other types of values.
Syntax
ISTEXT(value)
Arguments
- value: The cell reference or value for which you want to test.
Notes
- Any number enclosed in the double quotation marks will be treated as a text.
Example
In the below example, we have used ISTEXT with IF to create a nesting formula to deliver an alert message if the user enters an invalid name (other than a text).
15. N Function
The N function converts a Boolean into a number. In simple words, it converts TRUE into 1 and FALSE into 2. These numbers are the actual value of the boolean values.
Syntax
N(value)
Arguments
- value: A Boolean which you want to convert to a number
Notes
- In a real-life, N doesn’t have any use as a separate function as Excel can automatically convert Boolean into numbers where needed.
Example
In the below example, we have used N function to insert a comment into a formula.
16. NA Function
The NA function returns the #N/A value. In simple words, the NA function returns the error value #N/A in the result. The best use of the NA function is to get an error when you have empty and missing information in a cell.
Syntax
NA(value)
Arguments
- There’s no argument in NA.
Notes
- You can use NA with functions to returns an #N/A error.
Example
In the below example, we have inserted NA function in the cell A1 and it has simply returned #N/A.
17. SHEET Function
The SHEET function returns the sheet number for the reference used. In simple words, the SHEET function returns the worksheet number of the range you referred to in the function.
Syntax
SHEET(value)
Arguments
- [value]: The name of the sheet or reference to any cell in a sheet
Notes
- It will include all types of sheets (Chart Sheet, Worksheet or Macro Sheet).
- You can refer to sheets even if they are visible, hidden or very hidden.
- If you skip specifying any value in the function it will give you the sheet number of the sheet in which you have applied the function.
- If you specify an invalid sheet name, it will return a #N/A.
- If you specify an invalid sheet reference, it will return a #REF!.
Example
In the below example, we have used different inputs to get the sheet number.
Where we have invalid sheet name and invalid sheet reference, the SHEET function has returned an error.
18. SHEETS Function
The SHEETS function returns a count of the worksheets from the referred range. In simple words, with the SHEETS function you can count how many sheets are in the range you have referred.
Syntax
SHEETS(reference)
Arguments
- reference: The Reference for which you want to count numbers of sheets.
Notes
- It will include all types of sheets (Chart Sheet, Worksheet or Macro Sheet).
- You can refer to sheets even if they are visible, hidden or very hidden.
- If you skip specifying any value in the function it will give you the count of total sheets in a workbook.
- If you specify an invalid reference, it will return a #REF!.
Example
In the below example, we have used 3D cell reference to get the sum of cell A1 from five sheets and we have used the same reference to get the count of sheets.
19. TYPE Function
The TYPE function returns a number that represents the type of the value supplied. In simple words, the TYPE function returns a specific number which represents the type of the supplied value.
Syntax
TYPE(value)
Arguments
- value – Reference of a cell or a value for which you want to test the type.
Notes
- When you a test value from a cell that has a formula, then it will test the value which is return by that formula.
- If you refer to a blank cell, it will return number 1 as a result.
- If you refer to a cell that contains a date, it will return 1 in a result as a date has a value in number.
Example
In the below formula, we have used TYPE with IF.
=IF(TYPE(F3)<>1,”Enter Valid Quantity”,E3*F3)
In this formula, TYPE returns a number that represents the type of the value, and then there’s a condition in the IF. If that number does not equal the 1 which means is the supplied value is not a number and IF returns the message “Enter a Valid Value”. And if it is there, it multiples the quantity with the price.
20. ERROR.TYPE Function
The ERROR.TYPE function returns a number to which represents the type of error you have in a cell. For each error type in Excel, there is a specific number and if there’s no error it returns #N/A.
Syntax
ERROR.TYPE (error_val)
Arguments
- error_val: The value you want to evaluate for error.
Notes
- You can use it with other functions to test errors.
Example
In the below example, we have used ERROR.TYPE with VLOOKUP to show a relevant message whenever an error occurs.
You can use this method to deliver a relevant message to the user.