Tag: excel dashboards

  • Highlight Actions in Microsoft Excel

    How to implement a specific Highlight Action Use Case in Microsoft Excel

    Highlight Actions in ExcelHighlight Actions in Tableau allow you to visualize all data points related to a user-selection across different views on a dashboard by coloring the relevant marks and shading off all others.

    Highlight Actions are a great feature for interactive data exploration in general, but there is one specific use case where highlighting is extremely useful:

    Imagine you have a data set with a hierarchy of dimensions like Product Category|Product Sub-Category|Product, State|County or something similar. If this is the case, chances are that the dashboard will show consolidated results per e.g. Product Sub-Category in a table or Bar Chart and a distribution of all Products by two measures (e.g. Sales and Profit) on an XY Scatter Chart. With a Highlighting Action in place, you can easily click on one Sub-Category in the first view and immediately see all related data (i.e. all Products belonging to this Sub-Category) on the XY Scatter Chart. Highlight Actions are preferable to Filter Actions in this case, because they show the  members of the selected category within the context of the distribution of all data points.

    Creating Highlight Actions in Tableau is a piece of cake. A few mouse clicks and you are good to go. But what if you want to have a similar feature on your Excel dashboard? Unfortunately, Excel does not provide actions across views natively, but with a few lines of VBA code and a simple charting trick, you can replicate Tableau’s Highlight Actions in Microsoft Excel, too.

    We already had a couple of posts here providing similar functionalities with a comparable approach:

    Bluffing Tableau Actions with Microsoft Excel

    Highlighting on Excel Dashboards

      Mouse Driven Actions on Excel Dashboards

    Today’s post is along the same lines, but focusses on the specific use case described above. It explains how to create this particular Highlight Action in Excel and provides an example workbook for free download.

    (more…)

  • Mouse Driven Actions on Excel Dashboards

    Add context and interactivity to your Excel Dashboards with mouse hovering and clicking actions

    Mouse driven Action Dashboard in ExcelThe previous posts Customizable Tooltips on Excel Charts and User-defined Excel Chart Tooltips showed how to provide insightful tooltips when hovering over a data point on an Excel chart.

    The idea and its implementation can be taken one step further: instead of displaying a tooltip, hovering over or clicking on a data point of an Excel chart can send this information across your dashboard to other views (charts or cell ranges). I.e. you can select one data point out of many in one view and display detailed information on this selected data point in other views.

    My other favorite data analysis and data visualization application, Tableau Software, calls this an action. This is a fantastic feature for data exploration and analysis, especially for larger data sets.

    Today’s post shows how to mimic Tableau’s mouse hovering or mouse selecting actions on an Excel dashboard. As always, the Excel workbook is provided for free download.

    (more…)

  • Bruce Springsteen Discography in Excel

    A Microsoft Excel Replica of the Tableau Infographic on Bruce Springsteen’s Discography

    Bruce Springsteen at the Austin Music Awards - Photographer: Charlie Llewellin (flickr.com)The recent post presented a Tableau Infographic on the Discography of Bruce Springsteen’s Studio Albums.

    Tableau apparently liked my Infographic and selected it as the Viz of the Day for June 25, 2013 and even incorporated it in the Tableau Public Gallery. I am feeling honored. Thanks, folks!

    I originally planned to directly continue with a follow-up post about how to create and when to use Word Clouds in Tableau.

    On second thought I decided to postpone this follow-up article. I haven’t published anything for more than 6 months and I had a strong feeling I should quickly do something for those of you primarily interested in Microsoft Excel.

    So, if you are waiting for the article on Word Clouds in Tableau, please bear with me. The post will come soon.

    Those of you interested in Excel: this is for you. What would have been more obvious than publishing a replica of the Bruce Springsteen Discography in Microsoft Excel? It goes without saying that the Excel workbook mimics the most interesting interactive features of the Tableau original. As always the article provides the Excel workbook for free download.

    (more…)

  • Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard

    Bluffing the “Beer Prices at Oktoberfest” Tableau Dashboard with Microsoft Excel using Roll Over Tooltips and Web Actions

    Oktoberfest Impressionen - Photographer: sanfamedia (flickr.com)Recently we had an article visualizing the beer prices and beer price development at Oktoberfest with a Tableau dashboard: O'zapft Is!

    Oktoberfest is long over already and there is peace in the valley of Munich again. Having said that, I would like to come back to this visualization once more. We had a couple of posts here on Clearly and Simply showing how to bluff Tableau’s great interactive features with Microsoft Excel:

    Bluffing Tableau Actions with Microsoft Excel

    Bluffing a Visual Cross-tab with Excel

    The Next Level of Interactive Microsoft Excel Dashboards

    Microsoft Excel Site Catchment Analysis

    Drawing on this tradition, today’s article presents an Excel workbook emulating the Tableau Oktoberfest Dashboard. The technique behind this bluff is a very clever approach my blogging colleague Jordan Goldmeier published in several articles over at his Option Explicit VBA Blog.

    Stealing Jordan’s idea, today’s post shows how to recreate the Oktoberfest Beer Price Tableau dashboard with Microsoft Excel, as always including the Excel workbook for free download.

    (more…)

  • Excel Multiple Value Filters with Invert Selection

    How to enhance a multiple value filter check box control with an invert selection option in Microsoft Excel

    Invert SelectionThe comments and emails I received on the recent post Excel Dashboard Controls – Multiple Value Filters reinforced my conclusion that the original idea of providing a feature to switch back to a previous selection is more confusing than helpful.

    However, there is another option to really improve the functionality of multiple value filters. Leonid, one of my few but faithful readers was kind enough to share a very interesting idea: add an additional input feature to let the user invert the actual selection.

    Leonid provided 2 different solutions. Number 1 is a simple inversion symbol with additional explanatory text. Clicking on the symbol inverts the actual selection of filters:

    Invert Selection V01

    The VBA code triggered when clicking on the symbol is straight forward:

    Sub InvertFilters()
    Dim rng_cell As Range
        For Each rng_cell In Range("myActualFilter")
            rng_cell.Value = Not rng_cell.Value
        Next rng_cell
        Range("myCheckBoxAll").Value = _
                 Application.WorksheetFunction.And(Range("myActualFilter").Value)
    End Sub

    The code simply inverts all target values of the check boxes from TRUE to FALSE and vice versa.

    Here is the workbook for free download:

    Download Multiple Value Filter Control with Invert Selection v01 (Microsoft Excel 2003, 91K)

    If the additional row bothers you, you may want to look at Leonid’s alternative option: use a hyperlink on the symbol to provide a tooltip (”Invert Selection”):

    Invert Selection V02

    There is an additional cell and some more code necessary (on the dashboard worksheet), but the implementation is still pretty straight forward. Here is the workbook:

    Download Multiple Value Filter Control with Invert Selection v02 (Microsoft Excel 2003, 96K)

    Leonid, many thanks for sharing your ideas here and for your workbooks. Your contributions are highly appreciated.