The NOT function in Excel returns a logically opposite value. In this article, you’ll learn to use this function and also discuss its application with VBA Code.
The above screenshot is an overview of the article which represents the application of the NOT function in Excel. To clarify, in the following sections, you’ll learn more about the uses of Excel’s NOT function with its basics.
As a note, we have used Microsoft Excel 365 version, you may use any other version at your convenience.
Excel NOT Function: Syntax & Arguments
The NOT function reverses (opposite of) a Boolean or logical value. In simple terms, if you enter TRUE, the function returns FALSE, and vice versa.
Function Objective:
NOT function always returns a logically opposite value.
Syntax:
=NOT(logical)Argument Explanation:
| Argument | Required/Optional | Explanation |
|---|---|---|
| Logical | Required | A logical value that can be evaluated either TRUE or FALSE |
Return Parameter:
Reversed logical value i.e. changes FALSE to TRUE, or TRUE to FALSE.
Version:
The NOT function was introduced in Excel 2007 and is available in all versions after that.
1. Basic Example of NOT Function in Excel
First and foremost, let’s explore the most basic example of the NOT function, which alters the logical values TRUE and FALSE. In the following figure, the B5 cell contains TRUE, the NOT function returns the opposite FALSE in the C5 cell. Normally, 0 is considered FALSE in Excel, so the NOT function returns TRUE with 0. In the case of any other number, the output will be FALSE.
2. Omitting a Certain Value Using NOT Function
Alternatively, we can use the NOT function to exclude a specific cell value using the formula below.
=NOT(B5="TV")
For example, the B5 cell refers to the input TV. Specifically, the function returns FALSE for TV and TRUE for all other products since we want to exclude only TV.
3. Using NOT Function for Obtaining Greater Than or Less Than Value
Conversely, we can also check whether a cell value is less than a specific value. In this case, we want to filter the products whose prices are less than $200, upon fulfilling the condition, the function outputs TRUE.
=NOT(C5>200)
For instance, the C5 cell indicates the Price of the TV which is $500.
4. Checking If One or More Criteria Are Met
Moreover, we can combine the OR function with the NOT function to check if one or more criteria are met, and yield TRUE or FALSE respectively. In this situation, any Product other than TV and AC will return TRUE.
=NOT(OR(B5="TV",B5="AC"))
In the above equation, the B5 cell points to the Product TV.
Formula Breakdown:
- OR(B5=”TV”,B5=”AC”) → checks whether any arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE. Here, the functions check if the text in the B5 cell is TV or AC, if one of the conditions holds then the function returns TRUE.
- Output → TRUE
- NOT(OR(B5=”TV”,B5=”AC”)) → becomes
- NOT(TRUE) → changes FALSE to TRUE, or TRUE to FALSE. Here, the function returns the opposite of TRUE which is FALSE.
- Output → FALSE
5. Ensuring Both Criteria Are Met in Excel
Similarly, we can also utilize the AND function in conjunction with the NOT function to specify the condition where both criteria are met. On this occasion, we want to exclude the Product TV made by Manufacturer Silo Digital.
=NOT(AND(B5="TV",C5="Silo Digital"))
For example, the B5 and C5 cells represent the Product TV and the Manufacturer Silo Digital.
Formula Breakdown:
- AND(B5=”TV”,C5=”Silo Digital”) → checks whether all the arguments are TRUE, and returns TRUE if all the arguments are TRUE. Here, B5=”TV” is the logical1 argument, and C5=”Silo Digital” is the logical2 argument since both conditions are met, so the AND function returns the output TRUE.
- Output → TRUE
- NOT(AND(B5=”TV”,C5=”Silo Digital”)) → becomes
- NOT(TRUE) → here, the function outputs the opposite of TRUE which is FALSE.
- Output → FALSE
6. Constructing Logical Statements
Furthermore, we can combine the IF function with the NOT function to construct logical statements. Here, we want to avoid buying a TV or AC, and if the criteria hold, the result will be shown as “Don’t buy” (represents TRUE).
=IF(NOT(OR((B5="TV"),(B5="AC"))),"To buy","Don't buy")
In the above expression, the B5 cell refers to TV.
Formula Breakdown:
- OR((B5=”TV”),(B5=”AC”)) → checks whether any arguments are TRUE and return TRUE or FALSE. Returns FALSE only if all arguments are FALSE. Here, the functions check if the text in the B5 cell is TV or AC, if one of the conditions holds then the function returns TRUE.
- Output → TRUE
- NOT(OR(B5=”TV”,B5=”AC”)) → becomes
- NOT(TRUE) → changes FALSE to TRUE, or TRUE to FALSE. Here, the function returns the opposite of TRUE which is FALSE.
- Output → FALSE
- IF(NOT(OR((B5=”TV”),(B5=”AC”))),”To buy”,”Don’t buy”) → becomes
- IF(FALSE,”To buy”,”Don’t buy”) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, FALSE is the logical_test argument because of which the IF function returns the value of “Don’t buy” which is the value_if_false argument. Otherwise, it would return “To buy” which is the value_if_true argument.
- Output → “Don’t buy”
7. Checking for Blank Cell in Excel
Besides, we can utilize the ISBLANK function and the NOT function to check for blank cells and employ the IF function to return the result based on the prior output. For instance, the price hike of some products is shown as an Extra Price, in that case, the price of the product is discounted by 10%. Here, the products without Extra Price are not considered.
=IF(NOT(ISBLANK(E5)), E5*10%, "No discount")
Specifically, the E5 cell indicates the Extra Price.
Formula Breakdown:
- ISBLANK(E5) → checks whether a reference is to an empty cell, and returns TRUE or FALSE. Here, E5 is the value argument that refers to the Extra Price. Now, the ISBLANK function checks whether the Extra Price cell is blank. It returns TRUE if blank and FALSE if not blank.
- Output → FALSE
- NOT(ISBLANK(E5)) → becomes
- NOT(FALSE) → Here, the function flips the FALSE value to TRUE.
- Output → TRUE
- IF(NOT(ISBLANK(E5)), E5*10%, “No discount”) → becomes
- IF(TRUE,E5*10%, “No discount”) → In this case, TRUE is the logical_test argument because of which the IF function returns E5*10% which is the value_if_true argument. Otherwise, it would return “No discount” which is the value_if_false argument.
- 100 * 10% → 10
8. Applying VBA Code
Last but not least, you can apply Excel’s NOT function to check if a cell contains a numeric value with the VBA code shown below. It’s simple and easy; just follow along.
📌 Steps:
- First, navigate to the Developer tab >> click the Visual Basic button.
Now, this opens the Visual Basic Editor in a new window.
- Second, go to the Insert tab >> select Module.
For your ease of reference, you can copy the code from here and paste it into the window as shown below.
Sub Excel_NOT_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("NOT VBA")
'apply the Excel NOT function
ws.Range("C5").Formula = "=NOT(ISNUMBER(B5))"
ws.Range("C6").Formula = "=NOT(ISNUMBER(B6))"
ws.Range("C7").Formula = "=NOT(ISNUMBER(B7))"
ws.Range("C8").Formula = "=NOT(ISNUMBER(B8))"
ws.Range("C9").Formula = "=NOT(ISNUMBER(B9))"
End Sub
⚡ Code Breakdown:
Now, we’ll explain the VBA code, which is divided into two steps.
- In the first portion, the sub-routine is given a name, here it is Excel_NOT_Function().
- Next, define the variable ws for storing the Worksheet object and enter the worksheet name, here it is “NOT VBA”.
- In the second potion, use the NOT and ISNUMBER functions to check if the specified B5, B6, B7, B8, and B9 cells (input cells) contain numeric or text data.
- Now, use the Range object to return the result to the C5, C6, C7, C8, and C9 cells (output cells).
- Third, close the VBA window >> click the Macros button.
This opens the Macros dialog box.
- Following this, select the copy_and_paste_data macro >> hit the Run button.
Finally, the results should look like the screenshot given below.
Common Errors While Using the NOT Function
| Error | Occurrence |
|---|---|
| #VALUE! | Occurs when the cell range is inserted as input |
Practice Section
Here, we have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Download Practice Workbook
Conclusion
To sum up, this article describes 8 examples of how to use the NOT function in Excel. Now, we suggest you read the full article carefully and apply the knowledge in our free practice workbook.
