User friendly solution for multiple slicers. | Dashboards & Charts | Excel Forum

Hi everyone

Firstly, what an amazing resource this place is! It's my first post here and I am hoping that someone can help 🙂 I apologise in advance for how long winded this is but I thought it best to fully describe my methodology in order for you to understand what it is that I am trying to achieve!

Sp I have built a spreadsheet that analyses staff utilisation over a period of time. I am specifically looking at percentage of time spent on chargeable vs admin vs leave, whilst also identifying where staff members are working too many or too little hours. I need to show the % split by employee and by teams. My data set is timesheet entries exported from our database.

Firstly I created an extra column in my data set being “Utilisation value”. This utilisation value takes the quantity of time entered divided by the number of hours available in the period. For example, if we are looking at utilisation over a standard 40 hour week and the employee has entered 25 hours of chargeable time, the chargeable utilisation value would therefore be 25 / 40 = 62.5%.

In order to properly analyse the data I needed to create a multiplier per employee that takes into account whether said person works FT vs PT hours, and also considers where people start or leave within set time period. Taking the same example as above that this employee’s contract is actually 32 hours per week. Therefore a multiplier of 1.25 (40 / 32) is applied and the chargeable utilisation therefore becomes ~ 78%.

Each employee reports into a Team Leader (TL), who in turn reports into a Head of Department (HD) with all heads of department reporting into the CEO. I have created three additional columns on my data set that identifies which TL employee reports into, which HD the TL reports into and where the HD reports into the CEO. By doing this the TL’s data will appear both within their own team and also within the HD’s team.

I have built a pivot table and chart with three different slicers:

• TL selection (list of names)
• HD selection (list of names)
• CEO selection (“Yes” or “No”)

The catch is, I need to always clear the filters on all slicers when selecting different teams. I have no problem with doing myself however it's gets a bit confusing for the end user who may or may not be an experienced Excel user.

In the ideal world, there would either just be one slicer in which you could select names for all three slicers (which would obviously take into account where one person sits within multiple teams). Failing that, is there a way to automate the clear filter function when you select different slicer names? I’d prefer to avoid VBA but have no issue if this is the only solution.

Massive thanks in advance for any suggestions!

Euan