How to fix a slicer error in PivotTables in Excel – have you come across this error before?
This week’s hint and tip is on how to fix a slicer error in PivotTables in Excel. We look at PivotTables in our Advanced Excel training course and do cover slicers too. This specfic hint and tip has come out from an error an attendee on a recent course found. So we decided to do a hint and tip on the fix in case anyone else has come across it. We are going to go through it now below.
Slicers in PivotTables
Slicers are another way in which you can filter your PivotTables in Excel. They are often seen as a more visual way of filtering, compared to the more traditional way via the drop down arrow method. You can have a slicer for every field option available for your PivotTable and you can customise them on the Slicer tab that appears when you click on it.
The screenshot below shows a PivotTable with a slicer next to it.
Slicer Error in PivotTables
It’s not uncommon for there to be errors in data in spreadsheets and these errors can end up pulled through to your PivotTable. One example is if a date has been type in incorrectly, then the incorrect year can end up in your slicer too. You can see in the screenshot below how the incorrect year is in the slicer on the right hand side.
Usually an error is fixed and then refreshing the PivotTable will fix it. However, this refreshing only fixes it in the PivotTable and not the slicer. The incorrect year remains but greyed out in the slicer. This is shown in the screenshot below.
This error occurs due to Excel keeping old values in its Pivot cache so that slicers won’t keep changing shape due to a data change. This sometimes can be useful but in some cases it might not. We will look at how to fix this below.
Fix for the Slicer Error
To fix this error of the incorrect year remaining greyed out in the slicer, firstly go to the PivotTable Analyze tab. From here click on the Options button on the ribbon and in the pop up window choose the Data tab. In this screen click on the drop down arrow for ‘retain items deleted from the data source’ and change that to ‘None’. Now click OK. This is shown in the screenshot below.
This will close down the window for you. Now click on the Refresh All option again on the PivotTable Analyze tab and notice how the greyed out year has now gone from the slicer. This can be seen in the screenshot below.
The video below goes through how you can fix this slicer error in your spreadsheets. This might not come up often, but could be a useful fix to know in case it ever does. Take a look below at the video to find out more and then try it out on your own computer!
We hope you have enjoyed this hint and tip on a Slicer Error in PivotTables in Excel. Why not take a look at our previous video hint and tip on some features found in Word on the Review Tab?