If you're using Google Sheets, you may have a table of data and you
want to delete different rows based on whether or not it has certain words,
certain numbers, maybe even certain dates. We're going to go through two good ways
to do that and explain the differences in the different techniques. The first
way is to apply a filter. I'm going to select the data by left clicking my
mouse and just dragging to the bottom of it. That just makes it obvious to Google
Sheets what you want to filter. You don't need to do that if it's all continuous
data, but if you have something off to the side or maybe down to the bottom
that you don't want to have filtered, just select exactly what you want to be
careful about. Go to Data and do Create a filter. After I've done that,
you see that these buttons appear at the top of each one of the columns. I did
select the headers as part of my data so it's appearing up at the headers now.
You'll see the flexibility of the filter function here. Let's say we want to
include Central and South and exclude West. There's several different ways to
do this because this is pretty straightforward. We can just do it with
the checkmarks. We can uncheck West, click OK. It goes away and, if that's all you
want to do, then you're done. But, so often there's more flexibility that you need
so let's go through some of the other things happening here. Another way to do
that would be to filter by a condition. In this case, you want the condition to
be that it does not contain text, does not contain West. Click OK. The output is
the same but you could also do this as text does contain, or is empty, is not
empty, etc. There's a lot of flexibility just in these menu options.
Now, to show you the other way, we're going to take this filter off. Just go to
Data and Turn off filter. Over to the right, what we're going
to do is dynamically create a new table with certain conditions that you want
to specify. We're going to do this with the FILTER function. Type = and
FILTER and then, once I hit the opening parenthesis, I'm going to get this helper
text that comes down here and tries to tell me what to do. First it's
reminding me I need a range. You need to tell me what data I'm going to be
working with. Let's give it this range. The same range that we gave the menu
function of the filter and the condition is that we want this FILTER function to
return every row in here except when the value in column B is West, right? So we're
going to say the condition is when something in the range of B1:B17 is
not equal to so that's as <> and then I'm going to put
quotes around this and that's telling Google Sheets that this is a string and
not a number. West. And that's all. So this function can
take multiple ranges and multiple conditions. It can be very flexible
but this is just to show you basically how it works so let's close this off
with the ending parentheses and hit enter. There it goes. It returns
everything that is not in the region of West. I applied this formatting earlier
so it would usually come through as unformatted text so ignore that part. But,
what is happening here, is if I double click in F1, which is where I typed in
the formula, it's still there. If I click in F2, this text is here, but if I
change it, Google Sheets doesn't like that because it's saying whoa that's
actually a value that I returned from the function in F1. if you change that, it
doesn't make sense because it's coming from somewhere else.
So let's undo that change. If I go over here and I change this number to a
five, this updated dynamically. The FILTER function right now is buying you
a few things. One, it is off to the side, so if you don't want to filter this
because you want to show all the data, well you can just create a new list with
the FILTER function. You could even choose to put this on another worksheet
and then control their permissions down here if it's a multiple user worksheet
and then Protect sheet. But that's a whole different subject. I'll link to that in a
card if you want to see it. If you want to change this, just change the values in
the formula. If you want to make this future-proof, so if you add something to
the bottom of this table, you want this list to just pick it up automatically,
you'd have to change these references. You could say A12 you could just do
D100 or you could just say to D. That would pick everything up and B1:B.
Hit enter, still works. If you add something the bottom, let's just add this
one again because it's Central, it will show up, if it was West it wouldn't, let's go
down. There it goes. The last thing. If you want this list to not change, if you
want it to be static, you could just right click, left click on Copy, right
click again in Paste. There we go. Values only. Now if you double click in cell F1,
it's just the text. That function is gone. If I were to change this now, this would
not change. I hope that was helpful. If you like that, you might just enjoy some
other short videos about how to solve practical problems. Oh, I just caught that
the ship dates aren't formatted anymore. Let's make that look nice before we
close out this video. Paste that formatting. It's much better.
Now we can say, if you liked that video, you can check out a lot of other videos
like it. I threw up a playlist on the right hand corner here about other short
little fixes for Google sheets to help you work faster throughout your day.
Thanks for watching and please subscribe if you want to see
more videos just like this.