A VBA based Microsoft Excel Template to create a Marimekko Chart aka Matrix Chart aka Mosaic Chart for free download
A Marimekko or Matrix or Mosaic Chart (called Marimekko hereafter) is a combination of a 100% stacked column chart and a 100% stacked bar chart combined in one view. It works like a 100% stacked column chart, but additionally the width of a column is proportional to the total value of this column.
Microsoft Excel does not provide a built-in chart type for Marimekko charts, but there are several workarounds available to accomplish this. For instance, Jon Peltier shows in his article Marimekko Charts how to turn a combination of a stacked area and a line chart into a Marimekko chart. My friend Chandoo, Conditional Formatting aficionado he is, uses the cell grid, formulas and cell value based formatting rules to create a pseudo Marimekko chart in his post Market Segmentation Charts using Conditional Formatting. Of course there are also a couple of other blog posts on this topic and also commercial Add-ins available.
Although Jon’s and Chandoo’s solutions work well and are available for a long time already, I decided to add my 2 cents with another approach: a VBA based solution creating a Marimekko chart made up of freeform text boxes. The main advantage: reduced set-up time and more flexibility, if the number of rows and columns of the Marimekko chart changes.
Today’s article provides a VBA based solution to create a Marimekko chart in Microsoft Excel and explains how to use and customize the template to suit your requirements. As always, the workbook is available for free download and the VBA code is without password protection.
The Approach
Instead of tweaking a built-in Excel chart type like Jon or using the cell grid and Conditional Formatting like Chandoo, this template will use VBA to create a Marimekko chart made of freeform shapes (text boxes). So, it isn’t a real Excel chart: it rather is a group of textboxes looking just looking like a chart. This is exactly the same basic idea and approach my friend Fabrice Rimlinger uses for his great Sparklines for Excel.
In a nutshell, the code to create the Marimekko chart
- imports the data into a VBA array,
- calculates totals and percentages,
- adds text boxes (freeform shapes) to the worksheet,
- changes the size of the textboxes based on the single and total values and the size of defined range where the chart shall be displayed,
- moves the textboxes to the right position
- changes the text in the textboxes,
- formats the textboxes
- and finally inserts row and column headers, row and column totals and a grand total
Before it does all that, it destroys what’s there already. In other words, every time the chart is updated, the algorithm deletes everything and recreates the whole thing from scratch.
Well, this sounds as if the approach would come with some serious performance problems. However, the performance is actually still acceptable from my point of view, even for an interactive dashboard. The code needs ~0.4 seconds to create a 10 rows / 10 columns Marimekko chart. For 25 rows / 25 columns, the algorithm needs ~1.3 seconds. As I said: still acceptable, I think.
Truth be told, a 100 times 100 matrix takes almost 31 seconds, but honestly: Do you think you will still see something in a Marimekko Chart with 10,000 data points?
The Template
The Microsoft Excel template (download link see below) for creating a Marimekko chart is VBA based, but it is a stand-alone workbook, i.e. you do not have to install an add-in or anything else. All you need is the Excel workbook and to enable the execution of the macros in your security settings or when opening the workbook.
The template consists of three worksheets:
Marimekko
The display of the chart (which is in fact not a real Excel chart):
Data
The underlying data, i.e. a crosstab with row and column headers and positive numbers in the value area, similar to a simple Pivot Table: ![Worksheet [Data]](http://www.clearlyandsimply.com/images/6a00e554d9fb99883301a73dc666cb970d.png "Worksheet [Data]")
Control
The definition of the color scheme used to color encode the chart:
![Worksheet [Control]](http://www.clearlyandsimply.com/images/6a00e554d9fb99883301a3fd0b727a970b.png "Worksheet [Control]")
How to use this Template?
You have two options:
Option 1: Use it as it is
Use the workbook as it is and simply paste in your own data into the crosstab on the worksheet [Data]. The chart will be updated every time you activate the sheet [Marimekko].
Option 2: Transfer it to your own workbook
If you want to create a Marimekko chart in one of your own Excel workbooks, you have to transfer the VBA code and to define the required named ranges. The following steps are necessary:
- Open your existing workbook and the Marimekko template
- Go to the VBE and click on the module modMarimekko of the template and drag and drop it to your own workbook.
- Copy the code from the worksheet object Marimekko and paste it into to the sheet object of your workbook where you want to have the chart displayed. This way, the chart will be updated every time you activate this sheet
- Define the required named ranges in your workbook:
- myData: this named range covers the crosstab with the data to be displayed, but without row and column headers. Row and column headers have to be adjacent left and above to the data
- myChartArea: this is the cell range where the chart will be displayed. The VBA code requires myChartArea to have at least 3 rows and at least 3 columns. The range can cover as many rows and columns as you like, but at least 3
- myColorScheme: this is the range where fill color and font color of the Marimekko chart are defined. Select a range in your workbook, set the fill and font colors of the cells as you like and assign the name myColorScheme to this range. myColorScheme needs to have at least as many cells (colors) as you have rows in your data (one color for each row)
- If not done already, save your workbook as a Macro-enabled Excel workbook
How to customize the Marimekko Chart
The options to customize the Marimekko Chart directly on the worksheets are limited, but the most important things are covered:
- If you need less data than the template uses (10 rows, 10 columns), simply delete the rows and/or columns you do not need on the sheet [Data]
- If you need more rows and / or columns, simply insert additional rows and / or columns somewhere in the middle of the named range myData (to make sure the named range covers all of your data)
- If you inserted more rows, you also have to expand the range myColorScheme and to define more colors (at least as many colors as you have rows in your data, see also the previous section)
- If you want to use different fill and font colors, simply change the fill and font colors of the cells of the range myColorScheme
- The size of the chart is defined by the size of myChartArea. Adjust the row height and column width if you need a smaller or larger chart
- The width of the first column and the height of the first row of myChartArea define the width and height of the row and column headers. Simply adjust the column width and row height to meet your requirements
- The same applies for the last row and last column of myChartArea. Their height respectively width define the sizes of the column and row totals. Please note that the totals consist of 2 numbers: the sums and the percentages. Thus, the last row should be high enough and the last column should be wide enough to provide enough real estate for those 2 numbers
This is all you can customize directly on the worksheet(s), but you have a few more options in the VBA code:
At the top of the sub CreateMarimekko (lines 26 to 30), you will find a few constants:
- default font type
- default font color
- default font size
- default fill color of header shapes
- default margins of the rectangles
You can easily change e.g. the font type or the fill color of the headers here.
Furthermore, you can pass an optional parameter to the sub called lngColDivider. This parameter defines the space between the row header / the row totals and the chart area in pixels. If you want to change this, change this parameter where CreateMarimekko is called (sub UpdateMarimekko at the end of the module).
That’s it.
I guess you could wish for many more options to customize the chart, but I tried to keep it as simple as possible and I think the template fulfills the most basic requirements.
The Download Link
Here is the Marimekko chart template workbook for free download:
Download Marimekko Template (Microsoft Excel 2007-2013, 46K)
Should Marimekko Charts be used at all?
Marimekko charts have a lot of shortcomings, no doubt about it. I will go without repeating all that already has been said on the disadvantages of Marimekko charts. Have a look at Stephen Few’s article on Marimekko Charts, for instance. Not much to add, I think.
I have to admit, I do not use Marimekko charts very often, but depending on the situation and the data to be visualized, they may be a viable alternative from time to time. They are definitely not in my day-to-day chart toolbox, but I also wouldn’t discard them as a matter of principle.
What’s Next?
In the introduction I mentioned the flexibility of this approach. In the next article we will take advantage of this flexibility and create an interactive “Pivot Marimekko Chart”.
Stay tuned.
Leave a Reply to Laura Cancel reply