Clearly and Simply

Tag: table slicers

  • Filter Excel Dashboards with Slicers and Timelines

    How to use Table Slicers and Timelines as interactive Filter Controls on a Microsoft Excel Dashboard

    Filter Excel Dashboards with Slicers and Timelines (Intro)The recent posts

    Filter Excel Dashboards with Table Slicers

    and

    Showcase for Table Slicers on Excel Dashboards

    described a technique how to use Excel’s popular Slicers on tables as easy-to-use, interactive filter controls on a dashboard.

    Although the approach can quickly be implemented and is working fine, it has one major shortcoming: for whatever reason, timelines are only available for Pivot Tables, not for tables. If you have a date dimension in your data (and according to my experiences many data sets do), you can’t let the user filter by dates with a timeline on a table.

    Today’s article will describe a work-around to eliminate this shortcoming. As always, the post provides the example workbook for free download.

    (more…)

  • Showcase for Table Slicers on Excel Dashboards

    A more practical Showcase for using Table Slicers as interactive Filters on an Excel Dashboard including additional features

    The recent post Filter Excel Dashboards with Table Slicers was leading with the following screenshot, showing this prototype of a Customer Care Analysis dashboard:

    Showcase Table Slicers on Excel DashboardsFor instructional purposes, however, the article described the how-to on the basis of a way simpler dashboard than that.

    In the past few days I received a couple of messages from my few, but faithful readers, asking if I could publish this more complex dashboard, too.

    No sweat, here it is:

    Download Showcase Slicers on Excel Dashboards (zipped Excel workbook, 5.1MB)

    If you download the workbook, please be informed that

    • all data in this showcase is randomly and mindlessly made up and serves only for the demonstration of the techniques. The distribution of the call volumes e.g. over countries or hours of the day is totally pointless
    • this is a prototype, which I once developed for a potential client to demonstrate possible views and interactive features on a call center dashboard. This is by no means a consistent and story-telling dashboard. It is a collection of ideas. No more, no less.

    That being said, it does include a few interesting techniques. The filtering by table slicers, of course, but also

    • the option to sort table views directly on the dashboard using radio buttons above the column headers (call volume by brand and country)
    • the option to let the user decide which measure and target shall be shown on a chart (Average Handling Time or Waiting Time)
    • dynamically changing flag icons inside the table view “Call Volume by Country”

    Agreed, nothing really new under the sun and you probably have seen most of this already. But still, it may be interesting to see all these features combined with the table slicers.

    And for the records: all this is done without VBA. Just good old Microsoft Excel.

    Stay tuned.

  • Filter Excel Dashboards with Table Slicers

    How to use Table Slicers as interactive Filter Controls on a Microsoft Excel Dashboard

    Filtering data by one or several criteria is certainly one of the most common and important activities in data analytics. Adding visual filters to a dashboard is a built-in feature in Tableau and Power BI and as such a walk in the park.

    But what if you need interactive filter controls on a dashboard in Microsoft Excel?

    Filter Excel Dashboards with Table Slicers Intro

    Sure, you can use data validation lists, form controls or ActiveX controls as interactive dashboard objects to set a filter or at least make a selection. You could also write some VBA code to let the user filter data by directly interacting with cell ranges or chart elements. All viable options, but either limited in functionality or a lot of effort to implement.

    Wouldn’t it be nice if you could simply use the slicers, which have become a very popular way for facilitating the filter process on Pivot Tables? In other words, wouldn’t it be nice to create a dashboard like this in Microsoft Excel?

    A Microsoft Excel dashboard with a variety of views (numbers, tables, charts), all filterable using the visual filter controls above the views (the slicers).

    You guessed it, right? It is possible. And even better: it is very simple and straight forward to implement. No VBA, no limitation to Pivot Tables or Pivot Charts, no Power Query, no DAX formulas. Just good old Excel.

    Today’s article explains the basics of the technique, describes the process of the implementation step-by-step and – as always – provides the example workbooks for free download.

    (more…)