Clearly and Simply

Create Excel Freeform Shapes from Polygons

How to transfer polygon data into freeform shapes in Microsoft Excel

If 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.

Microsoft Excel Check List Compilation

A compilation of variations and enhancements of the interactive Microsoft Excel Check List Template

Clearly and Simply is supposed to be a blog on data visualization and data analysis. Hence, it is a bit embarrassing to admit that the most popular blog post ever is totally off topic. Believe it or not, it is the Microsoft Excel Check List Template published in August 2012.

Most hits on any given day, second most comments (176 so far), most downloads.

This is probably the case because I somehow managed to be shown on Google’s first page if you search for “Excel Checklist” or “Checklist Template”. Very popular searches, I guess.

Anyway. People seem to like this interactive check list and asked for various modifications and enhancements of the template. I already posted a few variations, but since they are buried in the avalanche of comments on the original post, I thought a compilation of the frequently asked enhancements could be helpful.

Today’s post provides the original check list and 14 of the most interesting modifications and enhancements: time and user stamped check lists, more checkboxes per item, check list with 3 possible states of an item and some more.

As always, the Excel workbooks including the unprotected VBA code are provided for free download.

Extract Custom Shapes from a Tableau Workbook

How to extract the Custom Shape Images embedded in a Tableau Workbook

In Tableau Software you can use the Shape Mark property to encode data in a view by different shapes. You can either use Tableau’s default shapes (circles, squares, crosses, etc.) or so called Custom Shapes. Each Tableau installation comes with a set of Custom Shape palettes like arrows, bars, currency and gender symbols and others. Have a look into the Shapes folder of your Tableau Repository to see what is already there.

On top of that, you can also add your own Custom Shape Palette to this collection. Simply create a new folder in the Shapes folder of your Tableau Repository and copy the image files you want to use as shapes (.png, .gif, .jpg, .bmp or.tiff, but no .emf) into this folder. If you then assign shapes in the Edit Shape dialogue in Tableau, this folder automatically appears in the Select Shape Palette drop down and your images can be used to encode the data.

Tableau stores the used Custom Shapes in the .twb file, to make sure the workbook is fully functional on every computer, i.e. also on installations which do not have the Custom Shapes in the Tableau Repository.

So much for the background. And so far, so good.

Now, imagine you have a Tableau workbook using Custom Shapes, but you do not have the image files in your Tableau Repository, because you are working with a new or different computer, you received the workbook from a colleague or downloaded it from Tableau Public.

What if you want to reuse the Custom Shapes in another workbook?

Is there an option to extract the Custom Shape image files from a Tableau workbook?

Not built-in, but there are two existing workarounds provided by Matt York in the Tableau Forum and on the Tableau Public Blog. Although Matt’s solutions are very smart and easy to use, I decided to add my 2 cents with a third workaround.

Today’s post includes the links to Matt York’s Tableau Shape Extractor workarounds and describes a third option of how to do the same with a Microsoft Excel workbook. As always, the article provides the Extract Custom Shape Excel tool for free download.

Microsoft Excel Check List Template

A Microsoft Excel template for a structured Checklist with the option to check and uncheck by double clicking

Unfortunately my blogging activities slowed down to a crawl during the last few months and I left you waiting for new posts far too long already.

Be assured that this site is not dead. I will revive the blog during the next weeks and I am already working on a couple of new articles.

For one of the planned next posts I needed a checklist template. There are tons of Microsoft Excel and Microsoft Word examples available for free.

However, I couldn’t find a checklist template I really liked. Formats are always easy to change, but I was particularly looking for a checklist providing a convenient way to change the status of the checklist items. Furthermore it should be easy to use and easy to maintain. Some of the templates I found simply expect to type in an X (or something similar) to check an item, some are working with data validation lists, some have form control checkboxes. The one that came closest to what I was looking for is provided by my good friend Daniel Ferry, the Excel Hero here: Excel Dynamic Checkmark. Already pretty close to what I was after, but since it did not fulfill all of my requirements, I decided to create my own.

Agreed, today’s post is a bit off topic regarding the focus of this blog. It has nothing to do with data analysis, data visualization or dashboards. However, a nice Excel checklist template is always a useful thing to have in the toolbox.

With today’s article I am trying to kill two birds with one stone: to show a sign of life and to share my little checklist template with you.

Create Your Own Filled Maps in Tableau

A step-by-step guide to Richard Leeke’s TabGeoHack for creating your own filled maps in Tableau Software

Way back in 2009, we had a beautiful guest post by Giedre Aleknonyte describing a workaround to generate Choropleth Maps with Tableau (using version 5.0 by the way).

Those days are over. One of the major new features of Tableau 7 is Filled Maps (or Choropleth Maps as we used to call them in all blog posts here).

Did I say those days are over? Well, not quite. While Tableau provides filled maps down to the zip code areas in the United States, the level of detail in other countries is more or less rudimentary. In Germany, for example, you can create filled maps only for the 16 states (Bundesländer). This is nowhere near sufficient to do really compelling geographical data analysis and visualization. Up to now there was no way to do anything about it.

My friend Richard Leeke recently developed a fantastic tool called TabGeoHack which allows you to import literally any geographic level of detail of any region in the world into Tableau in order to use them for visualizations using filled maps. Richard is a co-founder of Equinox Ltd, a New Zealand based IT consulting firm and if you are a long time reader of this blog, you certainly already know him from the fantastic guest post series on Site Catchment Analysis. Even if you haven’t seen those articles, you probably know him from the Tableau Forum.

Surprisingly enough, Richard made his tool generously available for free in this Tableau Viz Talk post.

Please be aware that TabGeoHack – as its name suggests - is a totally unsupported hack utility, using an open back door of Tableau. The tool is a workaround with a certainly limited lifetime. It is beyond question that Tableau will provide similar built-in functionality with one of its next versions.

Richard was kind enough to grant me a sneak preview of TabGeoHack before publishing. Although the download includes a very detailed description and instruction manual, it took me quite a while to cut my teeth on the use of TabGeoHack. That’s why I thought it would be a good idea to write a step-by-step article on how to use TabGeoHack, including an example on Tableau Public, visualizing unemployment rates in Germany, broken down by counties, on a Filled Map.

So, do you want to create your own filled map of e.g. English counties, Spanish municipalities or even the individual sales regions of your company?

Here you go.