Clearly and Simply

Category: 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…)

  • Zooming in and out of Excel Charts

    Interactive zooming in and out on Charts in Microsoft Excel

    Zoom in and out on Excel ChartsIf you have an XY scatter chart with a lot of data points in Microsoft Excel or even using an XY scatter chart mimicking a map in Excel, an interactive zooming function (allowing to magnify user-defined parts of the chart) can be very helpful.

    Microsoft Excel does not provide such a feature natively. There have been a few workarounds published. Most of them use interactive controls like spinners or sliders to change the axes scales with OFFSET formulas and dynamic cell ranges or by VBA.

    A more intuitive way of zooming into a chart would be to select an area on the chart with the mouse and zoom exactly into this user-defined part of the chart (or map).

    We recently had a couple of posts taking advantage of Andy Pope’s stellar idea to use a label control on top of a chart for interactive chart features:

    Another Technique for Interactive Excel Charts

    Selecting and Highlighting Areas on Excel Charts

    Select Areas on a USA Map in Microsoft Excel

    Andy’s technique is very versatile and can be a viable solution for many different use cases, including the requirement of an interactive zooming feature.

    Today’s post describes an implementation of an interactive zooming feature for a map (XY scatter chart) in Microsoft Excel. As always, the workbook is provided for free download.

    (more…)

  • Select Areas on a USA Map in Microsoft Excel

    Interactive radial or rectangular area selection on a map of the United States in Microsoft Excel

    One of the recent posts demonstrated how to mimic Tableau’s rectangular and radial map selection features in Microsoft Excel:

    Selecting and Highlighting Areas on Excel Charts

    The example workbook used a map of Europe. Since more than 40% of my readership is located in the United States, I thought it might be a good idea to also share a template with a map of the USA. Here it is for free download:

    Download Select and Highlight Areas on a US Map (zipped Microsoft Excel 2010 – 2013 workbook, 1.3MB)

    This is an example screenshot of the dashboard:

    Interactive Selection on a USA Map in Excel - click to enlargeThe map (the contiguous 48 states, i.e. excluding Alaska and Hawaii) includes all cities in the United States with more than 5,000 inhabitants, divided into 2 categories (greater than or less than 500k inhabitants), visualized with different sizes of the dots.

    To define a radial or rectangular selection (choose with the radio buttons), keep the SHIFT key pressed, click somewhere on the map with the mouse key pressed, move the mouse to span the selection area and – as soon as you are done with your selection – release the mouse key. The metrics above the map and the bar charts (selection statistics by states) right to the map are updated on the fly.

    For more details on how to use the template and how this was implemented in Excel, please have a look at the original post.

    I hope this will be helpful, especially for my friends and readers in the United States.

    Stay tuned.

  • Export Excel Dashboards (Improved Version)

    An improved version of the example workbook exporting Excel Dashboards to other applications

    Improvement - Photographer: Celestine Chua (flickr.com)The previous post provided an example Excel model with the option to export dashboards to other applications and file formats.

    The workbook came with one serious limitation: the PDF and XPS export routine created a separate file for each dashboard, instead of one file with all dashboards.

    In the article I stated, I wouldn’t see how to get around this without using an external PDF creator. It turns out that I simply couldn’t see the wood for the trees.

    It was (again!) Leonid Koyfman, long-time reader and contributor, who pointed me into the right direction. The idea is as simple as it is efficient: create a temporary Excel workbook, copy all dashboards to it (each dashboard on a separate sheet), use the ExportAsFixedFormat method of the workbook object to export the entire workbook (i.e. all dashboards) to one PDF or XPS file and finally close the temporary workbook without saving changes. So simple, so obvious. I should have figured that out on my own. But I didn’t. Many thanks to Leonid for the idea.

    Here is the improved version for free download:

    Download Export Excel Dashboard to other Applications – Improved Version (zipped Microsoft Excel 2010 – 2013 workbook, 210K)

    Stay tuned.