Make Dynamic Dashboards using Pivot Tables & Slicers [Video & Download]

Posted on December 8th, 2010 in Charts and Graphs , Pivot Tables & Charts - 46 comments

Do you know that Excel 2010 makes creation of dynamic dashboards very simple?

Yes, that is right. Using slicers feature, you can create dynamic excel dashboards from your data in very little time. Today we are going to learn a technique that will help you create a dashboard like below.

(Note: The technique works only Excel 2010 or above.)

Make Dynamic Dashboards using Pivot Tables & Slicers in Excel

[View the dashboard image in large size]

How to make a Dynamic Dashboard in Excel using Pivot Tables & Slicers

I made a short (12 min) video tutorial explaining the concept and construction of this dynamic dashboard. Watch it below.

[Watch the dynamic dashboard tutorial on YouTube]

If you are new to Pivot Tables,

Read this excellent pivot tables tutorial & watch the video on pivot tables.

Download Dynamic Dashboard Workbook:

Click here to download the Excel 2010 workbook. Remember, it works only in Excel 2010 or above.

Do you use Slicers? What is your experience like?

I have been using Excel 2010 for the last several months. I really like the new features – sparklines, slicers & pivot table enhancements. They give a lot of muscle for analyst in me.

What about you? How do you like the slicers feature in Excel 2010. How are you using them?

Get Dashboard Training from Chandoo.org

I have made an hour long video training explaining how to construct Excel Dashboards using a recent dashboard I made as an example. If you work on dashboards, this is a good program for you. Click here to learn more.

Excel Dashboard Training from Chandoo.org

More Resources on Dashboards, Dynamic Charts & Pivot Tables

Visit these pages to learn more.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

