Excel IS Functions - Microsoft Excel IS Functions Tutorial

Excel IS Functions - Microsoft Excel IS Functions Tutorial

Excel IS Functions - Microsoft Excel IS Functions Tutorial

The IS Functions return a TRUE or FALSE value based upon a condition they evaluate. They can detect errors, blank cells, text, numbers...etc. They are critical in the development of dashboard interfaces and are heavily used with the IF function.

What are the Microsoft Excel IS Functions?

The IS functions are a set of computer programs that you run from a worksheet cell formula. Their job is to return a TRUE or FALSE value based upon a condition they evaluate. These functions are basically your worksheet cell value and worksheet formula error testers in logic. Here is a list of the IS functions and what they do:

Function Name Definition
ISBLANK Function Returns TRUE if its argument evaluates to blank, for example an empty cell reference
ISERR Function Detects an error in a formula used as its argument and returns TRUE if detected, it ignores the #N/A error value
ISERROR Function Detects an error in a formula used as its argument and returns TRUE if detected, covers all errors (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!)
ISLOGICAL Function Detects if its argument has the value of TRUE or FALSE and returns TRUE if so
ISNA Function Detects an #N/A error in a formula used as its argument and returns TRUE if detected. It only detects #N/A
ISNONTEXT Function Detects if its argument is not a text value and returns TRUE if so (Note that this function returns TRUE if its argument refers to a blank cell)
ISNUMBER Function Detects if its argument is a number and returns TRUE if so
 ISREF Function Detects if its argument produces a value that can be evaluated as a cell or range reference and returns TRUE if so
ISTEXT Function Detects if its argument produces a text value and returns TRUE if so

The IS functions can be used alone in a worksheet formula as a flagging device when processing data on a worksheet. For example, say you have rows of data on a worksheet and you wanted to know if any of those rows had a number in a particular column. You could use the ISNUMBER function to evaluate that case and return TRUE/FALSE. You could then use Excel's AutoFilter to filter on the TRUE/FALSE values returned. The IS worksheet functions are also extremely useful when developing error traps on your worksheet thus preventing your worksheet formulas from generating errors a user can see. They are also useful when designing dashboards especially interpreting what a user has typed in a cell so a formula can adjust to the input. They are very commonly used as a nested function to the first argument of the IF function especially in the cases of error protection and user value interpretation (what the user types in a cell). The AND and OR functions also heavily use these functions as nested functions in their argument list. A complement to these functions is also the Excel Data Validation feature which also helps protect against the user typing the wrong values on a worksheet. Remember, locking down what the user can type is always more effective then trying to have a formula figure out what they typed. You run the IS worksheet functions by typing their names in a formula then followed by the information they are suppose to analyze.


How Do You Type the IS Worksheet Functions in a Formula?

Whenever you type a formula in a worksheet cell, this is called syntax or grammar. The general syntax of the IS functions are like this when you type them in a worksheet cell:

Function Name Argument Purpose
= ISBLANK( value ) The value argument can be a cell reference, range reference or formula that identifies a reference. If the cell or range is blank, this function returns TRUE
= ISERR( value ) The value argument can be a cell reference, range reference or formula. If a calculation error is produced excluding #N/A, this function returns TRUE
= ISERROR( value ) The value argument can be a cell reference, range reference or formula. If a calculation error is produced including #N/A, this function returns TRUE
= ISLOGICAL( value ) The value argument can be a constant, cell reference, range reference or formula. If they evaluate to TRUE or FALSE, this function returns TRUE
= ISNA( value ) The value argument can be a cell reference, range reference or formula. If a calculation error is produced generating #N/A, this function returns TRUE
 = ISNONTEXT( value ) The value argument can be a constant, cell reference, range reference or formula. If argument evaluates to a non-text value, this function returns TRUE (Note that this function returns TRUE if the value refers to a blank cell)
= ISNUMBER( value ) The value argument can be a constant, cell reference, range reference or formula. If argument evaluates to a number value, this function returns TRUE (Note that this function returns FALSE if the value refers to a blank cell)
 = ISREF( value ) The value argument can be a formula. If argument evaluates to an Excel range or cell reference, this function returns TRUE (check out the INDIRECT function)
= ISTEXT( value ) The value argument can be a constant, cell reference, range reference or formula. If argument evaluates to a text value, this function returns TRUE (Note that this function returns FALSE if the value refers to a blank cell)

Where value is called the function argument list. Remember, you are running a computer program at this point so the program needs information to operate and that is why there is an argument list. When you see an argument list and you see square brackets [ ] around the argument name, this means the argument is optional and you do not have to include it when typing unless you need it. So for the syntax above, all IS functions take one argument when typing them in a worksheet cell formula. What argument values can be used are discussed above and below this paragraph. Remember functions expect certain things in their argument lists, if you do not put the correct information in the list they will generate an error when run.


How Do You Run the IS Functions?

Since the IS functions are computer programs, they run when you press Enter to enter the formula that contains them. If any of the arguments are wrong, the function will return an error.


What Do I Type for an IS Function Argument?

