- [Instructor] All right, if you're using Google Sheets
and you have a table of data
like this one right here,
and it's a long table,
so you can't do what we're going to talk about manually,
but it has a blank rows that you want to get rid of,
we're going to go over a couple of different ways
to do that,
depending on how your data is structured
and where you want the output.
(gentle upbeat music)
So the first technique that we're going to use
actually leaves the data in the original columns.
And we'll just shift it up as the rows are removed.
And if that's okay to leave it there,
then this is probably the best technique to use.
What we're going to do is you just filter the data.
And in order to do that,
you have to be careful because if you have blank rows,
sheets might think that your table stops here.
So you're not going to get rid of the rows
because it's not going to put them in the data range.
So when you select your range,
left click on the column letter where it starts,
and while keeping that left mouse key down,
go over to the right until it stops.
So because we did it that way,
you'll see that the range continues
even below the empty rows.
If you didn't do it that way,
there's a good chance that this example would stop
at row 12.
All right, so now that you have the right range,
select the good data, and then just click on,
create a filter.
By the way, these menus are going to change slightly
within a month or two when this video was made.
So even though it looks a little bit different,
you're just looking to create a filter.
So you can find that, left click on it
and you get these upside down.
Well, I guess it was
we used to say when a triangle's upside down, right?
You get these triangles in their little controls.
And when you left click on one, you get these options.
In this case, this probably needs to remain sorted
in numerical order,
because it has this ID column.
Yours may be different
and we'll go over different options for that later.
But assuming you just want to keep it in that order,
be real handy just to do sort A to Z.
And when you do that,
it's going to sort the blank rows to the bottom.
So now they're at the bottom,
you've effectively gotten rid of them.
If you scroll down,
so that's all you need to do,
then you're done at this point.
You got rid of your blank rows.
But if you want to leave this data in place
and just return the data someplace else
without the blank rows,
but maybe you're still working with us
and you don't want to change it,
let's undo what we just did.
I'll left click one more time
to get rid of that filter control.
And we'll go over an easy way
to basically recreate what we just did,
but to make a standalone independent list
where if things get added to the top or the bottom
and more blank rows come in,
this list will be dynamic and it will update.
So first, let's grab the header.
When you sort, you don't want to sort the header with it.
So we'll just treat it like it's its own special row.
We'll go one below it,
and we're going to use the SORT function.
So this is similar to sorting the data in place,
but it creates a new dynamic list.
The range is going to be A2.
And what we're going to do here
is grab everything over to column D
and then all the way down to the bottom
of the entire spreadsheet.
So let's not designate an ending row number.
And then that way,
you'll be sure you get everything
when a comma,
and then this next argument that the function needs
is what column do you want to sort it by?
All right, so in my example here on the left,
it's pretty apparent that you want to do it
on the first column,
because it's supposed to be sorted by the ID number.
That could be different for you.
Let's act like we just want to do the second column
for some reason.
You don't want to say column B,
you wanna say two
as in the second column.
And then whether or not it is ascending,
we'll just say true.
So it'll put the first letter A at the top,
and then that's enough.
We'll close that off with an ending parentheses
and hit enter.
And that returns your data.
There are no blank rows in it,
it really put them at the bottom, but you can't see them
'cause they're completely blank.
And here's the nice thing about this.
So if you come in here and let's say you added 11.5,
for some reason
and you added this really great person, Adam Stainforth.
And I just happened to be alphabetically
the first person in that list.
We'll do this terribly long email address
that I probably need to get rid of at some point.
You'll notice that came in to the results
of this SORT function dynamically.
So you definitely use this sort technique at this list
is constantly getting updated, all right?
And so if we want to,
and let's just say,
we want to sort this by ID instead of first name.
All you do is go back into the sort,
change it to the first column and it will do it by ID.
Now, if we go into this sort
and we sort it opposite for some reason,
if you have to say false,
that's going to put the blank rows at the top,
and if you don't want that, there is a way around this.
So we can use a different function.
Let's start over,
and you can even start with this function if you want.
It's just that the sort is a little bit cleaner.
We can use the FILTER function
and the range will be the same range.
(indistinct)
And the condition one,
we're going to say, let's look at column A
and you have to specify something the same size.
So we'll say all the way down to the end of column A,
and all of that is not equal to,
so less than and greater than blank.
And blank is just two quotes with nothing in between them.
So that means blank, there's nothing there.
You're done with the FILTER function.
Let's close that off.
And what that is doing,
it's not returning empty rows either.
The empty rows aren't making it through that filter.
So there's a little bit more happening in this function.
It's not too bad.
And it does produce a little bit cleaner of a result
because there's no blank rows at the end.
So if those are causing the problem, use that.
And if you want to step this up even one more level,
you could use the ISBLANK function.
So you could say
if the ISBLANK function on that same range returns false,
so there's something.
Check to see if it's blank.
If it's blank, it will come back true.
So the falses are the ones
that will make it through the filter.
Hit enter, and that works too.
So there's absolutely no reason
why you would need to use this even fancier function.
But I just wanted to show it to you, what can I say?
All right, and the last option that we have
is a function that can introduce more flexibility
if you want to do additional things
with what you're returning.
So the sort and the filter are relatively easy,
they're pretty fast,
but you could also run the QUERY function.
And you would tell query
that you're working with columns A through D.
And since this works with query language,
it's different than what you're used to
with regular spreadsheets.
But you would say where A is not no, right.
And we'll say there's one header on here, hit enter.
And this returns the data where it's not no,
where column one isn't empty.
You would use the QUERY function
if you wanted to add in other operators here
after you've removed the blank rows.
And if you're working with ELISA data
and you just want to identify any duplicates
that might be in that list,
we'll check out how to do that in this next video.
Thanks for watching.
And if you made it all the way to the end,
I wanted to share a little celebration with you.
So it's the small things in life that are exciting.
I've been making these videos for five-ish years
and I've made over 200 of them.
And I just figured out that I could make this screen
a lot bigger while still having 4K resolution.
And this to me is important.
To you, you might not really care.
It's why I left it till the end.
But if I put up my settings, this was set at 150.
I saw other people making videos
where they were just always Zoomed in,
but they looked clear
and I think this setting was just higher.
So I changed it to 250
and it looks a lot better for you guys.
So I won't be zooming in and out on the screen
nearly as much, I don't think.
So that will save you from getting seasick
and it will save me time editing my videos.
Thanks for watching.