Interactive Report With Slicers

Here is another nice report that can be filtered dynamically. This kind of reports are pretty impressive for people who doesn’t know how easy they can be made. So lets impress coworkers with an interactive report with slicers today.

As explained in this post slicers are fancy pivot table filters (unfortunately they are available for Excel 2010 and 2013 only). So we need to build our report with pivot tables. Than we are going to filter multiple pivot tables with one slicer.

I’m going to continue over an example:

This time we are running a vehicle rental business on a touristic region. We operate on 4 sites and rent cars, motorbikes, ATV’s and bicycles at all 4 sites. So we are going to make a report that shows revenue by vehicle, total revenue and rental amounts by vehicle.

Our data includes following columns. There are one list for each site.

For our report, we need to combine all 4 list and make one list. Here is an important trick: we need to add a column to each list that contains site name before merging lists. This way we can filter data based on site names.

Now that we have our data arranged, we can proceed to pivot part. Make a pivot table from this merged list. Put month in row, vehicle type on filter and revenue in values sections. Make 3 more from this pivot table by copy/paste. Than filter these 4 pivot tables as car, motorbike, Atv and bicycle respectively. We are going to use them for our vehicle based charts.

Make another copy and don’t filter it. We are going to use this for our total revenue chart.

Lastly, make another copy and replace revenue with amount. We are going to use this for our amount based chart.

Now is chart making time:

Insert a line chart while first pivot table is selected. Hide field buttons from right-click menu as shown in the picture below.

Make line charts for remaining 3 vehicle pivot charts and make 2 column charts for total tables, one for each. Cut them all and paste them into another sheet. Here is how I arranged my report:

Now return to your pivot table sheet and select any one of your pivot tables and insert 2 slicers. One filtering site name, other filtering vehicle type. Cut them both and insert them into the empty space in your report.

Format them both as you wish. Then from right-click menu, enter “pivot table connections” options. Select table 1 to 5 for site name slicer (will filter top and middle charts) and table 6 for vehicle type slicer (will filter bottom chart).

Now it is all done. Make some final formatting, headers, footers, placements, etc. Than test your buttons. In case of any problem, return to previous steps. If all is working properly, you have a nice interactive dashboard with slicers.

If you like this interactive report with slicers tutorial, you can download our example workbook and practice on it.

For more charting tutorials you may visit charting category.