Repurpose slicers with a chart to craft an attractive and flexible dashboard

by Kara Hiltz

Application: Microsoft Excel 2016

Operating System: Microsoft Windows

Download: http://download.elijournals.com/office/201609/slicers.zip

You may think of slicers as a feature used for PivotTables and PivotCharts to filter data, and you’d be right. But slicers are so much more! You can rely on them to do the work of dependent dropdown lists—even if you aren’t working with PivotTables or PivotCharts.

To use slicers as dependent lists for filtering, we’ll:

Create a data table with a column that depends on a second column.

Insert two slicers so that they work the way dependent dropdown lists would work to update data on the fly.

Attach a chart to the two slicers to create a dashboard—no PivotTable or PivotChart needed!

You might have used dropdown lists that are dependent on another dropdown list, which you can achieve through data validation lists. This technique is great, but Excel 2016’s new slicers present an easier solution to dependent dropdown lists, as shown in Figure A.

We’ll create slicers that filter our Human Resources data by department and job title. When you select a department in the parent slicer, the second slicer will update to show only the job titles available in that department.Attaching these slicers to a chart is a great way to get some of the advantages of a PivotChart—without the intimidation of dealing with PivotTables!

Put your data in a table

In our example, we’lllook at base salary and bonus percentage information based on a company department and job title, as shown in Figure B. Before we insert our slicers, we need to convert our data range into an Excel table, which will format the data and add filtering capabilities.

Download: If you want to follow along with our example, just download and extract the file SalaryInfo.xls from the URL given at the beginning of this article.

B:

Our slicers will automatically show only the job titles available within the chosen department in the parent slicer.

To convert a range into a table:

Select any cell within your data range.

Switch to the Insert tab and click the Table button in the Tables area.