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

**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) select the chart and go to the Chart menu > Chart Options > Axes tab. The Primary Category axis is listed as Automatic. Change this setting to Time-Scale.

In New Excel (2007 and later) select the axis and press Ctrl+1 (numeral one) to open the Format Axis dialog. Under Axis type, change Automatically Select to Date Axis.

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.

## Peltier Tech Chart Utility

This tutorial shows how to create Marimekko Charts, including the specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes time, can be prone to error, and becomes tedious.

I have created the **Peltier Tech Chart Utility** to create Marimekko Charts (and many other custom charts) automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types. This is a commercial product, tested on hundreds of machines in a wide variety of configurations, which saves time and aggravation.

Please visit the Peltier Tech Chart Utility page for more information.

The “marimekko” chart is also known as the mosaic or the eikosogram.

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

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.

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?

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 :-)

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

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.And … presto ! Thank you John for your fantastic insights !

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.

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.

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.

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.

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

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.

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

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:

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.

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

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.

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

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:

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!

Oh no! Now I have to write it!

=)

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?? =)

Works for me in 2007/SP2. Your description is not sufficiently detailed to tell me where you got stuck.

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

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?

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.

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?

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.

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.

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.

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)

Lot of thanks for this great description how it works! I struggled with this, but your solution helped me a lot!

One comment for adding labels: I draw the “LabelY” on the secondary axis. So I can set the description for this axis to the “Labels”-Column (edit datasource for this row). After activating “Category” in the datadescription tab for the “LabelY”-serie, the text in column “Labels” is displayed.

Jon, thanks for the excellent write-up. I was able to use your instructions to create a fairly comprehensive (though not robust in terms of error management) macro within Excel. I would be happy to share my coding with you, if you would like to see/have it, as it only seems fair. I’m not a developer, so my code might be rather crude to you. Either way, thanks and let me know if you want my macro.

Andy –

I’ve already developed a macro into a commercial product http://peltiertech.com/Utility20/PeltierTechUtility.html), but thanks for your offer.

Hi Jon,

I’m sure I might just be missing something very basic here, but I managed to get all the way to displaying segment labels using the XY tool but got stuck trying to add labels to the blocks — the new series appear but the scale doesn’t seem to match the X axis even if I haven’t changed or deviated from your instructions at all.

So basically the points align across the Y axis but don’t seem to align on the X axis –making the plots fall a bit squat on the height area.

Any ideas on why this would happen?

Thanks!

sorry, ignore earlier comment :) finally figured this out. Thanks for the uber helpful post!

regarding the alternative method of producing the graph and your comments:

http://www.ultimatepres.com/create-marimekko-chart/info_13.html

“so they don’t have vertical lines between adjacent segments:”…

to get the vertical separators:

Format Data Series>Options> Drop Lines (with tick) (you can then format the drop lines to your satisfaction)

Lyndon –

I had not thought of using droplines to create the vertical lines. Nice suggestion.

Now there is a choice between needing more rows of data (to force the area chart outlines to reach to zero) or more elements in the chart (the drop lines).

Jon,

thank you for this posting, it was both useful and interesting. I have a question regarding the section that begins with “To add labels to the blocks in the chart”, I constructed the data in columns as shown, pasted them as new series, then deleted the rows of “0%”. however The markers at the 0% locations remain, so when i add the data labels they appear both at the center of each box and also at the lower left and lower right corners of the box at 0%. I am using Excel 2007. Any suggestions?

Jon,

I am trying to create a chart that is essentially a stacked bar chart along the x-axis, while at the same time a clustered collumn chart with respect to the Y-axis.

The aim is to create a visual representation of possible improvement projects, mapping their contribution to a total figure (tons of CO2) horizontally, and also showing the individual net cost (positive or negative) in dollars.

Could you please tell me if this possible? and if so, how?

Alistair –

Perhaps you want to use a Cascade Chart? I wrote a tutorial in Variable Width Column Charts (Cascade Charts).

Thank you so much for this really useful explanation

I am able to recreate your chart and creat my own with up to about 6 vertical blocks on the x axis. However, when I increase this in number – say to 9 – one of my vertical blocks is no longer the correct width (the one third from last). Any explanation for this?

Thank you

Kate –

Sounds like a calculation error, as if the formulas aren’t extended as far as they should be. Hard to tell without seeing the data.

Hi – is there a way of creating a Marimekko with horizontal segments rather than vertical segments (as above) or is it limited to vertical segments because of using an area chart?

