This step-by-step guide will show you how to create a Mekko chart in all versions of Excel: 2007, 2010, 2013, 2016, 2019, and Excel 365.
Here are the steps to create a Mekko chart:
- #1: Set up a helper table and add data
- #2: Append the helper table with zeros
- #3: Apply a custom number format
- #4: Calculate and add segment values
- #5: Set up the horizontal axis values
- #6: Calculate midpoints
- #7: Add labels for rows and columns
- #8: Insert a stacked area chart
- #9: Change series chart type for segments
- #10: Add and Format labels
- #11: Hide the line chart
- #12: Format horizontal and vertical axis
- #13: Insert a label to display market share
- #14: Add borders to separate areas
How to create a Mekko (Marimekko) Chart in Excel?
The bad news is that Excel does not have native mekko chart support. If you want to create a mekko chart in a single click to save your time, use our add-in.
Here is our initial data set. We will analyze four products and four quarters. Here is the market share for each quarter:
#1: Set up a helper table and add data
The first step is to create a simple structure (helper table) in range I1:L17. Then, make the same header for quarters and copy the data to the destination cells.
#2: Append the helper table with zeros
Insert “0%” below each group.
#3: Apply a custom number format
Insert a new row and apply a custom number format.
Select the range, right-click and choose ‘Custom’. Enter: 0″%”, so we’ll get the proper number format.
#4: Calculate and add segment values
To calculate the relative market share, apply the following formulas in the new column:
- =SUM($B$2:$B$2)*100 for H5 and H7
- =SUM($B$2:$B$3)*100 for H9 and H11
- =SUM($B$2:$B$4)*100 for H13 and H15
Enter 0% in cell H3 and add 100% to cell H17.
#5: Set up the horizontal axis values
To separate the columns of the Mekko chart, copy the data using the structure below:
#6: Calculate midpoints
To calculate the midpoints for each group, use the following formula in cell H4:
Copy the formula down and fill the empty cells in the selected column using this logic.
#7: Add labels for rows and columns
To create labels for all groups, we need to concatenate two cells.
The formula in cell N4:
=A2&TEXT(B2, ” (#%)”)
After that, locate the midpoint rows and copy the market share labels into the range O:R.
#8: Insert a stacked area chart
To insert a chart, select range H1:N18, then go to the Insert Tab on the ribbon.
Insert a stacked area chart from the 2D area group.
#9: Change series chart type for segments
Select the Label Marker series, right-click and choose ‘Change Series Chart Type…’
The ‘Change Chart Type’ dialog box will appear. From the series names, select the Label Marker and change the chart type to ‘Line with Markers’.
#10: Add and Format labels
First, select the “Label Marker” series. Right-click on the blue marker and select “Add Data Labels”.
Now we have the labels. First, select labels, then click “Format Data Labels”.
Here are the steps to prepare the labels:
Locate the Label Options tab on the right pane and ensure that the “Value From Cells” box is checked. Next, click on the “Select Range” button; a small window will appear.
Highlight cells that contain labels and click OK. Check the “Label Options Group” and leave the “Value” box empty. Finally, set the “Label Position” to “Above”.
Now we have the arranged labels for each product.
#11: Hide the line chart
The next important step is to hide the line chart. Please don’t delete it!
Select the line chart and use the “Format Data Series” option. Then, under the “Line” section, select “No line”. Now jump to the Marker group and select “None”.
#12: Format horizontal and vertical axis
We have just a few minor modifications, and our Mekko chart will be ready. First, modify the horizontal axis!
Select the horizontal labels and look at the right side pane. Under the “Axis Type” group, select the Date axis. Replace the default values for “Units”. In this case, enter 10.
#13: Insert a label to display market share
To add labels for all segments, repeat the steps in section #10.
#14: Add borders to separate areas
We’ll split the segments and finalize the mekko chart for better readability.
Select the section that you want to split and right-click on it. A floating window will appear; select the outline color and choose the line width.
Now, the mekko chart is ready!
Mekko chart Add-in
If you are in a hurry and want to skip the steps above, we recommend using our chart utility.
Here is our Mekko chart example. The procedure can take only 1 second using UDT.
Download Excel Mekko Chart template