The dashboard calculates project spending over specified time range for several projects. The user completes a table of Project start date and Month as well as Name and estimated cost. The dashboard shows a 5 year chart with breakout by month, quarter and year. Below the chart is a slicer which allows the user to select a project - or multiple projects - to isolate on the chart.
The dashboard works great and the client loves the result.
However, if the user leaves blanks in the project list, a blank entry will show up in the slicer. I have worked with settings for the slicer, the pivot table driving he slicer, and all the calculation tables to eliminate the blank, but no success. I spent much time reading articles and answers on various forums on the internet, but no luck with any of the suggestions there.
I have included two screen shots; the first shows the dashboard as it would be with all projects, and the second shows the same sheet with a blank entered in the project list.
Please note the blank in the slicer. I would prefer there be no blank and no selection in the slicer if there is a blank (or multiple blanks) in the project list.
Any help is welcomed.
Thanks.
Hi David,
The only way to remove blanks from Slicers is to ensure they don't show up in the PivotTable, and you can do this by filtering them out. Then you can set the Slicer to 'hide items with no data' in the settings.
However, from the look of your PivotTable, I see you can't filter the blanks out, so I would instead replace them with 'TBA' for the Project field. You can do this by selecting any of the blank cells in the PivotTable and typing TBA, then press ENTER. This will populate all blank Projects and will flow through to the Slicer.
Mynda
Thank you! This make sense and can make it work. I very much appreciate the help.
David