The only workaround I’ve found is to rotate text labels and then copy/paste a picture of the chart to allow me to rotate it horizontally. While this works, it is a pain to update!

Any suggestions would be greatly appreciated!

Matt –

I’ve made Mekkos with horizontal stacks. It’s much trickier using area charts, and if the blocks don’t move systematically from stack to stack, the whole thing falls apart.

However, you could skip the area charts and use stacked bar charts, using enough bars across the width/height of the chart to provide adequate resolution (100? 1000?), where the width of a stack is controlled by using more or fewer bars to build that stack.

In this example, I started with a bar chart, then used a very small gap width to show how it loos at first like a mekko with equal widths.

Then I expanded the data, with number of rows per category that is proportional to the width value. This example was easy, I only needed ten bars to provide accurate widths, but in general you will need lots of rows, with formulas that help you figure out how to apportion the data across your table. (Do I smell a blog post?)

Finally I hid the default axis labels, and added some hidden XY points to anchor the desired category labels, and another set to anchor horizontal error bars that separate the categories.

Dear Jon,

thanks for this great article!

I’m currently trying to follow the guide step-by-step, but unfortunately I’m using Excel 2010.

I’m stuck at this part:

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

Actually, I just can’t find this option in Excel 2010. Do you happen to know where I can find the option?

Or is there a new guide for 2010 already?

Looking forward to hearing from you soon!

Regards,

Rebecca

Rebecca –

I’ve added the 2007-2010 instructions to the article.

Jon-

Thanks for the guidance and inspiration (and the Chart Labeler macro). I found the macro-driven approaches a little limiting for my needs, so I built a general template for doing Marimekko charts that doesn’t involve any macros. I uploaded it at http://www.megaexcel.com/

This is very helpful! But I can’t get the x-axis widths correct. The mekko chart is here: http://tinypic.com/r/2e3cy2f/8

The width between 4 and 7 (difference of 3), for example, is the same width as between 7 and 9 (difference of 2). I’ve made sure that all the formulae and formats for the x-axis are correct, so I’m not sure what else could be the issue.

Emilie –

Could you post a screenshot of the data? I can see that some things are not right, but I can’t really tell what and why.

It might be helpful at first to build the chart with only a handful of vertical stacks. It’s smaller, so a little easier to get a handle on it. Then when it’s working, make another chart with the larger data set.

Thanks so much for responding! I have tried creating the chart with less data, but it didn’t solve the problem. I’ve pasted the data in this link: http://tinypic.com/r/2zflb9i/8.

-Emilie

Emilie –

The problem is that the date axis works in whole numbers only. Multiply the values in the X column by 1000. If you want the labels to look like percentages, use a custom number format of 0,”%” or 0.0,”%”

Great approach Jon! I love it.

Any chance this can be adapted to show a root-cause analysis two levels deep?

Main root cause A, B, C, D on the x-axis, Secondary root causes on the y-axis: A1, A2, A3, B1, B2, B3, B4, C1, C2, C3 e.g. Both primary root causes A-C and secondary root causes 1-3 or 1-4 adding up to 100%.

Difficulty is in the labeling of the area’s as secondary root cause 1-3 can be different for A, B and C.

Any chance you can pull something out of your sleeve other than adding labels manually. I would prefer to work with cell references.

Regards,

Twan

Twan –

You could easily do this. You may need to adjust the way the formulas calculate the box sizes (or not, I haven’t thought about it). To get different colors for the different secondary causes, just put each into a separate column, so each is plotted in a separate series and can be formatted differently.

I liked all the ways that the data was displayed totally awesome. I am looking for a software tool, and have been looking for month,, for software to be able to produce a wall chart that is similar to this link:

http://en.wikipedia.org/wiki/File:United_States_Frequency_Allocations_Chart_2011_-_The_Radio_Spectrum.pdf

The different lines of course cover a wider range of frequencies so what I would like to do is to produce a chart by using each line as a different graphic and then pasting these into a graphics tool and printing the wall chart after getting all the charts lined up.

Please help in my search

I can call you to explain further through Skype. I work for the US Army as a spectrum manager and want to produce a chart like this for our local area.

Thanks

Kenneth Johnson

Dear Jon,

I just have an easy problem, would you be so kind as to help me? I use Excel 2013, and if I type 5″%” the cell shows exactly this instead of 5% (as number formatted). How can I fix this problem? I have tried ‘ instead of “.

Thank you for your response in advance.

Tamás

You just want numbers in the cell. The number format has to be 0″%”.