When typing the IS function in a worksheet cell formula, you need to replace the value argument. Some typical arguments you can use are:

Argument Type Cell Formula Example Explanation
Cell Reference =ISNUMBER( A1 ) Determines if the value in cell A1 is a number
Range Reference =ISTEXT( A1:A10 ) Determines if the values in cell A1:A10 are text
Nested Function =IF( ISERROR( A1/A2 ), 0, A1/A2 ) ISERROR used as a nested function to the IF function. Determines if the formula A1/A2 (division by 0 is always a problem) generates an error and if it does, IF outputs 0 controlling the error. If no error then IF outputs the calculated value of A1/A2
Formula =ISREF( INDIRECT( "C1:C" & COUNTA( C:C )) ) Uses INDIRECT as a nested function to build a range reference, determines if INDIRECT generates a reference by returning TRUE
Cell and Range Names =ISBLANK( Sales_2012 ) Determines if the cell named Sales_2012 is blank *

* It is possible to name a cell or group of cells on a worksheet and use that name in place of a range reference or cell reference. Consult Excel help on how to name a cell.


Additional IS Function Examples

The worksheet seen below contains some typical worksheet formulas that run the IS worksheet function. Pay close attention to the argument list and the syntax used to write the formula.

A B C
1 Part# Cost  
2 11234AX =IF( ISBLANK( A2 ), "" , VLOOKUP( A2, Parts_Table, 3, False ) Typical use of the ISBLANK function being used in an IF function to detect if someone has typed in cell A2. If they have, VLOOKUP runs. If not empty string is returned blanking the cell when not in use
3      
4 1 =SUM( IF( NOT( ISERROR( A4:A7 ) ), A4:A7, FALSE ) ) Typical use of the ISERROR function being used to protect an array formula from erroring. ISERROR returns an array of TRUE/FALSE from range A4:A7. NOT flips the array from FALSE to TRUE and from TRUE to FALSE so all cells without errors register as TRUE. IF/a> takes the TRUE/FALSE array and only returns the values from A4:A7 that have no errors. SUM then takes the values and adds them. This is an array formula so CTRL+Shift+Enter must be pressed when entering into a cell.
5/td> 2
6 #DIV/0!    
7 4    

Things to Know About the IS Functions

Next Excel Training Dates - Public Training and Live Online Courses

Our next public Excel training workshops and live online Excel courses are listed below. For all of the dates and times, please visit our Training Schedule page using the link above in the main menu.

Business, Engineering and Science
Beginning Excel Training for Business Professionals Intermediate Excel Training for Business Professionals Advanced Excel Training for Business Professionals Beginning Excel VBA Training for Business Professionals Intermediate Excel VBA Training for Business Professionals Advanced Excel VBA Training for Business Professionals Advanced Excel Training for Engineers / Scientists Beginning Excel VBA Training for Engineers / Scientists Intermediate Excel VBA Training for Engineers / Scientists Advanced Excel VBA Training for Engineers / Scientists Microsoft Excel Dashboards Training for Combined Audience
Beginning Excel Training for Business
Next Date:
See Schedule
Intermediate Excel Training for Business
Next Date:
See Schedule
Advanced Excel Training for Business
Next Date:
See Schedule
Beginning Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Intermediate Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Advanced Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Advanced Excel Training for Engineers / Scientists
Next Date:
See Schedule
Beginning Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Intermediate Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Advanced Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Microsoft Excel Dashboard Training
Next Date:
See Schedule

Public Training Location

  • Santa Barbara, CA
  • Los Angeles, CA
  • Pasadena, CA
  • Reseda, CA
  • Chatsworth, CA
  • Topanga, CA
  • Santa Clarita, CA
  • Newbury Park, CA
  • Camarillo, CA
  • Calabasas, CA
  • Valencia, CA
  • West Hollywood, CA
  • Goleta, CA
  • Palmdale, CA
  • San Fernando, CA
  • Burbank, CA
  • La Canada, CA
  • Altadena, CA
  • Culver City, CA
  • Rosemead, CA
  • Anaheim, CA
  • El Segundo, CA
  • Fillmore, CA
  • Gardena, CA
  • Encino, CA
  • Oxnard, CA
  • Mojave, CA
  • La Crescenta, CA
  • Glendale, CA
  • Flintridge, CA
  • Beverly Hills
  • Arcadia, CA
  • Inglewood, CA
  • Hawthorne, CA
  • Manhattan Beach, CA
  • Santa Paula, CA
  • Century City, CA
  • Sherman Oaks, CA
  • Reseda, CA
  • Woodland Hills, CA
  • Hollywood, CA
  • Studio City, CA
  • Fillmore, CA
  • Ventura, CA
  • Moorpark, CA
  • Simi Valley, CA
  • Santa Monica, CA
  • Encino, CA

EMAGENIT performs Excel onsite company training in the cities listed above as well as the entire Northern and Southern California areas, United States and Canada.

LinkedIn
Facebook
Information
Need Help? Please call us at 1.866.924.6244

Copyright © 2002-2017

EMAGENIT All Rights Reserved