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.
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.
Marimekko Charts in Peltier Tech Charts for Excel
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, is prone to error, and becomes tedious.
I have created Peltier Tech Charts for Excel 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 thousands of machines in a wide variety of configurations, Windows and Mac, which saves time and aggravation.
Please visit the Peltier Tech Charts for Excel page for more information.