Welcome back to our course on Excel 2016 Advanced.
In this section we’re going to primarily focus on filtering Pivot Tables and specifically
we’re also going to look at the use of Slicers which actually present some particularly straightforward
ways of filtering Pivot Tables.
Before we do that I’ve got a few things to point out that I haven’t pointed out.
So far now that you should have a good handle on the basics of Pivot Tables a few more things
to fill in, one of which is that if I want to refresh a Pivot Table you’ve already
seen the Refresh option there on the Analyze tab in the Pivot Table Tools.
To the right of that there’s a button Change Data Source.
And if I click on Change Data Source that takes me to the source of the Pivot Table
and allows me to make any updates or changes there.
So that can be useful.
And the other thing about a Pivot Table, if I make sure that I’ve got the Analyze tab
selected, over on the left here you’ve got an Options dropdown.
And if I click on Options there are some very useful options within Pivot Table Options.
And in particular I’d like to look at Data because one of the options on the Data tab
is Refresh data when opening the file.
It can very often be useful to have that set, particularly if your Pivot Table depends on
data which is being refreshed from outside itself.
And particularly if it’s say using a data model that’s subject to updates from data
from outside the specific workbook.
But it is well worth going through the tabs in the Pivot Table Options.
And also on the Analyze tab over on the right you’ve got a Show Group and one of the options
there lets you switch on and off the Field List.
So that shows or hides the panel that includes the Pivot Table fields and the drop zones.
There is also an option here that switches on or off the plus and minus buttons that
allow you to expand or collapse the items within the Pivot Table.
So if I switch that off, look at the left most column A in the Pivot Table, the plus/minus
buttons have gone.
And another one that displays the field headers for rows and columns.
So particularly if you’re in sort of presentation mode and you want to simplify what’s shown,
you don’t need access to these facilities, you’re not actually going to make changes
to the Pivot Table say when you’re doing a presentation those can be useful as well.
Now something else which I haven’t really used so far but which is actually very useful,
don’t forget the right click option.
So for instance, I’ve currently got cell B6 selected.
I’ve got the month of Feb selected.
If I right click on that that directly gives me access to Group and Ungroup.
So ungrouping the month there takes the date right back to the original dates from the
source data which is day by day dates.
Let me right click in there again and go to Group.
What I really want to do is to group the dates in months.
Select month, click on OK and I’m back to having months again.
And as far as the data itself is concerned we’ve already seen the effect of hovering
over and being able to see in this case sum of values, the row and the column as well.
One of the options here if you right click and click on Show Details, it takes you through
to all of the source rows in the Source Table that relate to that specific combination of
field values.
So Detroit breads in this case.
It takes you straight there.
And you notice there that Excel creates a separate sheet with that data on it.
Let me go back to the Pivot Table again.
Another way of accessing the details is to just double click on the cell and that does
the same job.
And one other thing to point out, although I’m pretty sure you will have worked this
out for yourself, if I go to the Pivot Tables Design tab I can choose my Pivot Table Style.
So if I wanted something a little bit more intense color-wise, what about something like
that?
Styles work pretty much in the way that they do in tables in Excel.
You have a few less options with Pivot Tables but basically it works the same otherwise.
So let’s turn our attention now to filters.
I’ve swapped, I’ve pivoted Department and Branch.
On each row in the Pivot Table I have potentially two filters.
I have a date filter and I have a branch filter.
So for instance, if I click on the branch dropdown here I’ll get appropriate filters.
For instance, I could decide just to show say the Houston and the Atlanta figures.
Note the effect on the totals in each of those departments of course.
And of course I can easily clear a filter any time, Clear filter from branch, and I’m
back to where I was.
Similarly for dates.
Not only can I choose which of the months I show but I can click on Date Filters and
I have the normal date filters.
Things like next week, last month, last year, and so on.
Look at the departments at the moment.
I’ve got breads, coffee, general, gift cert, grab and go, and so on.
If I deselect all of the months at the moment and say just select Jan and Feb, click on
OK, watch what happens.
What happens is I only see data for two departments.
And of course the reason that there’s only data for two departments is that we only recorded
sales in those two departments in January and February.
Now this returns to a point that I mentioned earlier on which is that sometimes when you’re
applying filters it can be deceptive.
So somebody looking at this Pivot Table wouldn’t necessarily know that you had filtered on
Jan and Feb. they may thing that you have filtered on breads and coffee.
So you do need to be very careful that you don’t make assumptions about what filters
are applied purely on the basis of what you can see in the column headers and row headers
and so on, because I could of course filter on department and that would affect what months
are shown.
So the whole thing could have been done the other way around.
Now of course the little filter symbols are a clue as to what’s actually being filtered
but you just need to be careful that people don’t jump to the wrong conclusion.
Now another very useful feature when you’re filtering is this.
What I’m going to do is to remove the Date Filter here for the moment.
So what I’m going to do is remove the Date Filter here.
I’m going to right click on Feb and go into Group.
And I’m going to group by months and quarters.
Click on OK.
But what I’m going to do is go down to the drop zones and I’m going to move quarters
up into the filters area.
And now what you can see is that I have my Pivot Table with quarters as effectively a
separate entity up on the left here.
So if I just wanted to show quarter 2 for this year click there, click on OK and I’ll
only see the quarter 2 filter.
So that covers the moths April, May and June.
Note that with this particular type of filter control I have the option to select multiple
items.
So I might say just say well I want to see quarter 1 and quarter 2, click on OK and now
I can see January, February, March right through to June.
And just one other thing and then we’ll move on to slicers.
If you have a field that has many different values you may find it hard to find the particular
value you want to filter on.
So in the case of say Department okay I’ve only got about 15 departments or so but supposing
I had a hundred.
Finding the one I wanted may take a little while.
There is a Search facility here.
So if I wanted to look say for tea just type Tea, wait and now you can see the categories
that have got tea in them, hot teas and tea.
So that can be useful as well.
So let’s turn our attention now to Slicers.
Slicers are a very useful way of filtering.
They’re very good in presentations and they’re also very good when you want to keep track
of exactly what is filtered, as I pointed a couple of times already.
Sometimes it’s not particularly easy to see which filters are applied.
One of the problems with Slicers though is they do take up some space on the screen if
you want to make good use of them.
I tend to have some space around the sides of a Pivot Table when I’m using Slicers.
As you can see here I’m still zoomed in quite a long way here.
I wouldn’t normally be this much zoomed in but it makes it easier for you to see what
I’m doing.
So what I’m going to do is I’m going to click somewhere inside the Pivot Table now
and then on the Analyze tab one of the options in the Filter group is Insert Slicer.
Now that brings up the Insert Slicers dialog and I can decide which slicers I want.
I’m going to actually have two slicers at the moment.
I’m going to have Branch and Department.
Click on OK.
And I now have two slicers.
I have a branch slicer that I’m going to put over there and a department slicer that
I’m going to move up there.
Now those slicers basically work like filters.
And sometimes you’ll get in a situation like I have here where there sort of isn’t
really enough room to put them without obscuring part of the Pivot Table.
But the sort of thing you can do is for instance here.
I can use the sizing handle to make that one a little bit narrower.
And in fact with the Department Slicer, note if I click on a Slicer I have a Slicer Tools
Options tab up here.
And one of the controls on there in the Buttons group is a Columns control.
I can actually say I want my Slicer to have say five columns.
Well let’s go right up to seven columns, then I’m going to change the shape.
Note that I can change the shape using the controls on that Ribbon tab as well or instead
of.
Now with a little bit more adjustment, perhaps I just put one more empty row, maybe two more
empty rows above the Pivot Table.
Now we can see everything okay.
Now let’s do some slicing, that is some filtering.
If I just want to slice, let’s suppose that I only want to show the figures for Atlanta.
I click on Atlanta.
I filtered on Atlanta.
If I want to apply the coffee filter then in the Department Slicer I can click on Coffee
and I’ll only see coffee.
Now of course it’s very often the case that you’re going to want multiple selections.
If you’re using a keyboard and mouse multiple selections are pretty easy.
If I want to see Atlanta and Houston hold the Control key down and click on Houston
and now I’ve got the Atlanta and Houston figures.
Similarly the same thing applies if I’m working in Department.
So if I want to see coffee and tea I can now see both of those.
If you’re using a touch device it’s a bit more difficult to do that sort of thing.
And so in Excel 2016 Microsoft introduced this new control here, this Multi-Select control.
So if I click on the Multi-Select control you can see there that coffee and tea are
selected.
Let me tap with my finger now on Juices and you’ll see that juices are included as well.
So that’s the basic use of Slicers.
Note that with a Slicer selected on the Slicer Tools Options tab you have a Slicer Styles
gallery.
So if you’re trying to say match something in a presentation you have a number of slicer
styles there to choose from.
To remove a Slicer all you need to do is select is and hit the Delete key.
Note in this case although the slicer is removed the filers that were specified on it are still
inforce.
But of course I can click on the Department filter here and say Select All again.
And one other point, with the Pivot Table Tools selected, go back to the Analyze tab
again.
To the right of Insert Slicer there is Insert Timeline.
And Insert Timeline is a special sort of Slicer.
It relates to date.
If I select Date, click on OK I get a sort of date slicer.
And as you can see there let me click on say August.
I’ve now sliced according to the month of August.
There’s no data there.
Let’s try July, June and scroll the timescale back there.
What about April, May and June?
So it can use what is in effect a visual date slicer to filter on date.
Note that with all the Slicers you have a clear filter option now as well.
So that’s it on Filters and Slicers.
Please join me in
the next section.