All language subtitles for 1. Inserting and Formatting Slicers

In this section, we're going to be taking a look at how we can add interaction to our pivot tables

and pivot charts.

I'm going to kick off this section by looking at slices.

Now, if you've never used slices before, they're basically filters, but they are unlike filters you

might have been used to using in the past because they are visual filters and they add a really nice

level of interaction into your spreadsheets that make it super easy for anybody that you're sharing

this spreadsheet with to interact and filter the data.

So let's take a look at how they work.

So we're going to go back to our gross sales by year bar chart, and we're just going to click on the

pivot charts.

If we go up to pivot chart, analyze notice in the filter group, we have two slice options here insert,

slice and insert timeline.

Now we're going to be looking at timelines in the next lesson.

At the moment, we're going to concentrate on inserting slices.

And if we take a look at the screen tip, it says use a slicer to filter data up.

Visually, slices make it faster and easier to filter tables, pivot tables, pivot charts and cube

functions.

So if we click Insert Slicer, what you're going to see in this inset slices dialog box is a list of

all of those column headings from your data source, and you can basically choose one or more to be

the slices or the filters for your data.

So if we take a look at our chart, we're currently displaying the gross sales by country.

Now it might be that I want an easy way to see these totals by the year.

So what I'm going to do here is I'm going to select year from this list and add a slicer for that.

And what you get is this little panel pop up, and it shows all of the years from your data source.

Now I only have two years worth of data, so I'm only getting 2018 and 2019.

But look, what happens if I click on 2018, my pivot chart and my pivot table both update and I'm now

just seeing those figures for 2018.

Let's take a look at 2019.

The same thing happens.

So this is essentially filtering my pivot data.

And you don't necessarily just have to have one slice, so you could have multiple slices attached to

your pivot chart.

So let's add another one.

I'm going to go back up to pivot, analyze, insert, slice, and we're going to add a slice to show

the products.

And there we go.

Now I have to now where you positioned these slices on your page is entirely up to you.

But I'm going to show you a method that I really like to use.

Now we're going to apply a little bit of formatting to these slices to make them more in keeping with

the kind of overall theme that we have going on here.

So let's select both of the slices and notice that we have a slice of contextual ribbon.

So the first thing I'm going to do is choose a different slicer style.

So let's take a look at this one.

I think that's pretty good.

That's pretty in keeping with my color scheme.

If I wanted to create my own slice a style, much like pivot table styles, I could completely customize

my own slicer with the colors and stylistic options that I want.

Now, another thing I might want to do here is change the number of columns for this slicer.

So instead of having all the products in one big long list running down, if I jump up to the slice

a ribbon in this button scrape notice, it says columns one.

So all of my items are just appearing in one column.

But if I increase this to two, you can see how that changes things.

So I'm going to put all of these products onto one row and then I'm going to widen out this slicer and

pull the bottom of it up.

So then what I could do is maybe have this slicer underneath my char or above my chart, which makes

things look a lot nicer.

I think I'm going to drag that along.

So it's the same width as the chart.

I could do something similar for the year.

Let's put this up to two columns.

I'm going to drag the bottom up and then I might want to place this actually on the charts.

Now, notice here.

This doesn't look particularly great because we have a heading.

We've got some icons here.

We've also got a border around this, Lisa.

So if I want to make this look super clean, I might want to modify the style of this slicer and maybe

remove the outside border and remove these headings.

So let's click on here.

And the first thing I'm going to do is right click and go into slicer settings.

It's also worth noting that you can get your slice of settings on the slicer ribbon in this first group

just here.

And this is where you can basically customize what information you're seeing on your slicer, so you

can see there the name is, yeah, I can choose if I want to display the header or not.

So if I did select this, it's basically going to remove this year heading.

So let's click on.

OK, that looks a little bit better.

I'm going to drag that up like, so now I'm going to do the same for products down here as well.

Let's go to the settings and let's remove that header again, just making everything look a lot cleaner.

And the final thing I really want to do here is I want to remove the border off of this slicer at the

top.

So for this, we actually need to modify the current style.

So if we click the dropdown, I can see here, this is the style that I'm using.

It's got that gray border around the outside.

So if we right click on this notice that the modify option is actually grayed out so we can't modify

a standard slicer style, we need to duplicate it instead and then modify it.

So I'm going to call this my initials and custom slicer, and then I'm going to go to whole slice and

I'm going to remove the outside border.

So let's click on format.

Let's go to the border tab, and I'm going to say that I don't want any border.

Click on OK.

OK.

And now I can apply my new custom style to my slicer.

And now you can see the effect that has.

It makes it look like this slicer is part of this chop.

So then I can move that into place anywhere on this chart, so I might want to put it just there so

we can really customize the slices and make them at one with our charts.

It might be that I also want to apply that same custom style to the slice that we have at the bottom.

So let's select it and let's choose our custom style.

And again, that just removes the border.

So now I can use these to filter my data so I can click on 2018.

And maybe I'm only interested in Lux.

If I want to select multiple items, I simply hold down control and I can just click to select three

different products.

Now, clearing these slices is a little bit more difficult when you've removed the heading, because

when you have the heading applied, you have a clear filter button, which makes it super simple just

to reset everything.

But because we've removed the heading, if we want to clear all the slices, we need to right click

and select clear filter from product, and that's going to put everything back to.