Clearly and Simply

Category: Tools

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

(more…)

April 17, 2015
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.

(more…)

January 20, 2015
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.

(more…)

May 15, 2014
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.

(more…)

August 26, 2012