Creating interactive dashboards using Slicers

Excel is excellent for creating presentations which contain tables and charts. Users can be given options to amend variables such as months or sales regions in order to modify the dashboard contents.

This could all be done using drop down lists and conditional functions (such as SUMPRODUCT) to return the appropriate values. There is however a limitation - these simple tools do not work if the user wishes to select several or all of the variables being presented to them. A drop down list of regions created using the validation tools will not give you the option of selecting both the Northern and also the Eastern regions.

An alternative solution may be to use pivot tables and filters linked to 'Slicers'. This can overcome the problem of filtering for multiple options but other issues may arise. It may be possible to remove some limitations by using Power Pivots and Visual Basic, but I am assuming that my dashboard is to be used by other colleagues who may be prevented from using these tools.

[1] A table of data showing shifts worked by Agency staff. This is the source of the pivot table.		[2] A pivot table containing filters to select both the months and the specialties.
[3] Excel will place the filters above the pivot table.This probably isn't what you want to show to your users on the actual dashboard and you may want the original pivot and filters to be hidden on another worksheet. A slicer can be linked to a filter / pivot table and then be placed anywhere in your spreadsheet - such as on the worksheet which contains the actual dashboard.
[4] Click within your pivot table and then select PivotTable Tools > Options > Sort and Filter > Insert Slicer. Choose which field you wish to filter by (e.g. Month). The slicer appears beside your pivot table but can be cut and pasted elsewhere. Clicking a value in the Slicer changes the equivalent filter field value.

[5] Another helpful feature of slicers is that they can be connected to more than one pivot table (based on the same data source). Choosing a value on a slicer can change the filters on different pivot tables - thus enabling multiple items on your dashboard to be updated simultaneously. Click on a slicer and then choose Slicer Tools > Options > Slicer > Pivot table connections.

Other Notes

Power Pivots are more efficient and flexible than regular (old style) pivot tables but they do require an Excel Add-In to be loaded. If you are sharing the file with other users, they may not have Power Pivot available.

Regular pivot tables which are based on different data source tables cannot share slicers. It might be possible to get around this limitation by adding calculated fields to a table, but the pivot tables may not automatically update to reflect changes in the other source table. The need for a manual refresh can be avoided with VBA but this means that the worksheet will need to be saved as an XLSM file which may present security issues for other users. One solution may be to consolidate your source data into a single data table.

In this example two queries have been combined within a single table (possibly using a UNION query) and can be identified as source: 'a' or 'b'.

A slicer based on the period can be connected to two pivot tables because they are both based on a single data table.

This page is not included in the Excel functions guide

Click here for details about obtaining this file