How to use NOT Function in Google Sheets [With Examples]

how to use NOT Function in Google Sheets 14

Hi. In this article, we will learn a pretty useful NOT function. We will learn how to use NOT function in google sheets.

NOT function is simply an inverting function that can negate a logical expression or value at once. Simple is that, if I pass a true into the NOT function that it will return TRUE, and if I pass true it will return FALSE. It’s very simple, you might be thinking why do we need to use this function? What’s the practical usage of this function, and why and when can we be needed to negate a value of the logical expression? I understand your concerns let me discuss how and when NOT Function helps us work with logical expressions mostly the Boolean values such as True and False.

Use Case of NOT Function in Google Sheets

We many times see some situations even in the real life when we have to declare the true as false and the false as true. Let’s understand with an example, let’s say a shopping mall has several products and has several deals on some specific products, now here they mainly use the NOT function real-life example, the sales manager will say if the sale of a specific product is more than 100 then remove the deal from it to save the product going out of stock, similarly, the other condition is that if the sale of a specific product is less than 20 then start the deal from it to save the product going unsold.

This is how it works in real life, now sales manager is simply doing the thing that is done by the NOT function, not function checks some condition, and based on that it flips the result into true or false, if (condition) = true then flip the Boolean value true or false. This is the best real-life example of NOT function. We will learn it in the context of google sheets so you will understand it better. Therefore we need to learn how to use NOT Function in google sheets.

See also  How to Flip or Reverse Data Order in Google Sheets (Complete Guide)

How to use NOT Function in Google Sheets

Not function is pretty simple, it is simple but the conditions and logical operations used along with NOT function can be complex, we will learn how to deal with them and we will try to understand the functionality and features of NOT function.

How to use NOT Function in Google Sheets – Basic Usage

NOT function Syntax

=NOT(logical_expression)

Here, the logical expression can be anything from if-else, AND, OR, etc.

The basic usage of the NOT function is to inverse a Boolean or logical expression.

Step 1

Have a Boolean value in a cell

how to use NOT Function in Google Sheets 1

Step 2

In any adjacent cell, try =NOT(cell_address), to see the NOT effect.

how to use NOT Function in Google Sheets 2

Step 3

This is how NOT simply inverse the value, TRUE to FALSE and FALSE to TRUE.

how to use NOT Function in Google Sheets 3

 

But this does not make any sense. Using a NOT function like this does not look useful. We will see some real use cases by which you will see how handy NOT function can be.

See also  How to Add Text and Text Box in Google Sheets

Let’s move to some use cases.

How to use NOT Function in Google Sheets – A Simple Use case

In this section, we will see a simple use case of the NOT function, it’s a real-life application of the NOT function and you will find some interest to use the NOT function after learning and seeing this use case.

Here I have some students and their ages. Now I want to check each person’s age and based on age I will decide who is going on a school trip.

A student’s age must be equal to greater than 18 to go on a school trip.

Step 1

Sample data

how to use NOT Function in Google Sheets 4

Step 2

In the third column, use a condition =NOT(B4<18)

how to use NOT Function in Google Sheets 5

The above formula will check the value in cell B4 and compare it with 18, it will check if B4 is less than 18, if true it will return false, and if false it will return true. This is how NOT can inverse any result

Step 3

The result.

how to use NOT Function in Google Sheets 6

Step 4

Now you further need to check if students went on the school trip last year, where the condition is simple, if students are going then they must not have gone last year so we need inverse answers in the next column.

how to use NOT Function in Google Sheets 7

Step 5

Simply you can use the syntax =NOT(C4)

how to use NOT Function in Google Sheets 8

how to use NOT Function in Google Sheets 9

You can say, this can be done using IF, but the syntax of IF is pretty long, and this is not the best of NOT.

The if Syntax for the same purpose

=IF(B4>=18,”True”,”False”)

This is how we can use the NOT function to shorten the syntax and get efficient results.

See also  How to Sort in Google Sheets (Complete Guide)

How to use NOT Function in Google Sheets – With IF Condition

Here in this section, we will learn how to use the NOT function in google sheets with if condition. If the condition is written above, it can also do the same operations as NOT but with long syntax. Let’s see how we can use NOT with IF in google sheets.

For this use case, I have another dummy data set to work on.

I have some products and yesterday sold units. I need to calculate if I should open some kind of offers on products for today, I will determine it looking at yesterday’s sale. If yesterday’s sale is less than 50 then I will open some offer on this product or if the product’s yesterday’s sale yesterday was 50 then I will close the sale because it’s already an average selling product. I hope you have understood the scenario.

