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.

interactive_report_with_slicers

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.

Interactive Report With Silcers 1

 

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.

Interactive Report With Silcers 2

 

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.

Interactive Report With Silcers 3

 

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:

Interactive Report With Silcers 4

 

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).

Interactive Report With Silcers 5

 

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.

You may also like...

9 Responses

  1. mhp says:

    I was curious if you ever considered changing the layout of your site?
    Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so people could connect with it better.

    Youve got an awful lot of text for only having 1 or 2 images.
    Maybe you could space it out better?

    • melih says:

      Hi,
      Thanks a lot for your kind suggestion. I am aware of visitors preference of visual content over written content. That’s why, my more recent posts are a bit image heavy.

      There is also the issue of latest wordpress update messing up the pictures(especially ones added by copy/paste) in some posts. I am repairing posts as fast as I can.

      This post also has acouple of more images.

      Cheers 🙂

  2. Maddison says:

    Hello there, You have done a fantastic job. I’ll definitely digg it and personally suggest to my friends.
    I’m sure they will be benefited from this web site.

  3. No matter if some one searches for his vital thing,
    so he/she wishes to be available that in detail, thus that thing is maintained over here.

  4. Do you have any video of that? I’d care to find out more details.

  5. didigames says:

    I have been exploring for a little for any high quality
    articles or blog posts on this sort of area .
    Exploring in Yahoo I ultimately stumbled upon this web
    site. Reading this info So i am glad to show that I have a very
    excellent uncanny feeling I came upon just what I
    needed. I most definitely will make certain to do not disregard this web site and give it a look on a constant basis.

  6. Craft Projects for Grandkids along with GrandparentsCraft tips for grandparents along with grand kids are a fun way to destroy which age group obstacle. Fortunately there are a variety of internet sites that offer free patterns along with tasks for several varieties of homemade projects to suit your needs!The way to Win Freelance Writing ProjectsTips to acquire big article writing projectsFour Kids Creating Projects With regards to SportsKids can occasionally see composing as being a task linked to university.
    solomon hiking shoes

  1. April 9, 2017

    […] can check Interactive Report With Slicers to Excel Slicers it on […]

Leave a Reply

Your email address will not be published. Required fields are marked *