Clearly and Simply

Author: Robert

Cartograms in Microsoft Excel

How to create Cartograms in Microsoft Excel

The article Cartograms in Tableau described the limitations of Choropleth Maps under certain circumstances and why Cartograms can sometimes be a viable alternative to color coded maps.

Building upon that, the previous post (Create your own Cartograms in Tableau) provided a Microsoft Excel tool to create the polygons for Cartograms based on your own data. The Excel tool (called Cartogram Data Generator) is based on my VBA implementation of “an algorithm to construct continuous area cartograms” by J. Dougenik, N. Chrisman and D. Niemeyer, published in “Professional Geographer” back in 1985.

With this VBA algorithm and two other techniques previously published here (Faster Choropleth Maps with Microsoft Excel and Create Excel Freeform Shapes from Polygons), we have all modules available to calculate and plot Cartograms directly in a stand-alone Microsoft Excel workbook.

Today’s post describes how to create Cartograms in Microsoft Excel and provides two versions of the Cartogram workbook for free download.

(more…)

June 30, 2015
Create your own Cartograms in Tableau

The Cartogram Data Generator: a free Excel Tool to create your own polygon data for plotting Cartograms in Tableau

One of the recent posts showed how Cartograms can be a viable alternative when Choropleth or Filled Maps are stretched to their limits and tell the wrong overall story:

Cartograms in Tableau

The article included a Tableau Public dashboard comparing Filled Maps with Cartograms for the results of the US Presidential Elections since 1900.

Since Tableau does not provide Cartograms as a built-in chart type, the distorted polygons have to be calculated outside of Tableau and then used as a data source to plot the maps using Tableau’s polygon map approach.

Today’s article is the follow-up post. It provides a free and open Microsoft Excel workbook to create Cartogram data with a few mouse clicks: copy in the data and the original polygon points of all regions and let the tool create a ready-to-use data set to plot Cartograms in Tableau.

(more…)

June 20, 2015
German Public Holidays in Tableau

Calculated Fields in Tableau for German Public Holidays

The previous post explained why color coding public holidays can be very effective for analyzing daily data, provided the Calculated Fields for all public holidays in the United States and included an interactive calendar for the US on Tableau Public.

The Calculated Fields for the US public holidays are pretty straight forward and not too complicated.

This can be different in other countries. And it sure is in my homeland. In Germany, all variable public holidays depend on Easter Sunday. And, if you read on, you will see that Easter Sunday requires a really complex calculation.

Today’s post provides the Calculated Fields for all German public holidays and a German version of the interactive calendar on Tableau Public.

(more…)

May 31, 2015
US Public Holidays in Tableau

Calculated Fields in Tableau for US Public Holidays

For the analysis of daily data, the weekday can be an important and helpful additional information.

For instance: if you are analyzing sales or order figures, incoming calls in a call center, internet usage, web statistics or the like, you will probably see significant differences in the data between working days, weekends and public holidays.

Your views and dashboards should enable the reader to immediately distinguish between the three types, e.g. by color coding. This is pretty easy to do for the weekends, but it takes some additional efforts to include the public holidays, too.

Today’s post explains why color coding daily data by the type of the day can be important for analysis and visualization. The article provides and describes Calculated Fields in Tableau for all US public holidays and put them to use in a Tableau Public dashboard showing an interactive calendar with all public holidays in the US between 1900 and 2099.

(more…)

May 30, 2015
Selecting and Highlighting Areas on Excel Charts

Interactive Selecting and Highlighting Areas on an Map or an XY Scatter Chart in Microsoft Excel

The previous post demonstrated an alternative technique to implement a direct interaction with an Excel chart using an ActiveX label control sitting on top of the chart. The example used in that post (a geography quiz) allowed a click on a map and evaluated the coordinates of the position the user clicked on.

The technique, however, can also be used in many more cases. If you are working with an XY scatter chart and especially if you are working with a map, interactive selecting and highlighting a user-defined area of the chart can be a very helpful feature for exploring and analyzing the data.

Tableau Software, for one, allows for different ways of selecting and highlighting areas. Microsoft Excel has no such features built in. With the technique described in the previous post and some additional VBA code, however, you can bluff almost the same functionality in Excel.

Today’s article describes how to implement selecting and highlighting features on Excel charts. As always the example workbook and the VBA code is provided for free download.

(more…)

May 27, 2015