46 Responses to “Make Dynamic Dashboards using Pivot Tables & Slicers [Video & Download]”

  1. claudia says:

    WOW, is all I can say.

    I could not have imagined a dynamic dashboard without getting approved software budget and a team of people involved to create it. Given that I am a relative newbie to excel and actually got here by looking for pivit table help, I imagine that i would not be able to make anything myself. But armed with the demo excel sheet I will press buttons (and I will report back how that went;-)

    Claudia

  2. winston says:

    Good stuff Chandoo, thanks
    The slicer buttons take up quite a bit of room on the dashboard
    Is there a way to make the buttons smaller so we can have more room for charts, tables, and commentary?

    Kind regards,
    Winston

    • Jova says:

      You can resize the slicers! When you click the slicers you can change the height and width of columns and slicers. You can also, under slicer style click “New slicer style” where you can define your own style, which enables you to change most things, including font size.

  3. Gregory says:

    I hadn’t seen the Group Option used as you did for the Duration PivotTable. And thanks for showing how to remove the Field Buttons on a PivotChart, I loathe them with all my heart.

    Fantastic design and a great dashboard.

  4. Chandoo says:

    @Claudia.. I am glad you like it. Do let us know how your adventures go.

    @Winston: You can resize slicers or increase the number of columns inside. Unfortunately, we can not readjust the font sizes in slicers. So when you resize, you will see partial text.

    @Gregory: Thank you. I am happy you like it :)

    • kris says:

      Hi Chandoo, your dashboards are really professional and simple. I do have some question, if I have the following scenario, could you help to advise : -different data sources eg monthly
      -calculations percentile
      -%difference between financial year

      Thank you so much!

  5. Divya says:

    Hi,

    Thanks for your great information.It has helped me a lot.
    Now,I can build my excel addin for Excel 2010 better with your tips.

  6. Istiyak says:

    Hi chandoo i am new reader for ur site.and really found good stuff and temp. But i suggest u 2 put a guidance step sheet in temp so anyone can understand easily.and also help me to become awesome as ur noume.

  7. Stevros says:

    Chandoo, Wow these are very powerful reports. I will be implementing them straight away. It will save me hours of work. Thankyou so much.

  8. Paul Avenell says:

    Hi Chandoo,

    I love the Slicer, but how do I link a slicer for different data sheets e.g.: Client data on one tab and products on another tab, as I find that as long as you use pivot tables off the same data you can link the Pivot tables using Slicer connections.

    Regards
    Paul

  9. Vivek says:

    I appreciate the work you have posted on your website – very informative and easy to understand. I just wanted to inform you that you can make selections within the slicer too by using Ctrl and selecting the fields you want to group and use as filter.

    I had a question regarding the data used in pivot tables. Is there a way to update the data (eg. a new customer entry) and have the pivot tables and the linked charts in dashboard automatically update? I will search for the answer in other posts so ignore if you have covered it elsewhere.

    Thanks again and keep up the good work.

    -Vivek

  10. Brij Arora says:

    Dear All,

    Me too is a die hard fan of Slicer. it’s requirement was arise when management is feeling it difficult to juggle with filters for sales of a particular location, Product Category in Pivot Table.

    Got very positive response when introduced to tackle the above situation. furthermore in slicer setting there would be option to enable or disable deleted data is handy for particular scenario.

    These are eye catching color themes would be like icing on the cake.

    There is one more feature of excel 2010 which proves to be tool for great time saving is “Repeat Labels” in Pivot Tables.

  11. Katherine says:

    This is fantastic!! Your steps were super to easy follow. I can’t wait to show my new dashboard off to the boss. Thank you so much!

  12. Van says:

    This might be a little unrelated but I’d like to know which software was used to record your on screen actions? I’d like to use it for tutorials on models that I build for my customers. Thanks!

  13. DV says:

    The slicers are coming in a sorted order… How can i get it in the way it appears in my original data…. The settings show to sort them A to Z or the other way round but they are option boxes and can not be unchecked… What are my options????

  14. Duncan Williamson says:

    I watched the video and then worked through an example of my own, also telephone costs by coincidence. It took me about 30 minutes to do everything. Once you’ve understood the basics of pivot tables and slicers, all that limits you is your imagination!

    The only thing missing from the video is now to change the number of columns in a slicer: Right click a slicer then Size and Properties, Position and Layout, Layout, Number of Columns …

    Good page and video.

    Duncan 

  15. soycharnichart says:

    How do you insert ‘Year’ in the Pivot Table Field List if it doesnt exist in the Master table???

    Thanks 

  16. Manu says:

    Hi,

    Can I disable the multi-selection of the slicer to only allow one selection at a time?

    Thanks in Advance    

    • Chandoo says:

      @Manu.. as of Excel 2013, this is not supported yet. But you can remove slicer heading, clear filter button and style it so that it looks like a single selection. You can also use Macros to ignore previous selection upon multiple selection, but I would not recommend it.

      For an example on styling see – Interactive Pivot Calendar

  17. Devin says:

    Awesome guide!  The dashboard I made blew people away.  I do have one question.  I want the chart title to match what I have selected.  How can I do this without writing macros?

    • Hui... says:

      @Devin
      Lets say what you have selected in in A1
      Select the Chart then Select the Title
      Click in the Formula Bar and type =A1
      enter or click the small arrow to the left of the Formula Bar
      Enjoy

  18. Johnny says:

    Love the slicers and use them often in my dashboards.  Question about the data (specifically the date)  I see the “date of call” column but was wondering how were you able to filter on slicers by year and month when there is only a date of call entered into the data?

    Thanks for your help! 

  19. Jet Copeland says:

    Thanks for taking the time to create this interesting and very useful tutorial!
    I was able to create a similar dashboard in a short time after watching your tutorial. The problem I am having now is how to update the pivot tables and dashboard graphs when a change is made in the raw data. I tried two methods; Change data Source and Refresh. When I used Change Data Source (Options-> Change data source) the values in the pivot tables didn’t update. When I tried refresh the values in the pivot tables disappeared as well as the information in the graphs, since the data in the pivot tables no longer existed.
    I have been searching for a solution for a while now but I have unfortunately not been able to solve this problem yet. Any help someone can provide is GREATLY appreciated.
    All the best 

  20. Nigel says:

    Hi, looks great, but how valuable is power view when it comes to financial data? I’ve been having trouble trying to visualize how I would use power view to report of financial data.

  21. Snooky says:

    Hi Chandoo, you are awesome! Thanks for the good work!

  22. beth says:

    there is duplication for my slicer, probably cause i choose date, time as my options. i changed it to date but still theres a duplication of the same date

  23. Alex Cardoso says:

    Just Great! Thank you for the time to put this together and teach us.

    Alex Cardoso from Indaiatuba, Brazil.

  24. jose says:

    First of all I would like to thank you guys for this post I used this amazing tool with the help of your tutorial to create a dashboard for one single account and my regional manager said “good job, it looks very profesional” she was so impresed that now she wants one daschboard with all the acounts and services she is going to replace her KPI reports with my report !! I smell a promotion!! My demand was a new laptop with MS 2010 and it was granted. now I have allot of work and many many questions to post .. kudos

  25. krishna prasad says:

    Hi Chandoo

    I want to say thanks first because i loved ur tutorials

    i have a small doubt how to insert slicer from external connections

    i searched every where could you please explain how to insert a slicer from external source

  26. Jyothi says:

    Hello Chandoo,

    How to get rid of the > items in Months slicer?

    They are appearing when there is a grouping on the date field in pivot

    Thanks

  27. Emma says:

    Hi Chandoo,

    One problem always bothers me when i use slicer. I have no idea aobut how to change the number format in slicer. Want to display number in slicer as general format, but it always displays other number format such as date.
    I check my source data and it doesn’t effect the number format.

    Look forward you or any EXPERTS to solve it. Thanks very much!

    In the end, This website is awesome!!!

  28. Jonas says:

    Hi Chandoo (Or others)

    Is there a way to make the color change, when the value changing after the use of a slicer?

    Lets say the value is 4,5, when i press the slicer, and the value change to 3,5 i would like the color to change. Can anyone help?

    Thank you.

  29. Burendei says:

    Hi Chandoo,

    It was very useful video for me. Thanks.
    But I have one question to ask.
    How can I connect data which is growing in size (rows, records) by time (daily, monthly etc.) to this kind of dashboard?
    Or it is only on select number of data?

    Thank you.

  30. Angela says:

    Chandoo zindabad!

  31. Angela says:

    Hi Chandoo,
    I have been able to create something similar quite easily. The problem that I am facing is that I want to keep the Top 10 filters permanently. If I select one option and then clear the filter, the chart removes the Top 10 filter; I want it to go back to Top 10 filter.

    Is there a solution to this problem?
    Regards

    • Federico says:

      Thanks a lot for the tutorial and for the demo file!
      I have the same problem of Angela: after clearing the filter applyed on P1, the filter on P1 shows all the customers without filtering top 10 (as it was before).
      Thanks!

      Federico

      • Justin says:

        Go to your pivot table, right-click and choose “pivot table options.” On the “Totals & Filters” tab check “Allow multiple filters per field.”

        • Federico says:

          Justin, thank you so much!
          now after clearing the filter applyed on P1, the filter on P1 shows again top 10 customers.

  32. Roger says:

    Chandoo!
    Just find out your website, I´ll follow your tutorials from now, very useful!
    Great thanks from Brazil!!!

  33. Priya Ranjan says:

    Very useful. Learned a new skill today. Thanks a ton!

  34. Manav says:

    Hi Chandoo,

    This is fantastic! It’s going to really help me with some operational reports I develop regularly. Two questions I’m hoping you can answer for me:

    1. How can I use one slicer to manipulate two different pivot charts that came from two different pivot tables?
    2. If I have a slicer in an excel and share that with someone who is on older versions of Excel – what will it look like to them?

    thanks!

Leave a Reply