Excel NOT Function
Summary
The Excel NOT function returns the opposite of a given logical or boolean value. When given TRUE, NOT returns FALSE.When given FALSE, NOT returns TRUE. Use the NOT function to reverse a logical value.
Purpose
Reverse arguments or results
Return value
A reversed logical value
Syntax
=NOT (logical)
Arguments
- logical - A value or logical expression that can be evaluated as TRUE or FALSE.
Usage notes
Use the NOT function to reverse a value or logical argument:
- When logical is FALSE, NOT returns TRUE.
- When logical is TRUE, NOT returns FALSE.
A common use case for NOT is to reverse the behavior of another function. For example, If the cell A1 is blank, then the formula =ISBLANK(A1) will return TRUE. NOT can be used to reverse this result to FALSE like this:
In essence, by adding NOT, you are able to create a formula that behaves like ISNOTBLANK, which doesn't exist in Excel.
NOT formula examples
If you want to do something specific when a cell is NOT this or that (i.e. is NOT equal to X or Y, etc.) you can use the IF function in combination with the NOT and OR functions to run a test, then take one action if...
If you want to highlight the differences between two columns of data with conditional formatting you can do so with a simple formula that uses the" not equal to" operator (e.g. ) and mixed references.
For example, if...
To count the number of cells that contain errors, you can use the ISERR and NOT functions, wrapped in the SUMPRODUCT function. In the generic form of the formula (above) rng represents the range in which you'd like to...
To sum results from formulas only, you can use the SUMPRODUCT and ISFORMULA functions. In the example shown, the formula in F6 is:
=SUMPRODUCT(sales*ISFORMULA(sales))
where "sales" is the named range C5:C13, values...
To test if all values in a range are at least a certain threshold value, you can use the COUNTIF function together with the NOT function. In the example shown, the formula in G5 is:
=NOT(COUNTIF(B5:F5,"<65...
If you want to use conditional formatting to highlight cells that are NOT between two values (a lower and upper limit), you can use a simple formula that returns TRUE when a value meets that condition. For example, if...
To highlight values in one column when values in one or more other columns are blank, you can use the OR function and a basic logical expression to test for blank or empty values. In the example shown, conditional...
To test multiple cells, and return the value from the first non-blank cell, you can use a formula based on the IF function. In the example shown, column D holds task complete dates. The formula in cell F5 is:
=IF(B5...
To take an action only when the cell is not blank (not empty), you can use a formula based on the IF function. In the example shown, column D holds task complete dates. The formula in cell E5 is:
=IF(D5<>"...
To filter and extract data based on multiple complex criteria, you can use the FILTER function with a chain of expressions that use boolean logic. In the example shown, the formula in G5 is:
=FILTER(B5:E16,(LEFT(B5:...
To show a checkmark when a task is complete, you can use a formula based on the IF and CHAR functions. In the example shown, the formula in D5, copied down, is:
=IF(C5="complete",CHAR(252),"")...
To lookup data based on multiple complex criteria, you can use the XLOOKUP function with multiple expressions based on boolean logic. In the example shown, the formula in G5 is:
=XLOOKUP(1,(LEFT(B5:B16)="x...
Related videos
In this video, we'll look at how to use conditional formatting to highlight entire rows using multiple criteria.
Related functions
