Clearly and Simply

Category: Charts

  • An Alternative Design of Bullet Graphs

    An alternative design of Bullet Graphs: no qualitative ranges, additional data labels and a visualization of the performance gap (if applicable)

    Bullet Graph Alternative DesignMore than 10 years ago, Stephen Few of Perceptual Edge introduced the Bullet Graph: an alternative chart type to replace the formerly (and unfortunately sometimes still) popular gauges and speedometers on dashboards. A Bullet Graph visualizes a performance metric compared to a target (or other comparative measures) in a small, clear, simple and effective view. In the meantime, the Bullet Graph has become a widely respected, even standard chart type. Some major Data Visualization software applications like Tableau Software natively support Bullet Graphs.

    Back in 2009, Matt Grams described a possible solution of creating Bullet Graphs in Excel here on Clearly and Simply: Bullet Graphs for Excel: A Simple Way?

    I am using Bullet Graphs very often in my models for a very long time already, especially on Performance Measurement Reports and Dashboards. Starting with Stephen’s original design specification, I received a lot of feedback from my clients over the years. As a consequence, I made a few variations to the design of the Bullet Graph regarding the qualitative ranges, data labels and the performance gap (if applicable, i.e. if actual is smaller than target).

    Today’s article describes my variations of the original Bullet Graph design, explains the background why I made them, shows how to implement the charts in Microsoft Excel and provides the corresponding Excel 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…)

  • S-Shaped Function in Microsoft Excel

    A ready-to-use template for an S-shaped Function in Excel

    S-shaped function ExcelThe previous post S-Shaped Function in Tableau discussed and provided the implementation of a customizable, i.e. user-defined S-shaped function in Tableau Software.

    In the introduction to this post I wrote: “Having a ready to use S-shaped function in Tableau is a must-have for your Advanced Analytics Toolbox.”

    This applies to Microsoft Excel, too. Maybe even more.

    There are quite a few different S-shaped functions in Excel available on the Internet. The first implementation I saw was a post by Juan C. Mendez back in 2007. Unfortunately Juan’s site is down now, but an archived version is still available here. Others built upon Juan’s approach, like Mathias over at Clear Lines Consulting and this site is still up.

    Juan and Mathias used a different formula than I do (in Tableau and in Excel), but the result is pretty much the same. The only new feature in my implementation is the option to highlight the period of fast growth of the S-shaped function with a reference band.

    So, nothing really new under the sun in this post, but – for the sake of completeness – here is my Microsoft Excel template of an S-shaped function for free download:

    Download S-shaped Function (Microsoft Excel 2007-2016 workbook, 26K)

    If you are interested in how to use the parameters to shape the curve, please refer to the previous post S-Shaped Function in Tableau.

    More things to come soon.

    Stay tuned.

  • User-defined Excel Chart Tooltips

    How to provide different options of customized tooltips on a Microsoft Excel chart and let the user decide which one to display

    User Defined Tooltips in Microsoft ExcelOne of the previous posts described four different techniques how to create customizable, meaningful tooltips on XY Scatter Charts in Microsoft Excel:

    Customizable Tooltips on Excel Charts

    Two of the approaches used a camera object (aka linked picture) to allow more formatting options of the tooltip.

    This idea can be taken one step further: with camera objects, you can easily provide more than one type of tooltip and let your user interactively select the tooltip which is most helpful for his analysis.

    Today’s article describes how to enable your user to select from five different tooltips with one single click: dimensions and measures, only a dimension and three additional charts displaying further information on the data point currently hovered over with the mouse. The post describes the idea and the implementation and of course makes the example workbooks available for free download.

    (more…)

  • Customizable Tooltips on Excel Charts

    How to create customizable, meaningful tooltips on XY Scatter Charts in Microsoft Excel

    Customizable Chart Tooltips on Excel XY Scatter ChartBack in December 2010, I published an article about Better Chart Tooltips with Microsoft Excel. The post described the weaknesses of Microsoft Excel’s standard chart tooltips and provided a VBA-based technique how to get to customizable, meaningful and more useful tooltips in Excel.

    Today, I would like to revisit this topic for several reasons:

    Firstly, Microsoft hasn’t done anything about this shortcoming in the versions 2013 and 2016. The problem of insufficient chart tooltips in Excel remains.

    Secondly, the workbook provided in 2010 implemented the better chart tooltips on another dashboard with additional features, which was published here: Bluffing Tableau Actions with Microsoft Excel. Many people have asked for a simplified, generic template only providing the tooltips on a standard XY Scatter chart.

    Next, reader Will Clark came up with the great idea of using a camera object instead of a simple textbox, in order to have more formatting options.

    Last, but not least, I discovered another approach for creating interactive charts in Excel. I already used and published this in several posts (Another Technique for Interactive Excel Charts, Selecting and Highlighting Areas on Excel Charts, Select Areas on a USA Map in Microsoft Excel and Zooming in and out of Excel Charts), but this technique is also viable for implementing chart tooltips.

    Today’s post will discuss again Excel’s shortcomings in terms of chart tooltips, briefly describe 4 different techniques how to overcome this issue and – as always – provide the Microsoft Excel workbooks for free download.

    (more…)