How to set up Excel data and create a Marimekko chart manually, or with a time-saving Excel add-in.
With a Marimekko chart, the column widths show one set of percentages, and the column stacks show another set of percentages. In the example shown below:
NOTE: This is fake data, with no relation to real sales figures. This simplified example is based on a complex tutorial on the Peltier Tech website.
Using Excel's built-in chart types, you can create a stacked column chart, but all the columns are the same width.
Or you can create a stacked area chart, but it doesn't show the segment percentages, just the company percentages. Also, the areas are all in one, with no vertical dividers for the segments
Excel has added a few new chart types recently, but it still doesn't have a Marimekko chart type. However, you can create your own version, by doing the following (detailed steps are in the next section):
These steps show how to build the basic chart, and you can add formatting and labels later, to enhance the chart.
To build a Marimekko chart, the original data has to be rearranged. In this example, the data is summarized in the table below:
For this Marimekko technique, the data must be in whole numbers, instead of percentages.
To convert the numbers, create another table, below the original table. In the new table, create formulas that link to the percentages, and multiply each value by 100.
The values in the new table will be used to build the chart data.
Next, a chart data table will be created. In the chart data table:
To create headings for the chart data, copy the company names into cells I1:L1
In the next row, type a zero in each column -- that is the point where each column should start.
To create the segment blocks, the data for each segment is added to the chart data table 3 times. (NOTE: The centre points are not used in this example, but can be used to add segment labels later.)
The completed result is shown below, with colour coding to show the data for each segment.
To complete the main section of chart data, you'll add zeros. These create lines that visually divide the segments.
Next, a column of numbers is added, to the left of the chart data, to show where each segment should begin and end.
These numbers are based on the percentage for each segment. You'll have to do some easy arithmetic while you enter these values -- each percentage is added to the previous total percentage.
The zero rows are in the data to create lines at the end of each segment. These rows should be at the same percentage marker as the rows before and after them.
So, to fill in the percentages for the zero rows, type in the same value that in the segment row above and/or below.
Here is the chart data with the zero rows filled in.
The last thing to do is calculate the midpoint for each set of segment data. For example, the Phone segment goes from 0 to 40, so 20 would be at the centre of that data block.
These values can be used later, to add segment headings to the chart.
To calculate the midpoint for Phone:
Here is the chart data with the segment midpoint rows filled in.
Now that the data has been set up for the Marimekko chart, it's time to build it.
A Stacked Area chart is added to the middle of the worksheet. It doesn't look quite right, but you'll fix that in the next section.
Move and resize the chart, if necessary, so it doesn't cover your data.
The chart currently shows flat-topped triangles, instead of the "steps" that we want for the Marimekko chart. The repeated percentages are showing on the X Axis, because those numbers are being treated as text.
To fix the chart:
The chart changes, to show the Marimekko "steps".
To complete the chart, follow these steps:
Here is a screen shot of the completed chart. With a dark grey border added to the chart shape.
NOTE: Instead of text boxes, you can add a list of label points and label text, at the right side of the table. Then, add that series as a line chart, and use Rob Bovey's free Chart Labeler add-in to label the points. See the example in the sample workbook, that you can download below. For detailed instructions, go the Peltier Tech website, and search for the Marimekko instructions.
If you don't have the time or patience to arrange your data and create your own Excel Marimekko chart, Excel charting guru, Jon Peltier, offers a fantastic Excel Chart Utility. You can relax, while the chart utility does all the work.
Reasonably priced, the Peltier Tech chart add-in will quickly pay for itself, because you won't have to waste your time fiddling with data arrangements and chart settings.
This video shows the steps, and the written instructions are below the video.
For this demo chart, I created a pivot table from the sales data, and sorted the segments by Grand Total.
Then I selected the headings and data (F5:J9), and clicked the Marimekko button on the Ribbon.
In the dialog box, I choose the Series in Columns Orientation, and clicked OK, as shown below.
Instead of a long, painful process, the Marimekko chart is created in a few seconds. A new sheet is inserted in your workbook, with data linked to your original data, and completed Excel Marimekko Chart.
You can adjust the label text, and other options, by using the yellow cells and check boxes on the worksheet. Then, move the chart to a different sheet, for your final report.
Here's the completed Excel Marimekko chart, showing the segment percentages across the X axis, and company percentages within each segment. This one took a couple of seconds to complete -- much faster than building it manually!
If you need to make a Marimekko chart, or other custom Excel charts, go to the Peltier Tech website, and check out the features in the Peltier Tech Chart Utility.
Instead of copying and pasting data, and fussing with the chart formatting, just click a button to create a complex chart. You'll save time and reduce frustration, and your boss and clients will be impressed by how quickly and easily you can create complex Excel charts.
There are several other custom chart types in this All-in-One Chart Tool, including Waterfall, Stacked Waterfall, Cluster Stack, Histogram, Box Plot, Dot Plot, and Pareto.
The tool also includes a Loess data tool, and several other chart management and formatting tools, like Export Chart, and Label Last Point.
To see how the Marimekko chart is created, download the sample workbook. The zipped file is in xlsx format, and does not contain macros.
Note: These are affiliate links, and I'll earn a small commission if you purchase the cluster stack chart utility through these links.
Last updated: June 21, 2017 4:26 PM