Let’s move to practical implementation

Step 1

Sample data

how to use NOT Function in Google Sheets 10

Step 2

Keep “Offer Yesterday” true for every product.

how to use NOT Function in Google Sheets 11

Step 3

We need to determine “Offer Today

how to use NOT Function in Google Sheets 12

Step 4

Write the IF condition in the first row of the “Offer Today” column

how to use NOT Function in Google Sheets 13

Step 5

The overall formula will be

how to use NOT Function in Google Sheets 14

=IF(B4<50,C4,NOT(C4))

Explanation

If the value in the B4 is less than 50, then simply write what’s written in C4, and if not, then return NOT(C4), which means the inverse of the C4.

Step 6

This is how we will get the accurate results

how to use NOT Function in Google Sheets 15

In another use Case of IF with NOT function, we will calculate the rewards for the workers who worked on weekends

For this, I have another data set

Step 1

Sample data

how to use NOT Function in Google Sheets 16

Step 2

Add the formula in the last column in which you want to calculate the reward

how to use NOT Function in Google Sheets 17

Step 3

We are also using the ISBLANK function to check if the cell is blank.

how to use NOT Function in Google Sheets 18

Step 4

The formula will be

how to use NOT Function in Google Sheets 19

=IF(NOT(ISBLANK(L5)),L5*100,”-“)

Step 5

The result

how to use NOT Function in Google Sheets 20

This is how you can do this very simply with the combination of ISBLANK, NOT, and IF

See also  How to use FORECAST Function in Google Sheets (Best Guide)

How to use NOT Function in Google Sheets – With Conditional Formatting

In this section, we will learn how to use NOT Function in Google Sheets with conditional formatting. We have a sample data set for it. Normally we specify a condition in the conditional formatting and according to the custom formula, the formatting is applied to our filtered content. What if I want to reverse this logic? Yes, we can use the NOT function here.

For this example, I have a dataset on which we will apply this logic practically

I have a dataset with the student’s names and their cities. I want to apply conditional formatting where the city is not “Las Vegas”. Hare, if I don’t use NOT Function, so I will have to check all other conditions other than LAs Vegas, so you can understand how complicated it will become without NOT Function. Since we are smart so we will use NOT Function.

Step 1

Sample data

how to use NOT Function in Google Sheets 21

Step 2

Select All (without headers)

how to use NOT Function in Google Sheets 22

Step 3

Go to Format > Conditional Formatting

how to use NOT Function in Google Sheets 23

Step 4

Click on the drop-down icon below the “Format cells if…” section.

how to use NOT Function in Google Sheets 24

Step 5

Select “Custom formula is”

how to use NOT Function in Google Sheets 25

Step 6

Write the condition

how to use NOT Function in Google Sheets 26

=NOT($S5=”Las Vegas”)

Step 7

Choose the formatting from below “Formatting Style” section

how to use NOT Function in Google Sheets 27

Step 8

Click on Done

how to use NOT Function in Google Sheets 28

how to use NOT Function in Google Sheets 29

 

This is how you can make it very easy to apply to negate conditional formatting based on a condition.

Conditional formatting is one of the most common ways that use the NOT function to make negative conditions and save a lot of time and efforts

Note: NOT Function can also be used with logical operator functions like AND and OR to negate after checking two conditions or checking either one of the two conditions.

See also  How to Set Print Area in Google Sheets [Complete Guide]

Important Notes on NOT Function in Google Sheets

  • NOT Function simply negates the logical value or expression
  • NOT can be used with any other function or logical expression such as IF, AND, etc
  • NOT is a function and it can also be used under conditional formatting custom formula
  • NOT Function can also be used with OR/AND functions

Download/Copy Google Sheets Practice Workbook

Note: Do not ask for editors’ access. Simply make a copy and use the template.

Conclusion

Wrapping up how to use NOT function in google sheets. we have learned how to use the NOT function in google sheets, we have seen several use cases of using the NOT Function along with IF and conditional formatting. We learned real-life examples like making an offer looking at yesterday’s sale, and formatting the values where the city is not Las Vegas. These conditional logics are very easy with the NOT function if we use other ways to perform this logic, we may have to cover a longer syntax, and also the margin of error will be increased. NOT Function makes it very easy to negate any logical value and expression easily in one step.

I hope you find this article helpful. I will see you next time. Take care, and keep learning with Office Demy.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general