Marimekko Charts
by Jon Peltier
Tuesday, February 17th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In a recent tutorial I showed how to make Variable Width Column Charts. A stacked version of this chart type, called a Marimekko chart, is popular in business, particularly where marketing departments analyze segmentation of their markets. The Marimekko chart gets its name from the patterns found in Marimekko fabric.
Marimekko Chart Utility
I’ve received a lot of questions and comments about Marimekko charts, through comments here and via email. To address this attention, I’ve created a custom Excel utility to create Marimekko charts from simple data tables. You can read about it in PTS Marimekko Chart Utility.
Marimekko Chart
Laying Out the Data
Suppose we have four companies, Alpha through Delta, competing in four segments, A through D, of a particular market. This could be like the companies Dell, HP, and Toshiba competing in the laptop, desktop, and home server segments of the personal computer market. A summary of the data would look like this table. The sum of the segment percentages is 100%, and the market segment shares of the companies in each segment (each row) also sum to 100%.
Like most non-standard charts, we have to arrange the chart source data in a special pattern. Here is how the above data is rearranged. The first column, the X values, comprise a cumulative percentage of the segment percentages: 0, 40, 70 (40+30), 90 (40+30+20), and 100 (40+30+20+10). Each of these values is listed three times. In between each of these values, I’ve inserted another row with the average of the values above and below, with orange text. These orange values correspond to dummy data which will provide labels later in this protocol.
Note that the X values look like percentages, but are not. A date-scale axis, which we will use to spread out the widths of the area chart data, cannot render fractional values. These X values are whole numbers, and I’ve used a number format of 0"%" with the percentage symbol in quotation marks to place it as generic text after the whole number.
The Y values for each series and each segment are repeated three times, at the beginning, middle, and ending X values for this segment. For example, Alpha has 60% for Seg A in rows 3 through 5, next to X values of 0%, 20%, and 40%. The Y values return to zero at the end of each segment. Alpha shows 0% in rows 2, 6, 10, etc.
The column labeled “dummy” holds Y values for a dummy series that will provide the labels in the column marked “Labels”. I’ve colored this text orange to indicate labeling helper data, corresponding with the inserted orange rows above.
Creating the Chart
Select the data in columns H through M and create a stacked area chart.
Convert series LabelsY into a line chart series.
If you clear the zero values in the LabelY data, column M, the points and connecting lines will not clutter up the chart.
Clear the legend to add more room for the chart.
Now the X axis needs to be adjusted. All the X values in column H are treated as text labels, equally spaced without regard to their numerical value. In Classic Excel (2003 and earlier) go to the Chart menu > Chart Options > Axes tab.
The Primary Category axis is listed as Automatic. Change this setting to Time-Scale.
The result is that all Y values for equal X values are vertically aligned, as if in an XY chart. Of course, Excel has helpfully converted the values into dates, spaced one week apart.
Format the X axis scale so it runs from 0 to 100 in steps of 20 (not percent but whole numbers). Apply a number format of 0″%”, which places the percent sign in quotes so Excel adds it as text to the end of the whole number rather than converting the value to a percentage.
Add Labels to the Marimekko Chart
To add segment labels, I use Rob Bovey’s free Chart Labeler utility. This utility adds a menu item called “XY Chart Labels” to the Tools menu. It works on any chart type that accommodates chart labels, not just XY charts.
Using the utility, assign the labels in the Labels column to the LabelY series. The labels here have been positioned above the points.
Format the Labels series so it uses no markers or lines, and these points will no longer appear.
To add labels to the blocks in the chart, you need points which will be located in the center of each block. The X values are the same as above, but the Y values for each series (the names are the same as above, but they are distinct series) are calculated to place a point at the mid-height of each block.
Copy this data, select the chart, and use Paste Special to add the data as new series. Since the last series in the chart, LabelsY, had been converted to a line type, these are added as lines. Notice that the points are located exactly where needed, although there are also points along the category axis and lines everywhere.
Clear the cells with zero values. . .
. . . and only the desired points will remain in the chart.
Format each series so it uses no lines or markers, and so it also displays data labels using the series name. Then format the labels so they are centered on the points (the default is to the left of the points).
This chart has way too many labels. We could get away with one label per series. I’ve removed all Y values except for the ones that put points in the largest block for each series.
This is a much cleaner chart.
Naturally you could tailor the labels to show a dollar value or a percentage, or any other information, but you would need not only a table containing the X and Y values as shown in the table above the last, but also a table containing the values to be used for each label. You would use Rob Bovey’s Chart Labeler to apply the labels, as described for the segment labels along the top of the chart.
Related Posts:
- The Problem with Marimekkos
- Marimekko Replacement – 2 by 2 Panel
- Variable Width Column Charts (Cascade Charts)
- Marimekko Chart Utility
- Marimekko Replacement – Overlapping Bars (Hard)
- Marimekko Replacement – Overlapping Bars (Easy)
- Error Bars in Excel 2007 Charts
- Cascade Chart Utility Joins the Team
- Growth Rates in a Panel Chart
- Callout Labels with XY Line Segments
Posted: Tuesday, February 17th, 2009 under Example Charts.
Comments: 35
Comments
Comment from Hadley
Time: Tuesday, February 17, 2009, 9:25 am
The “marimekko” chart is also known as the mosaic or the eikosogram.
Comment from Scott
Time: Wednesday, February 18, 2009, 4:55 pm
I’m wondering why everytime I pick the time-scale it almost looks like it delete’s the data. In the previous post I got the right outcome, but i’m struggling to replicate today’s.
Scott
Comment from Jerry Betz
Time: Wednesday, February 18, 2009, 5:47 pm
Hi Jon,
I am having trouble with the method outlined here, I end up with a mismatch on the X-Axis starting at the 90% mark and progressing towards the 100% mark.
This Step 1 snapshot shows the series created and the label series converted to a line, with the extraneous zeroes removed (all appears as yours does):
http://picasaweb.google.com/lh/photo/QuZiKYJOLlMfTsuqKeXHpA?authkey=dsoYDUktVq0&feat=directlink
Step 2 makes the change on the X-Axis from Automatic to Time-scale:
http://picasaweb.google.com/lh/photo/ZXryxSdb70tFeUQgX1PBiQ?authkey=dsoYDUktVq0&feat=directlink
In Step 2, everything collapses onto the Y-Axis. I found that by multiplying my X-Axis numbers in Column H by 100, they came back, as shown here in Step 3:
http://picasaweb.google.com/lh/photo/PDJoBB5f6wRRpRPbv82qMA?authkey=dsoYDUktVq0&feat=directlink
In Step 4, I made the changes to the X-Axis number format, and added the labels to the four points at the top using JWalk Chart Tools:
http://picasaweb.google.com/lh/photo/OomAY8Rn7ANxxCeZDjjbxQ?authkey=dsoYDUktVq0&feat=directlink
It is in Step 4 that the 90% X-Axis value doesn’t line up with the 90% data values, and there would appear to be a gap between what the series data shows as 100% and the X-Axis shows as 100%. I’ve tried going over these steps several times and keep getting the same result. In Step 3, the X-Axis labels are the same as yours, as well as having the right-most label “4/7/00″ showing up slightly to the left of the end of the axis.
Thank you for any advice.
Comment from Jon Peltier
Time: Wednesday, February 18, 2009, 5:56 pm
Jerry & Scott -
I suspect you might be having the same issue. Since you can’t render fractions on a time-scale axis, you need to use whole number percentages, and append “%” in the number format to provide the look you need.
Then make sure the axis scales from 0 to 100; 100 should be 4/9/1900. Make sure the upper limit didn’t automatically adjust itself. Is everything on the primary axis?
Comment from Jerry Betz
Time: Wednesday, February 18, 2009, 9:14 pm
Jon,
That was it. I created the table right away, and didn’t use the 0″%” Custom number format until it came time to do so on the X-Axis. I was using numbers formatted as a percentage in column H. In the corrected version shown:
http://picasaweb.google.com/lh/photo/CmPFZfBSPB2hL_a6M40Pwg?authkey=dsoYDUktVq0&feat=directlink
both the X-Axis values in column H and the X-Axis labels are formatted using the 0″%” Custom number format. All other numbers in columns I, J, K, & L are formatted as percentages.
I should have mentioned in the first post that I am using Excel 2003.
Thanks :-)
Comment from Bernard Lebelle
Time: Thursday, February 19, 2009, 4:04 am
Hi John,
Am using Excel 2007 and with the appropriate table, all is fine until I reach the point where I change the X axis to “timescale”.
All I end up with is a big block with the 4 layers and without the width / height variations. The X axis only shows 0% and 100% values. There is no indication that it as converted the X axis labels in date format.
Many thanks for your advices & great blog
Comment from Jon Peltier
Time: Thursday, February 19, 2009, 9:00 am
Bernard -
That’s the same problem that Jerry and Scott experienced before you. I couldn’t imagine so many of my readers, among the smartest people on earth, were having such trouble. But then I went back to the tutorial and discovered that I’d left out a crucial paragraph about using whole numbers, not percentages, as my X values.
Read the paragraph that begins with “Note that the X values look like percentages, but are not.” This should explain how to fix your problem.
Comment from Bernard Lebelle
Time: Thursday, February 19, 2009, 9:34 am
And … presto ! Thank you John for your fantastic insights !
Comment from derek
Time: Thursday, February 19, 2009, 2:39 pm
For accuracy’s sake, I would go further, and make my scale from 0 to 100,000, and use the following number format:
0,”%”
Now the data (suitably multiplied) can be represented with a potential precision of 0.001% on the chart.
Comment from Jon Peltier
Time: Thursday, February 19, 2009, 3:58 pm
Derek -
That’s probably 20 times more precise than pixel spacing allows.
There’s a limit to what the multiplier should be. I built one of these a long time ago which used revenues across the X axis. When the client looked at a larger market, the chart broke down. I had to insert a divisor and remove a comma from the number format. The cut-off seems to be 2,958,465 days.
Comment from derek
Time: Thursday, February 19, 2009, 4:26 pm
That’s probably 20 times more precise than pixel spacing allows.
Yes, but it’s the minimum precision you can arrange for using this technique, short of integer percentages. It’s not as if you’re forced to use it all. I usually use it for tenths of a percent, because it would not be acceptable to display a 3.6% segment next to a 4.4% segment with them both looking the same size.
The cut-off is caused by reaching Dec 31 in the year 9999, which is why I couldn’t have done it with two commas even if I wanted to.
Comment from Jon Peltier
Time: Thursday, February 19, 2009, 4:44 pm
I thought 2,958,465 days was kind of random, but 31 Dec 9999 is logical after all. I was looking for a nearby power of 2.
Comment from Joe
Time: Thursday, February 19, 2009, 9:02 pm
Hi Jon,
Thanks for the Marimekko chart! I have another question for you. Do you have an example of a Ishikawa (fishbone) diagram template? Possibly one that adds lines to the chart automatically as you add different sements? Thanks. – Joe
Comment from Jon Peltier
Time: Thursday, February 19, 2009, 9:05 pm
Joe -
Sorry, I’ve used Ishikawa diagrams, but never tried programming one in Excel. It wouldn’t fit well within the Excel charting system. I think years ago we used Visio for such diagrams. Not automatic, but elegant in the way you could add elements to diagrams.
Comment from Nitin
Time: Thursday, February 26, 2009, 4:07 am
Hi Jon,
In this merimekko charts is it possible to color individual blocks seperately. For Ex: I want to color different color for alpha 60% & alpha 40%.
I can do it using text box. Paste a text box of different color on the required block.
Is there any way out????
Comment from Jon Peltier
Time: Thursday, February 26, 2009, 8:10 am
Nitin -
Each block which is separately colored needs its own area series. The original source data in H1:N18 has to be split out into H1:Z18 like this:
Construction of the chart proceeds as above, with the result like one of the following:
Comment from Jon Peltier
Time: Friday, February 27, 2009, 8:24 am
Maybe the visualization gods will smite me, but on the other hand, I’ve received a lot of questions about Marimekko charts, through comments here and via email.
To address this attention, I’ve decided to offer a custom Excel utility to create Marimekko charts from simple data tables. You can read about it in PTS Marimekko Chart Utility.
Pingback from ggplot2: Marimekko/Mosaic Chart « Learning R
Time: Sunday, March 29, 2009, 2:59 pm
[...] proportion of a product market by region, and proportion of region by product. Jon Peltier’s tutorial demonstrates the technique to create one in Excel. Marimekko chart by Jon [...]
Pingback from Statistical Graphics and more » Blog Archive » Don’t call me “Marimekko”
Time: Friday, May 15, 2009, 4:02 pm
[...] with the greenhouse data here – and not just play around with artificial data as we find here and here. This seems also to be the source of the Marimekko … Comment (RSS) [...]
Comment from Gülsen
Time: Friday, May 29, 2009, 6:56 am
Hello Jon,
Thanks a lot for all the information. It realy helps :)
I created my Mekko successfully, but I can’t create this last table you mentioned, the one thats needed to label the values.
It will be really great if you can also show the formation of that table.
Thanks, Gülsen
Comment from Jon Peltier
Time: Friday, May 29, 2009, 5:51 pm
Hi Gülsen -
The tables can be populated using formulas that compute the midpoints of each rectangle, and add this to the height of the rectangles it is stacked on. I didn’t show the formulas because it was somewhat tedious. In this case, I manually deleted all data except for the points I wanted to show, resulting in the last table.
Comment from Kalle
Time: Monday, July 13, 2009, 2:09 pm
Hi Jon,
First of all, really nice page!
I have a couple of questions, is this applicable in Excel 2007 or is it just for the 2003 version?
Would you be willing to share the xls file so it is a bit easier to test it?
I had a look at this page:
http://www.ultimatepres.com/create-marimekko-chart/info_13.html
I was not able to create the Mekko chart based on that cause the last step are to blurry. Is it possible to use the 100 % area stack chart in the 2007 version? Is that why they have a smaller matrix then you?
Thanks!
Kalle
Comment from Jon Peltier
Time: Monday, July 13, 2009, 3:04 pm
These types of chart, both stacked 100% area and marimekko) can be made in 2007 as well as in 2003. The steps may be slightly different in 2007, and I should address that in a new post.
In the reference you’ve cited, their data array is not as large as mine because they didn’t use enough data to get the chart they showed. Here is my stacked 100% area chart at an intermediate step. Note that the rows of zero values force the areas to drop to the bottom of the chart:
These sloping lines to zero result in vertical lines to zero in the final chart:
The data in the page you cited does not force the areas to drop to the bottom of the chart:
so they don’t have vertical lines between adjacent segments:
Comment from Kalle
Time: Monday, July 13, 2009, 3:17 pm
Thank you Jon for clearing that out, I have been sitting for some hours now trying to figure out how they could achive that chart with that data.
I am looking forward to your new post on the 2007 version!
Comment from Jon Peltier
Time: Monday, July 13, 2009, 3:59 pm
Oh no! Now I have to write it!
Comment from Kalle
Time: Monday, July 13, 2009, 4:37 pm
=)
That would be great cause I got stuck again doing it step by step.. =/
The problem the “time scale” step, either I have formated wrong or the time scale in the 2007 version does not work!
How is it going with that post?? =)
Comment from Jon Peltier
Time: Monday, July 13, 2009, 4:56 pm
Works for me in 2007/SP2. Your description is not sufficiently detailed to tell me where you got stuck.
Comment from Kalle
Time: Tuesday, July 14, 2009, 5:39 am
Hi Jon,
I tried it now in Excel 2003 and it works fine and it was the Time Scale that I got wrong in the 2007 version but that was somehow easier with older version but now I know were to look.
Thanks for a great blog
Kalle
Comment from Kalle
Time: Wednesday, July 15, 2009, 4:05 am
I have now created three charts one for the fiscal year 07, 08 and 09. It consist of 6 countries and each country have 5 groups. The changes over the years has not been major so when I will use this in a PP the audience might have a hard time to see the difference over the years.
My question is if you know any good way of showing what the differnce is between the charts other then just adding the numbers?
Comment from Jon Peltier
Time: Wednesday, July 15, 2009, 7:18 am
One of the shortcomings of Marimekko charts is that they present a picture of a single point in time. Even if the audience had full color glossy reproductions of several charts, comparisons between them would be difficult to make.
This is a hard realization, after so much work, but you should consider other types of charts, or groupings of charts, which effectively show how the various items change with time. For example, line charts (or bar/column charts) with time as the X axis, perhaps in panel chart form. I have some examples in Panel Charts with Different Scales and How to Build a 2×2 Panel Chart.
Comment from Charles
Time: Tuesday, September 15, 2009, 12:42 pm
Hey Jon – thanks for this great site.
I’m having issues with the label-lines in the chart; after following the steps, I remove 0%’s from the table but the lines persist. Effectively, for each series I have one labeling point where Y>0, but for all other points (X>0) it’s recording distinct points on the chart with Y=0…any thoughts on how I remove these unwanted points?
Comment from Jon Peltier
Time: Tuesday, September 15, 2009, 8:32 pm
Charles -
Presumably you are formatting the series so it has no markers and no lines? And you’ve cleared the cells that have the Y values for the labels? If you don’t clear the cells, but use a formula that returns “”, the “” is text not a blank, so Excel treats it as a zero. To keep the formulas, change “” to NA(), which puts the #N/A error in the cell, but doesn’t plot a point.
Comment from James
Time: Thursday, October 1, 2009, 11:12 am
Hi, is there any way to sort the data such that for each company, their largest share of a market appears at the bottom of the chart, so one company might have laptops closest to the x-axis, another company might have servers – it would mean finding a way to decouple the ‘category data’ so it can be individually moved / prioritised for each company . . . . can this be done in Excel or does it need a package of some sort? All best, James.
Comment from Jon Peltier
Time: Thursday, October 1, 2009, 12:13 pm
James -
Arranging blocks by size (not color) is already pretty complicated to handle with column charts, as I described in Stack Columns In Order Of Size and in Stack Columns In Order Of Size With VBA. In the latter article I also noted that this change makes the chart much more difficult to interpret.
Comment from James
Time: Thursday, October 1, 2009, 2:23 pm
Many thanks Jon, I can see I have some more bedtime reading here . . . hope you are well, this is the first blog I have ever had any really useful information from, all best James (Gloucestershire, UK)
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.