Contextures

How to Build a Marimekko Chart

How to set up Excel data and create a Marimekko chart manually, or with a time-saving Excel add-in.

Introduction

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:

  • market segments (phone, laptop, desktop and tablet), are represented by the width of the columns.
  • each column shows the company percentages for that segment (Acer, Dell, HP, Lenovo)

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.

marimekko chart 01

No Built-In Marimekko Chart

Using Excel's built-in chart types, you can create a stacked column chart, but all the columns are the same width.

marimekko chart 01

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

marimekko chart 01

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

  • carefully rearrange your data
  • add a list of percentages for the axis
  • build a Stacked Area chart

It will take some time and patience, and you can find the Marimekko chart instructions below.

These steps show how to build the basic chart, and you can add formatting and labels later, to enhance the chart.

How to Make a Marimekko Chart

To build a Marimekko chart, the original data has to be rearranged. In this example, the data is summarized in the table below:

marimekko chart 01

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.

marimekko chart 01

Chart Data Overview

Next, a chart data table will be created. In the chart data table:

  • The data for each segment will be repeated three times. This will create a "steps" effect in the chart, with left, centre, and right points.
  • Between the segment data, rows with zero values will be added. This will create lines down to the 0 point, to visually separate the "steps".
  • A list of cumulative percentages for the segments is created at the left of the data. These values will be used for the X axis, across the bottom the chart.

Start 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.

marimekko chart 17

Add the Segment Data

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

  • Copy the Phone data from cells C9:F9
  • Paste as values in cells I3:L5.
  • Leave a blank row, then repeat those steps for Laptop, Desktop and Tablet.

The completed result is shown below, with colour coding to show the data for each segment.

marimekko chart 17

Add Zero Rows

To complete the main section of chart data, you'll add zeros. These create lines that visually divide the segments.

  • Put zeros in all the blank rows
  • Add anther row of zeros at the end.

marimekko chart 17

Add X Axis Percentages

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.

  1. The Phone segment is 40%, so it should start at 0%, and end at 40%. Type 0 in H3 and type 40 in H5.
  2. marimekko chart 17

  3. The Laptop segment is 30%. It will start at 40%, where Phone ended. To get the ending point, add 30, so Laptop will end at 70%. Type 40 in H3 and type 70 in H5.
  4. Then, add the start and end numbers for Desktop (70 and 90)
  5. Finally, add the numbers for Tablet (90 and 100)

marimekko chart 17

Add Percentages For Zero Rows

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.

marimekko chart 17

Add Percentages For Segment Centre

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:

  • In cell H4, type the formula: =AVERAGE(H3,H5)
  • Then, copy cell H4, and paste into H8, H12, and H16

Here is the chart data with the segment midpoint rows filled in.

marimekko chart 17

Create a Stacked Area Chart

Now that the data has been set up for the Marimekko chart, it's time to build it.

  • Select the chart data and headings -- cells H1:L18
  • On the Excel Ribbon, click the Insert tab, then click the button for Line Charts
  • In the drop down list, go to the 2-D Area section, and click on Stacked Area

marimekko chart 17

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.

marimekko chart 17

Fix the Chart

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:

  • Right-click the X Axis, and click Format Axis
  • Under Axis Option, select Date Axis as the Axis Type
  • For the Major and Minor Units, type 20, and select Days
  • Leave the Format Axis pane open for the next step

marimekko chart 17

  • With the Format Axis pane still open, click on the Y Axis.
  • In the Bounds section, set the Maximum to 100
  • Close the Format Axis pane

marimekko chart 17

The chart changes, to show the Marimekko "steps".

marimekko chart 17

Complete the Marimekko Chart

To complete the chart, follow these steps:

  • Remove the default Chart Title, or change its text.
  • To add a visible outline for the Acer blocks:
    • Click on the Acer series, to select it
    • On the Excel Ribbon, click the Format tab
    • Click Shape Outline, and click a light colour to use for the lines
    • Click the Dell series, then press the F4 key on your keyboard, to repeat the formatting
    • Repeat for the HP and Lenovo series
  • Add text boxes with the Segment Names

Here is a screen shot of the completed chart. With a dark grey border added to the chart shape.

marimekko chart 17

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.

Quick and Painless Marimekko Chart

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.

marimekko chart 17

Then I selected the headings and data (F5:J9), and clicked the Marimekko button on the Ribbon.

marimekko chart 17

In the dialog box, I choose the Series in Columns Orientation, and clicked OK, as shown below.

marimekko chart 17

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!

marimekko chart 17

Get the Peltier Tech Chart Utility

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.

Peltier Tech Charts for Excel 3.0

Download the Workbook

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.

Search Contextures Sites

 

Save Time
With
Excel All-in-One
Chart Utility

 

Peltier Tech Charts for Excel 3.0

 

 

Peltier Tech Charts for Excel 3.0

 

Peltier Tech Charts for Excel 3.0

 

Last updated: November 29, 2016 3:39 PM