If you're using Google Sheets, let's say you have a table of data like this
and you want to be able to create a Pivot Table from it and filter it
quickly and easily.
I'm going to go through the steps to create something like this and add slicers
to it and these slicers will cut through the data.
They'll do it a different way than the built-in filters
will.
So we'll go through what that means and how to do it.
This worksheet that we're looking at, we're going to keep the
before worksheet like this with just the source data on it.
We'll keep the after worksheet looking like when we're done.
We're going to work on this one in the middle.
So what we're going to start out with is just this table
of data, it's transactional data, each row is a unique record, it has nice headers
on it, and there's no spaces in the data.
So this is ready to go to be made into a pivot table.
I want to take this data and I want to look at it a
few different ways.
I'm actually going to make two different pivot tables and
you'll see why in a minute.
I want to show the sales by item, I want to summarize
them.
That's why I want to do a pivot table and then I'm going to create another
pivot table to show it by item and then by date.
So we'll drop these two pivot tables in here relatively quickly.
We'll do Data, Pivot table and our source is going to be this table including the
headers.
We'll click OK.
We're going to put the Pivot Table on the existing
sheet and we'll just drop it over here in G2.
The rows will be the item description like we had talked about and the
values will be, let's say we want to look at the quantity sold.
I want the quantity and we want that to be summed so we'll leave this drop-down box
at the value of SUM and we have a nice clean pivot table here that's just summing
the quantity of sales by the item description.
We're going to drop another one into here.
We'll go back to the data pivot table.
We're going to also do that on the existing sheet.
We're going to use this table as a source.
We're going to put this in.
Leave a little bit of room because pivot tables expand and
contract based on what you do with them.
We'll come down to let's say G13.
That should be enough padding.
Create that.
First, we'll add a row with the date.
We're going to go in and right-click and group these
dates.
We don't have to look at every day.
If you're watching this as part of a course, there's another module
that covers this, but for now let's just suffice to say that this is just
different sections of a date by which you can group things.
Let's just do it by the month.
We'll add our values in right now.
We'll still do quantity and we also wanted to add the items to this second pivot
table.
So, we'll drop in another row with the header of Item description.
Here we go.
So we have two different pivot tables, they're both showing 336 items.
They're showing it in a different way.
We built two because we're going to show you
that this way of filtering called slicers will act on both of these pivot tables
as long as both pivot tables are in the same sheet and they're working on the
same source data.
These slicers will also work for charts, this is just about pivot
tables right now, but if you do it with charts and pivot tables, as long as they're
on the same sheet, it's going to be the same thought process.
The first way to filter these, if you weren't using
a slicer, would be to left-click in the pivot table.
You get the pivot table editor down to the bottom.
You can add a filter let's say we want to filter by
item description.
When you're using a filter, if you left-click on the status,
right now it says showing all items.
You can either filter by condition or by values.
That's going to be the same in a slicer.
We're going to take the garden hose out, but now we're going to talk about
some differences.
One difference is, it only applied to this pivot table because
filters are specific to one pivot table.
The other difference is, you don't see this being filtered unless you
left-click on it and look at the editor.
So if you're showing someone the pivot table, and you filtered it, maybe you did
that on purpose and you don't need to show that, but if you do it in a slicer,
it will be obvious that you did it.
So it depends on whether or not you want to prepare it and have someone be
able to change it, or if you prepare it and you just want someone to view it
after it's filtered.
Let's take this filter out.
It was only applied to this top at the table if you remember.
We're going to I hit an X and you'll see, when we remove the filter, that
the garden hose is going to come back into the table.
So let's left-click on this X, you have your original pivot
table back now.
Instead of doing filters, we're going to do the slicers.
You're going to see how it operates differently.
We'll go to data slicer and now it's asking for the data range.
Pick the table as the data range.
Don't pick the pivot table.
We're going to be actually slicing the source data.
I'll click OK.
There's a slicer.
We're going to move it over to the right and let's take
a look at a few of the options right now.
The first thing is, and we already selected this, but it's filled in here, is
the range.
I picked the table, I included the headers so the headers will come
up when we use the slicer.
You'll see that in a second and we'll skip the column
for now.
You want to make sure that this is checked to apply to pivot tables.
If you have that unchecked, it's not going to apply to pivot tables, right?
So pay attention to that.
Then you can also customize it.
You can give it a title.
Maybe "Filter by item here" and the color and the font and all this stuff.
So let's leave that the way it is for now.
Let's say we want to filter by item description.
We're going to choose a column.
It can only do one column.
You have to add a separate slicer to do another column.
We'll do Item description and the first thing that
you're going to notice, if someone comes into this pivot table, the slicer is
clear, right?
It's not buried inside the settings of one of these pivot tables.
It's here for everyone to see.
If I go to it, you have the same options of filter
by condition or values.
If you filter by condition, you could do something like
select only the dates after July 1st, 2019 or you could do something more
advanced like selecting only things that have that
word "hose" in it.
But our list is kept pretty simple here on purpose so what
we're going to do, we're gonna do the same thing again.
We're going to take out garden hose.
Look at this first.
There's four items.
This has four and four.
Click okay and everything updates.
Even the source table updated.
So, that data is still there, but it's hidden right now if
we also want to filter by date.
You just add in another slicer.
So let's say Data, Slicer.
Arrrrgh!
That gave me an error.
Let's go ahead and select the range and then do Data,
Slicer so it doesn't get mad at us.
Alright, we'll move it over to the right.
All the slicers on one sheet have to work on the same range and they're sheet
specific.
So if there's a pivot table on this data on another sheet, it's not
going to be filtering it.
Another thing to keep in mind with slicers is
the changes that we've been making are only going to work in our user profile
with our Sheet.
So if you're sharing this spreadsheet, and you want these settings
to apply when someone else comes in here, you'll have to edit the slicer and pick
"Set current filters as default."
When someone else uses the Sheet, then they'll see the same filters that you have only if
you do that.
We have to select a different column, let's do date and we're
going to drop this down and we're going to filter by condition.
We're going to say we only want it if it's after let's
say August 1st, 2019.
Now before I click OK, this table you can see has July
values in it and August.
This is totaling 280.
We'll click OK.
It updated both of them.
This only has August and this has much less than they
had before and now it has these both of these filters applied.
If you want to go through these steps yourself, there's going
to be this file linked with notes to go along with it.
It'll have that before worksheet and the after worksheet.
Just head on over to the description in the video, follow the link there, you'll
be able to check it out.
Thanks for watching!