I was doing a webinar and it sounded like a simple question; how do you delete a slicer in Excel? But, when you delete a slicer, why does Excel seem to remember the slicer settings?
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Hey, if you're a regular viewer of the podcast, it's Friday and you're saying: wait a second, Friday is for Dueling Excel!
I want to send “Get Well” wishes to Mike Girvin, who is on injured reserve this week.
The Dueling podcast will come back, but for right now, you're stuck with just me.
It is my episode 1915.
How to Delete a Slicer?
All right, yesterday I was doing a seminar for the customers of the company excel4apps.
It was a great seminar, we had 700 people watching me do Pivot Tables and right at the end, a question came and it said: how do you delete a slicer?
I read that question, I said well, that should be easy, it should be easy to delete a slicer.
But it turns out that it's not easy to delete slicer.
So actually, I do a little bit of research on this.
So here's a slicer, where we choose three Customers and see, it shows up in the Pivot Table, right.
And you say, all right, well, if I'm no longer going to use this slicer, you would think you would just click here on the white space and press Delete, and that all of the Customers would come back.
But they don't.
That filter persists even after you delete the slicer.
What's up with that?
And it turns out, I talked to some folks at Microsoft, and in their view, the slicer is nothing special, it's just a great big shortcut way to avoid having to open this drop-down.
You know, choose (Select All) a couple of times, and then choose the Customers that you want.
So to them, you know, the slicer… when we delete the slicer, we're just saying: hey, we don't want to use that part of the user interface anymore.
But we're going to keep the Customers that you selected, when you were using it.
All right, which was kind of interesting to me.
And the behavior actually changes a lot, whether Customer is in the table or not.
So let me take Customer out and replace it with Product.
All right, so we have 6.7 million dollars, I choose a few Customers, and we're down to 739 thousand dollars.
And then I click here to delete the slicer and hey, wait, the rules changed.
We're back to 6.7 million dollars.
Oh, but that's… here's the crazy thing.
They've actually remembered what is in the slicer and we have a little clue over here, at the Pivot Table Fields list.
The Customer field, it has that grey funnel that says: hey, there is still something that we're caching.
We've cached the former state of that and as soon as I take, for example Product out and put Customer back in… huh, the three Customers come back, isn't that bizarre?
So I had 6.7 million, I added Customer in, and I'm down to 739 thousand.
Really, really crazy.
It doesn't seem logical to me, it doesn't seem logical to me.
Let's put the slicer back in and we'll choose a few Customers.
I thought, okay, well, what if we come out here to slicer tools options and look for a button that says “Delete”.
But there isn't one.
I could find this button, says “Report Connections” and in the Report Connections I could say, hey, I want this Pivot Table to no longer be associated with that slicer, click OK, and we go back to 6.7 million.
But let me add Customer in and it still remembers those five Customers.
It's still cached, even though this slicer is no longer connected to the Pivot Table.
Now, it truly is no longer connected to the Pivot Table, because if I would try and clear the slicer or choose other Customers from the slicer, it's not changing the Pivot Table.
So I have successfully decoupled it from the Pivot Table, let's say, but it doesn't mean, that I've gotten all the Customers back.
So we're back to the original question: how do you get rid of a slicer?
Let me bring the slicer back, we'll choose a few Customers.
Here's, what I think, the steps should be to delete a slicer.
First thing, make sure that your Pivot Table does not have, whatever field in the slicer is.
So this is the Customer field, make sure the Customer is not in the list.
Then click on the slicer and press Delete, we go back to the 6.7 million, and then, this is the really important part, come to the ANALYZE tab and click Refresh.
When you click Refresh, now, even later, when we add Customer in, we're going to get all of the Customers and not the five Customers that were chosen in the slicer.
On the one hand, it seems confusing.
On the other hand, I kind of understand their point.
And if you've been using Pivot Tables since Excel 2003, you might remember that trick that we used to have back in 2003.
That you couldn't choose more than one Customer from the list up here, they didn't have “Selected Multiple Items”.
But back in 2003 we would use this trick where we would move it down to the ROW field and then, in the ROW field, we could choose a couple of Customers.
Click OK and then move the Customer from the ROW field back to the report FILTER field, or the PAGE field, was called back then.
And you would actually get the multiple items.
So there was a sneaky way to pull that off in Excel 2003.
And now, this is kind of just the same thing, that we’re remembering the filters, as it moves out of the Pivot Table and back into the Pivot Table.
Well, it kind of makes sense.
Anyway, to delete a slicer, make sure the Customer is not in the Pivot Table, click on a slicer, delete and then refresh your data and all memories of whatever Customer was chosen, will go away.
Oh, hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
Hey, if you're a regular viewer of the podcast, it's Friday and you're saying: wait a second, Friday is for Dueling Excel!
I want to send “Get Well” wishes to Mike Girvin, who is on injured reserve this week.
The Dueling podcast will come back, but for right now, you're stuck with just me.
It is my episode 1915.
How to Delete a Slicer?
All right, yesterday I was doing a seminar for the customers of the company excel4apps.
It was a great seminar, we had 700 people watching me do Pivot Tables and right at the end, a question came and it said: how do you delete a slicer?
I read that question, I said well, that should be easy, it should be easy to delete a slicer.
But it turns out that it's not easy to delete slicer.
So actually, I do a little bit of research on this.
So here's a slicer, where we choose three Customers and see, it shows up in the Pivot Table, right.
And you say, all right, well, if I'm no longer going to use this slicer, you would think you would just click here on the white space and press Delete, and that all of the Customers would come back.
But they don't.
That filter persists even after you delete the slicer.
What's up with that?
And it turns out, I talked to some folks at Microsoft, and in their view, the slicer is nothing special, it's just a great big shortcut way to avoid having to open this drop-down.
You know, choose (Select All) a couple of times, and then choose the Customers that you want.
So to them, you know, the slicer… when we delete the slicer, we're just saying: hey, we don't want to use that part of the user interface anymore.
But we're going to keep the Customers that you selected, when you were using it.
All right, which was kind of interesting to me.
And the behavior actually changes a lot, whether Customer is in the table or not.
So let me take Customer out and replace it with Product.
All right, so we have 6.7 million dollars, I choose a few Customers, and we're down to 739 thousand dollars.
And then I click here to delete the slicer and hey, wait, the rules changed.
We're back to 6.7 million dollars.
Oh, but that's… here's the crazy thing.
They've actually remembered what is in the slicer and we have a little clue over here, at the Pivot Table Fields list.
The Customer field, it has that grey funnel that says: hey, there is still something that we're caching.
We've cached the former state of that and as soon as I take, for example Product out and put Customer back in… huh, the three Customers come back, isn't that bizarre?
So I had 6.7 million, I added Customer in, and I'm down to 739 thousand.
Really, really crazy.
It doesn't seem logical to me, it doesn't seem logical to me.
Let's put the slicer back in and we'll choose a few Customers.
I thought, okay, well, what if we come out here to slicer tools options and look for a button that says “Delete”.
But there isn't one.
I could find this button, says “Report Connections” and in the Report Connections I could say, hey, I want this Pivot Table to no longer be associated with that slicer, click OK, and we go back to 6.7 million.
But let me add Customer in and it still remembers those five Customers.
It's still cached, even though this slicer is no longer connected to the Pivot Table.
Now, it truly is no longer connected to the Pivot Table, because if I would try and clear the slicer or choose other Customers from the slicer, it's not changing the Pivot Table.
So I have successfully decoupled it from the Pivot Table, let's say, but it doesn't mean, that I've gotten all the Customers back.
So we're back to the original question: how do you get rid of a slicer?
Let me bring the slicer back, we'll choose a few Customers.
Here's, what I think, the steps should be to delete a slicer.
First thing, make sure that your Pivot Table does not have, whatever field in the slicer is.
So this is the Customer field, make sure the Customer is not in the list.
Then click on the slicer and press Delete, we go back to the 6.7 million, and then, this is the really important part, come to the ANALYZE tab and click Refresh.
When you click Refresh, now, even later, when we add Customer in, we're going to get all of the Customers and not the five Customers that were chosen in the slicer.
On the one hand, it seems confusing.
On the other hand, I kind of understand their point.
And if you've been using Pivot Tables since Excel 2003, you might remember that trick that we used to have back in 2003.
That you couldn't choose more than one Customer from the list up here, they didn't have “Selected Multiple Items”.
But back in 2003 we would use this trick where we would move it down to the ROW field and then, in the ROW field, we could choose a couple of Customers.
Click OK and then move the Customer from the ROW field back to the report FILTER field, or the PAGE field, was called back then.
And you would actually get the multiple items.
So there was a sneaky way to pull that off in Excel 2003.
And now, this is kind of just the same thing, that we’re remembering the filters, as it moves out of the Pivot Table and back into the Pivot Table.
Well, it kind of makes sense.
Anyway, to delete a slicer, make sure the Customer is not in the Pivot Table, click on a slicer, delete and then refresh your data and all memories of whatever Customer was chosen, will go away.
Oh, hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
