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!

A:

IME18201A

Instead of two dependent in-cell dropdown lists, we’ll use Excel’s slicers to filter our chart data.

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:

IME18201B

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:

  1. Select any cell within your data range.
  2. Switch to the Insert tab and click the Table button in the Tables area.
  3. Confirm the data range Excel predicts for your data table in the Create Table dialog box. In our example, we’ll use the range A1:D17.

  4. Select the My Table Has Headers check box, if needed.
  5. Click OK to convert your range into a table with formatting and filtering applied.

Your data range should now have banded row shading and filtering capabilities like the one shown in Figure C, indicating that it is an Excel table.

C:

IME18201C

Quickly format your table by using one of the preset styles on the Table Tools ribbon’s Design tab.

Add the two slicers

We’ll add slicers to our workbook and change a setting in the dependent slicer so it automatically hides data with “zero” values (i.e., data filtered out by your first slicer’s selection).

To insert slicers in your worksheet:

  1. Select any cell within your data table.
  2. Go to the Table Tools ribbon’s Design tab.
  3. In the Tools area, click the Insert Slicer button to view the Insert Slicers window.
  4. Select the check boxes for the columns for which you want to create slicers. We’ll select Department and Job Title, as shown in Figure D.
  5. Click OK to add the two slicers to your worksheet.

Once you insert the slicers, you can move them and r[...]

 
Join NowClose
Return to the ExcelSkillsSociety's homepage