Clearly and Simply

Author: Robert

  • Another Technique for Interactive Excel Charts

    Another technique to create interactive charts in Microsoft Excel using an ActiveX Label Control on top of the chart

    Where is it?Interactive features add a lot of analytical power to dashboards. If you want to create a professional analysis dashboard, interactivity is almost a must-have.

    Unfortunately, Excel does not provide built-in interactive features for charts. However, this doesn’t mean you can’t have interactivity on Excel dashboards.

    As always, VBA is the way to overcome Excel’s shortcomings.

    We already had a couple of articles providing workbooks with interactive features, like Bluffing Tableau Actions with Microsoft Excel, The Next Level of Interactive Microsoft Excel Dashboards, Microsoft Excel Site Catchment Analysis, Better Chart Tooltips with Microsoft Excel 2010.

    All of them were taking advantage of the chart object’s mouse event procedures. More precisely, they were based on the great code provided by Jon Peltier here: Get XY on any Chart.

    I recently discovered another technique to implement interactivity on Excel charts. Andy Pope uses an ActiveX label control on top of a chart to track and evaluate mouse positions. Unlike the chart object mouse events, Andy’s approach doesn’t require to activate the chart first.

    I “stole” Andy’s idea and used his technique to create a little interactive geography quiz in Excel: find European cities on a map. Today’s article describes the implementation of the workbook and the code and – as always – provides the Excel file for free download.

    (more…)

  • Cartograms in Tableau

    Plotting preprocessed polygon data as Cartograms in Tableau: the results of the US Presidential Elections since 1900

    Cartograms of the US Presidential Elections plotted in TableauChoropleth Maps or Filled Maps (as they are called in Tableau) are a great technique for analyzing geographical data, especially for maps with a high level of detail (e.g. US by counties or ZIP codes). They make it very easy to identify geographical hot spots first and then drill down into these regions using other visualization types.

    Having said that, they also have their limitations and disadvantages. Even worse, under certain circumstances Choropleth Maps can be misleading and visualize the data incorrectly.

    A classic example of misleading Choropleth Maps are the well known US Presidential Election Maps. We all have seen them, right? A map of the USA with color coded states: a red fill color if the Republican candidate won the state and a blue fill color if the Democratic candidate won (and in some years other colors for independent candidates).

    Although these maps correctly depict the geographical distribution of “who won where”, they are usually telling the wrong overall story.

    How come?

    Traditionally Republicans very often win some of the larger states with a small population density like Alaska, Montana and Wyoming (and thereby only a small number of electoral votes). As a consequence red often dominates the color coding of the map, even if the Democratic candidate won the entire election.

    Cartograms are a geographical visualization on a map avoiding this problem. Cartograms are distorting the map by resizing its regions according to e.g. the population, the popular votes or the electoral votes, At the same time the Cartogram algorithm tries to retain the topology of the map as far as possible.

    Today’s article presents a dashboard on Tableau Public comparing a Filled Map with a Cartogram for every US Presidential Election since 1900.

    (more…)

  • Create Excel Freeform Shapes from Polygons

    How to transfer polygon data into freeform shapes in Microsoft Excel

    Create Excel Freeform Shapes from PolygonsIf you are a regular reader or even subscribed to this blog, you will not be surprised to hear that I am working on a new challenge regarding maps in Microsoft Excel.

    We had a lot of articles already on how to create Choropleth Maps in Microsoft Excel. The new project is about using maps in Excel again, but with a different type of visualization.

    Since I am just starting out on this challenge, I can’t provide more details yet.

    Having said that, for the preparation of the new project, I had to work with geographical polygon data. Now, what’s this?

    “A polygon is […] a plane figure that is bounded by a finite chain of straight line segments closing in a loop to form a closed chain or circuit.” (source: Wikipedia).

    In the context of geographical visualization, a polygon represents a region (e.g. country) on a map. Such a polygon consists of a series of geographical points: pairs of latitude and longitude in a defined order (path). If you draw an outline through these points in the given order, you get a shape forming the boundary of this region.

    And this was my challenge: transfer geographical polygon data into freeform shapes in Microsoft Excel. I am not sure, but I thought maybe some of you might be interested, so I decided to share my solution.

    Today’s post first shows how to plot polygon data on standard Excel XY Scatter charts and then describes my solution to transfer the data into freeform shapes. As always, the article provides a link to download the Excel workbook for free.

    (more…)

  • Excel Oddity: Camera Objects bloat File Size

    Camera objects (aka linked pictures to cell ranges) can considerably increase the size of an Excel workbook. Why?

    Vintage Camera - Photographer: Peter Miller (flickr.com)Camera Objects (aka linked pictures) are an easy-to-use, powerful and flexible technique in Excel, especially for making dynamic and interactive charts and dashboards.

    I learned this technique years ago from Charley Kyd’s fantastic e-book Dashboard Reporting With Excel.

    My good friend and Excel MVP Chandoo also wrote several articles about the Camera Object (all articles with ‘camera tool’ tag on chandoo.org).

    The feature may still not be as known among Excel users as it should be, but there are so many very good tutorials available that I do not need to write another one.

    As great as they are, Camera Objects always had a few issues (printing, merged cells, distorting captured charts, etc.) as Charley points out here: A New Excel 2010 Camera Tool Bug, and a Workaround.

    Today’s article will describe not a real bug, more an Excel oddity or annoyance regarding Camera Objects: the extensive use of Camera Objects can let the file size of your workbook explode, especially if they are linked to cells with inserted images sitting on top. I took a closer look into this and today’s post describes what I found out.

    (more…)

  • Highlighting on Excel Dashboards

    Highlight one selected item across all charts and views of an Excel Dashboard: an example and the how-to

    Dashboard EU Unemployment RatesVery often, a dimension is displayed on more than one view of a dashboard.

    Let’s say you want to analyze data of the European Union. Chances are that you will have the dimension “member state” on more than one of your charts.

    If you pull these charts together into one dashboard, highlighting a selected member state across all charts is very helpful to explore and analyze the data. It supports the user to easily focus on the selected state and to identify its position in the context of all members at a glance.

    Highlighting is the very simple act of selecting one item out of many (by e.g. clicking) and automatically seeing this item emphasized in all views across the entire dashboard. This is a very effective and user-friendly visualization technique and should be available on every interactive dashboard.

    Today’s post shows how to implement highlighting on an Excel dashboard. As always, including the Excel workbook for free download.

    (more…)