- All right, if you're using Google Sheets
and you have a table of data,
let's say just like the one right here,
and what you want to do in this example here
at least is that you want to separate out the rows
based on the value of the city
and put them each on their own worksheet.
So, when we're done doing this
we want to have a worksheet that says Boston
with all of the rows that belong to Boston
and then a new worksheet for Pittsburgh,
a new worksheet for Buffalo.
So, we're going to do this two ways
in this first worksheet
where we're just going to do a kind of a manual way,
it's not very hard
but if you had 12, 15, 200 values in this column,
the second technique that we're going to show you
automates the process and gives you a predictable result
with just a few clicks.
So, the first way that we can do this
is you would go down to the lower left hand corner,
click on the plus sign to add a sheet,
and we'll call this first one, let's see,
what was our first city?
Boston, we'll double click on sheet two, rename it Boston.
And then we'll want to grab our header row,
so, highlight your header, right click on it, click Copy,
come into A1 on our new sheet and paste that header.
And then we'll come into the first cell
where our table data starts, which is A2 in this case,
and we're going to use the filter function.
And what the filter function does is that
it looks at a certain table of data
so that'll be where we started,
and then it will filter out everything
except the criteria that we specify.
So, I'll show you what that means we're going to do filter,
and then it's asking for the range.
And the range would be, let's go over to this other sheet
and you'll look at the screen,
this is still showing our formula.
And it starts out with the name of the sheet
and an explanation point.
That's how you reference another sheet in Google Sheets.
And it's A2, I'm going to hold down the shift key
and use the arrow keys here.
And the control key, I'll go all the way down,
and this is our range. A2 to E1001.
So, we want to filter that table, I'll enter a comma,
and for the range where the criteria is
we'll go to column E because that's where the cities are,
go all the way down to the bottom.
And what we're looking for is when that equals
start out with a quote
because this is just a string of characters, not a number.
Boston.
Close that off, hit Enter,
and the filter function returns all of the rows
where column E is equal to Boston.
So, you could just repeat that process, right?
Let's go back to the data, we'll go back up to the top.
All right, several different steps here,
but if you only have to do this a few times,
let's grab that same filter function
from the Boston worksheet,
it just resides in the upper left hand corner of this table.
So, when you do the filter function,
you type it in A2, all of these other cells are written
from what you put in A2.
So if you want to grab it, you have to go back to cell A2,
highlight that, copy it,
we'll come over to Pittsburgh,
paste it, something weird happened with the fonts
but we'll get that later, Boston to Pittsburgh,
and there we go.
Let's just fix that weird font.
And repeat that process
and you separated out all of the sheets.
So, if you only have a few values
in the city column in this example, then you're done.
But let's look next at an example
where you have lots of different cities.
So, we're saying there are too many here
for you to go through, create a worksheet for each one.
So one, that's going to take a long time,
and two, that's going to be an error-prone process
where you may skip some.
So, let's look at our second solution,
we'll go up to the add-ons
and we'll go to a plugin called power tools,
and we'll start that up.
Alright, while that loads up
there is an affiliate link down in the description.
If after watching this,
you think power tools is the right solution for you,
if you use that link to check it out,
you'll get a discount
and you'll be helping to support my channel
so I would appreciate that.
And after you install it,
you'll get a menu like this that comes up.
There's lots of different functionality here
but what we're going to show you this time
is the split and the split sheet option. Right?
So, we'll left click on split sheet
to split sheet by values and key columns is what it says
and it will bring up a menu.
And it's asking you what column to use to split it by,
so we don't wanna do ID, let's put a check mark in city,
we'll uncheck ID,
we'll leave it specifying that the table has headers
so it doesn't try to split out that value,
and we're going to put the values
in this current spreadsheet
but we could make a new spreadsheet if we wanted to.
All right, we have this line down how we want,
let's left click on split.
It runs the process in the background
and then when it's done,
it's creating a new sheet for every value in the city column
and it has created 17 new sheets for us.
Close this down, if you had 100 it would have created 100.
Left click through some of these,
that's all Washington D.C, that's all Savannah.
All right, and next up we'll show you
how to do some functions
based on text color and background fill color
using power tools.
See in the next video. Thanks!