all right so slicers are finally here so let's take a look at them and see what we can do with
this to use a slicer let's start by building a pivot table from this data set so if I quickly
just take a look here if I do control down or command down see we have 1400 lines so we're
gonna do a quick pivot table out of this going under data I'm gonna do a pivot table I'm gonna
put it on a new worksheet that's fine to make sure this pivot table updates going forward I will just
unselect this and as a data range I'm gonna select the entire column going down I'm gonna hit OK and
create again so to build the pivot table I'm gonna go on their values here I'm gonna add sales as
values and I'll go on the rows and do sales reps as rows and to get rid of this blanks because we
selected the whole column I'm gonna scroll down here and under filters - sales rep and as a filter
I will filter by condition and instead of none I will select is not empty ok that should take care
of that so in pivot tables we have these filters we can use this to add more filters so for example
if I wanted to filter this by region just add another basically filter by region and now we
have a region so I can go here and just clear this and do Midwestern heat okay that should filter to
just that then you can just go here and change this but this is not very visually pleasing for
somebody who doesn't understand how to use this things so to make something more user-friendly
I'm gonna take this back and remove this we're gonna add a slicer to this pivot so what we do
we just click on this pivot table on their data and click slicer and that's gonna show the list
of columns from our data set again I'm gonna go ahead and choose region so we want to make sure
this apply to pivot tables is checked then I can take this and move this around and now we have
to so if I take this and do Midwestern you'll see that should filter the PivotTable so it's
mostly basically just a nicer looking filter for this pivot table well it's really great
about this is that I can actually add more pivot tables using the same data on the same worksheet
here this pivot table 13 worksheet and I'm just gonna call it report and that same slicer should
work for those as well so let me go ahead and add that second pivot table here I'm gonna go back to
the subtotal tab again select all of the columns data and pivot table and now I'm gonna do existing
worksheet click in this go back to my report tab and here basically click where I want that second
pivot table to show up I'll just click over here that's fine heed okay and hit create so here we'll
do number of sales by brand so I'm gonna go brand here and values we'll do sales since I don't want
the sum I'm gonna do count so let me just scroll right so you can see what's going on here so
we have this and here I want to get rid of these planks again so I'm gonna add a filter for a brand
column and filter by condition and is not empty to get rid of that blank on top good scroll this way
close this so now we have two pivot tables here and if I go here and filter to a particular region
we should have both of them filtered to that region so if I do a midwestern that should
again just filter both of them and it works this will work on pivot tables this will also work on
charts but you have to make sure that all the pivot tables and all the charts are made using
the same range of data if you have different data sets it will not work you also want to make sure
that all those pivot tables and charts appear on the same worksheet otherwise again it's not
gonna work so let me go ahead and add a little chart here so to do a chart out of this I'm
just gonna do a pie chart by gender I'll select all of this go under insert and chart there it
is so instead of column chart I'm gonna switch this to a pie chart we're going to change this
to the label being gender so I'll click in here and select that column in a background this is
that a column gender hit okay so we want to make sure we aggregate values but the values
we aggregate is not gonna be date it's gonna be sales so I'll just edit this and change this to
this age column and hit OK and then I'm gonna check this aggregate because I want the total
of that column broken down by gender so there it is we have ourselves a pie chart you can obviously
make it pretty and things like that I'm not gonna worry about it right now so I'll go ahead and cut
this chart because this needs to be here on this tab so I'll do command X or control X if you're
on Windows and then I'm gonna go here and do command V or ctrl V to bring that chart over
and paste it here we'll just resize this fit this in here somehow there it is now it's on the same
worksheet as this other stuff so let's go ahead and select all there it is our numbers change we
go ahead and select midwestern everything changes so our charts and our pivot tables using the same
data are all updating based on that slicer you can also add multiple slicers if you want to so
if I click on one of these pivot tables go under data and click slicer it will get us and slicer
will choose another column we want to filter this by so let's say I want this to be state or gender
it doesn't really matter I'll do gender that's fine and now we have two slicers here side by
side and we can just use both of them to filter stuff so if I go here and do female it will just
filter these and now we have hundred percent female here because we filtered the rest and
these two filter and then you can also filter like this one midwestern so I'm gonna take this
clear select all it okay get back to this so what if we want to add a date slicer so it's not gonna
be that different we're gonna click here and data slicer yet another one drag this someplace in here
and choose a column date and now we can filter this bike dates and you have all this individual
dates which I don't think you're going to use so we can go filter by condition and go here and
scroll down and do things like take this before date is after if you have a specific range of
dates you're trying to filter this too you can also scroll down and find is between option and
then just give like the beginning date and the ending dates so I can do 2016 1 and I can do 12
31 2016 and then we're gonna scroll down it okay and that should just filter to that but let's be
honest who wants to do this nobody wants to go here and try to enter this date somehow and hit
okay so most of the time people just want to just have a drop-down that gives us years or
months but overall this is how slices work just make sure all your charts and your pivot tables
are located on the same tab as your slicers and they all use the same data set and it's
just going to work now let's take this to the next logical step and try to build a dashboard
which will have nicer slicers that will give us years months this sort of stuff because like I
said nobody wants to do this so let's just get rid of all of this and start from the beginning