Marimekko Chart

Today’s guide will explain how to create a Marimekko Chart in Excel 2007, 2010, 2013, 2016, 2019, and Microsoft 365.

Marimekko Chart: The Basics

The Marimekko chart provides a quick overview of market share and is used in decision preparation. It is also used to create market segmentation maps using easy-to-understand visualization. By looking at the Marimekko chart, you can identify possible opportunities and threats.

Steps to create a Marimekko chart in Excel:

Steps to create Marimekko Chart in Excel

If you are working with Excel, there are some limitations with built-in chart types. For example, the Marimekko chart is completely missing, so you need to prepare the chart manually or use custom add-ins to create a mekko chart quickly.

#1: Prepare data and create a helper table

Before diving deep into the details, we must prepare data and create a helper table. It is not rocket science. Select the B2:G6 range and copy each row using the mentioned steps below:

#2: Append the helper table with zeros

Now append the helper table with dummy data. Enter 0% values in each blank row in the range.

#3: Use custom number format in the helper column

In this step, insert a new column before column J. Afterward, apply a custom number format for all cells in the range. To do that, select the cells that you want to format. Then, right-click on the selection and choose “Format Cells”.

Under the Number Group, select Custom, then enter 0″% “. Finally, click OK to close the window.

#4: Calculate and add segment values

The main characteristic of the Marimekko chart is that the column widths represent the market share. In our example, apply the following calculations to column I.

• I6 and I8 =SUM(\$C\$3:\$C\$3)*100
• I10 and I12 =SUM(\$C\$3:\$C\$4)*100
• I14 and I16 =SUM(\$C\$3:\$C\$5)*100

Type 0% in cell I4 and enter the formula =SUM(\$C\$3:\$C\$6)*100 to cell I18. After the calculations mentioned earlier, our table looks like the below:

#5: Set up the horizontal axis values

To improve the look of the Marimekko chart, we’ll separate its columns. To do that, copy the given values and build the following structure.

#6: Calculate midpoints

We have a few empty cells in column I, which play an important role in label positioning. Use the calculations below to calculate the averages for segments.

For example, to calculate the midpoint for the Region “East”, apply the formula:

=I5 =(I4 + I6)/2

Following this logic, the midpoints are 13%, 45%, 75%, and 93%.

#7: Add labels for rows and columns

To create the labels for the segments, we have to join two cells that contain different data types. Enter the formula in cell O5:

=B3&TEXT(C3, ” (#%)”)

Copy the formula down to create the group labels for other regions.

Create the labels for all quarters, then copy the data from range D3:G3 to D6:G6. Now, our table is ready to create a Marimekko chart!

#8: Insert a stacked area chart

Steps to insert a stacked area chart:

1. Highlight the range I2: O19. The selected range contains the helper column, the quarters, the label markers, and the group labels.
2. Select the Insert Tab on the ribbon
3. Choose the “Insert Line or Area Chart” section
4. Under the 2-D Area Group, select the stacked area chart type

#9: Change series chart type for segments

Select the “Label Marker Series” (in the example, the dark-grey area of the chart). Next, right-click and select the “Change Series Chart Type”.

After clicking “Change Series Chart Type…” a “Change Chart Type” window will appear. We can change the current chart type under the “Choose the chart type and axis for your data series” section.

Select the “Label Marker” row from the Series Names list and change the chart type from stacked area chart to line chart with markers. Click OK.

To create labels, select the data points from the line chart series. Next, right-click to open the context menu, then choose the “Add Data Labels” option.

Select the data labels, right-click, then choose “Format Data Labels..“

Here are the steps to align custom segment data labels:

1. Navigate to the Labels Options Tab
2. Click on the “Values From cells” checkbox
3. The “Data Label Range” window will appear; browse the range
4. Select the range that contains the labels; in the example, range \$O\$3 : \$O\$19
5. Click OK to close the window

Under the “Label Contains” section on the right side pane, remove the selection from the “Value” box. Finally, under the “Label Position” Group, select “Above”.

Result:

#11: Remove the line chart from the chart area

Because all labels are aligned, it’s time to hide the line chart. Select the series, right-click, and choose “Format Data Series”.

Select “No line”, and hide the marker by checking the none box under the Marker options.

#12: Format horizontal axis

To transform the current chart into a Marimekko chart structure, select the horizontal axis and make some changes under the “Axis Options“. Select the “Axis Type” and pick “Date Axis“. Set 10 for major and minor units.

#13: Format vertical axis

Please take a look at the vertical axis and select it. Then, choose “Axis Options”. Then, under the Bounds group, Maximum field, type =1.

#14: Create labels for market share segments

The method is the same as in step 10. Repeat these steps for all Quarters!

We have only one step left: split the Marimekko charts groups.

#15: Split and separate areas using borders

Finally, we’ll apply borders for better readability. Select the group that you want to separate. Then, right-click on it and select the border color from the “Outline” menu. Next, choose the line weight from the “Weight” menu.

You can download the practice file containing the Marimekko Chart Template. We recommend that you follow our guide and modify the chart if necessary.

We provide automated solutions to create Marimekko charts in a single click if you are working with complex charts. Select the range that you want to visualize. The add-in generates the chart ASAP with some additional information. Read more about the chart utility.

Enhancing Analysis with Marimekko Charts: Practical Applications and Insights

Beyond its technical construction in Excel, the Marimekko chart is a powerful analytical tool in business strategy and market analysis. Its unique ability to display market segments and share sizes simultaneously makes it an indispensable part of the data visualization toolkit for business analysts, marketers, and strategic planners.

1. Market Analysis: The Mekko chart allows strategists to visualize market segmentation and the relative sizes of each segment. This visualization aids in identifying high-potential market segments or underserved niches.
2. Strategic Planning: By visualizing the distribution of resources or revenues across different business units or product lines, companies can make informed decisions about where to allocate resources for maximum impact.
3. Competitive Analysis: When comparing your company’s product or service distribution in the market against competitors, the Marimekko chart provides a clear picture of where you stand, highlighting areas of strength and opportunities for improvement.
4. Identifying Growth Opportunities: By analyzing the size and share of market segments, companies can spot emerging trends and allocate R&D investments to capitalize on these opportunities.
5. Resource Optimization: For internal analysis, Marimekko charts can illustrate the distribution of resources (e.g., budget, manpower) across projects or departments, guiding decisions on resource reallocation to improve efficiency and outcomes.
6. Dynamic Data Integration: Consider linking your Marimekko chart with live data sources. This approach ensures your chart updates in real-time, providing ongoing insights into dynamic market conditions.
7. Integration with Dashboards: Embed your Marimekko charts into larger business intelligence dashboards. This integration provides a holistic view of business performance, combining market share insights with other key performance indicators (KPIs) for comprehensive analysis.

Thanks for being with us today. We’ll continue our data visualization adventures soon.