How to create an interactive “Pivot-like” Marimekko Chart in Excel allowing the user to select the visualized dimensions and measure on the fly
The previous post Marimekko Charts in Microsoft Excel described and provided a VBA-based template to create a Marimekko chart in Excel made up of freeform textboxes. In the article I claimed, the used approach would offer some flexibility. Action speaks louder than words, so let’s turn this rhetoric into action.
A Marimekko chart visualizes one measure (numerical) by two dimensions (categorical). The original template provided in the previous post was based on a data source arranged in a crosstab.
Now let’s say you have a data source with several dimensions and measures organized in a flat table. That’s the norm, isn’t it? If you want to quickly analyze this data in Excel, you will probably use a Pivot Table and maybe a Pivot Chart allowing you to easily select which dimensions shall go to rows and columns and which measure shall be summarized in the values area. Now this is exactly the feature we want to bring to our Marimekko chart template.
Today’s post describes how to enhance the existing template with interactive features to enable the user to easily define what shall be displayed in the Marimekko chart. As always, the article includes the Microsoft Excel workbook for free download.
The goal of today’s post is easy to describe: Turn the existing Excel Marimekko chart template into an interactive visualization which allows the user to select which dimension shall be displayed in the rows, which dimension in the columns and which measure shall be size encoded and visualized in the value area. In other words: a Pivot-like Marimekko chart.
Set Up the Workbook
First we have to make some structural changes on the worksheets:
As mentioned above, the source data isn’t organized in a crosstab anymore. Instead, we have a simple, flat data table like this:
This data is an extract of the “Sample - Superstore Sales” workbook coming with Tableau Software. The table has several dimensions like Customer Region, Product Category and Order Priority and some measures like the Order Quantity, Sales and Shipping Cost. It is just a made up database for testing, but it is fully sufficient for demonstration purposes.
Next step is to insert a Pivot Table based on this data and to place it on a new worksheet. I think I do not have to go into the details, you know what to do. Now drag one dimension on the rows, one on the columns and one measure on the values, i.e. create the most simple Pivot Table like this one:
To address the Pivot Table in the VBA code later, I recommend to change the name e.g. to “myPivotTable” (on the ANALYZE tab of the ribbon).
In the original template the [Control] worksheet contained only the definition of the color scheme. We will now use this sheet to store some other things:
Manually insert a list of the names of all dimensions (B5:B10) and a list of the names of all measures (C5:C8). Then define 3 cells (C14:C16) to store the user selection. These cells will be used as the target cells of the combo boxes (see below). And finally insert 3 simple INDEX formulas (B19:B21) to get the field names of the current selections from the lists above.
Now we have to adjust one of the existing names and add a new one. We will later address these names in the VBA code. Go to the Name Manager (CTRL+F3) and change the name “myData” to refer to the data body of our Pivot Table, i.e. PivotTable!$C$7:E$11 in the example shown above). Next step is to add a new name called “myPTDefinition”, referring to Control!$B$19;$B$21, i.e. the field names of the current combo box selection:
Finally go to the sheet [Marimekko] and insert 3 form control combo boxes (click on Insert on the DEVELOPER tab): one to select the field on the rows, one for the field on the columns and one for the measure to be visualized. Define the input ranges and cell links of the combo boxes according to the lists and target cells we created on sheet [Control], see above. Here is what this could look like:
That’s it for now on the worksheets. Let’s move on to the VBA part.
The additional VBA Code
Since we changed the named range myData to refer to the values area of the Pivot Table, we do not have to change anything in the existing sub which create / updates the Marimekko chart.
However, we need some extra coding to
- change the definition of the Pivot Table (fields in rows, columns and in the value area) according to the selection the user made with the combo boxes
- adjust the named range myData to make sure it refers to the entire values area of the Pivot Table after the user changed the fields and and the Pivot Table was refreshed.
Why do we have to do this?
In the original template, the data was stored in a fixed cross tabular data structure which never changed. In this enhanced version, we let the user decide on the fly which dimensions shall go to the rows and columns. Therefore, we do not know in advance, how many rows and columns our data source (i.e. Pivot Table) will have, since this depends on the count of unique entries of the selected dimensions. That’s why we have to change the named range myData by VBA.
All this requires only one new sub (UpdatePivotTable) with a few lines of code:
After initializing the variables (lines 10 to 30), the code
- clears all existing fields in the Pivot Table (lines 50 to 70),
- assigns the current user selection of fields (defined in the named range myPTDefinition, lines 80 to 130),
- refreshes the Pivot Table (line 140),
- changes the range, the name myData refers to (.DataBodyRange, line 150)
- and finally cleans up
Next step is to call this new sub within the main sub UpdateMarimekko before we recreate the Marimekko chart.
Finally we have to make one last change on the worksheet: the chart has to be updated (every time the user changes a combo box. Therefore we assign the macro UpdateMarimekko to all three combo boxes.
Let’s have a look at the result. Here is a Marimekko Chart showing total Shipping Cost by Order Priority in the rows and Ship Mode in the columns:
Now click on e.g. the rows dimension drop down and select Product Category instead of Order Priority. Almost instantaneously the view changes to this:
You see: although the workbook only mimics a real Pivot Chart, it is live and works well.
Although the approach fakes a regular Pivot Chart not too bad and allows for a better and more interactive user experience, it also comes with some disadvantages:
- It is – of course - still a VBA based solution and a lot of people still have concerns about using macro-enabled workbooks. However, if you want to overcome the limitations of native Excel features, there is no way around VBA solutions
- It takes some additional time to set up the workbook, even based on the template provided below
- The lists of dimensions and measures have to be inserted manually on the worksheet [Control]. If the structure of your data changes, e.g. you have more or less columns or the column headers changed, you have to manually update those lists. It would be possible to automate this with some extra coding, but I wanted to keep the code as simple as possible
- Last, but not least: you have to make sure that the size of the color scheme (number of colors) covers the maximum number of unique entries of every dimension. For instance, in the example used in the template, the color scheme defines 10 colors. If your data changes and one of the dimensions would have more than 10 unique entries, you have to expand the named range myColorScheme and define additional colors
It goes without saying, there is some room for improvements. Here are just a few ideas:
- Eliminate or at least minimize the requirement of manual updating (see section The Disadvantages)
- Add an option to filter the dimensions on rows and columns
- Add a drill down option, i.e. allow the user to drill down to a filtered list of the relevant data of one data point by double clicking on a shape (as you can do on an Excel Pivot Table)
- Add a feature to change the sort order of rows and columns
All of this is possible with VBA, of course. Not in this post, however. Maybe some later day. I will put this on my list of ideas for future blog posts.
The Download Link
Here is the workbook for free download: