In this article, we will show you a step-by-step procedure to create a Mekko or Marimekko chart in Excel.
We will take an imaginary dataset. Then, we will create a Helper Table from the values of our dataset. After that, we will insert the helper table values in a Stacked Area chart. We will modify the chart in different steps and create a final Mekko chart.
Overall, you will get a good idea of creating a Mekko chart from scratch.
Download Practice Workbook
You can download and practice this workbook.
How to Create Mekko/Marimekko Chart in Excel: Step-by-Step Procedures
We will show you a step-by-step procedure for creating a Mekko chart in Excel. To generate the Mekko chart, we have taken the following dataset of different Markets and corresponding Company shares. There are 4 companies Co. 1, Co. 2, Co. 3, and Co. 4. These companies have different shares in 4 different markets. We will use this data set to create a Mekko chart.
Step-01: Create Helper Table
To create the Mekko chart, we need to create a helper table so that we can input data into a chart and make a Mekko chart.
- The blank column just before Co. 1 will be later filled with Horizontal Axis Values.
- First, leave the first row (Row 5) within the Helper Table blank.
- Then, copy the Market A share of 4 companies (D5:G5) and paste it 3 times in the table as shown in the following image.
- Again, leave a row blank and copy all the Company Shares of Market B and Paste it 3 times.
- And repeat the procedure for Market C and Market D.
- The following image shows how it should look.
- Then, we have to fill the buffer blank rows.
- Fill 0% in each of these blank cells.
Step-02: Find Horizontal Axis Values for Buffer Rows
- In this section, we will insert the Horizontal axis values in the left blank column.
- We need to set a custom format for this column.
- First, select the column area as shown in the following image.
- Then use the keyboard shortcut ‘Ctrl + 1’.
- By doing so, the Format Cells window will open.
- From the Number tab, select Custom Category.
- Then set the custom format to
0“%”
. - And press the OK button.
- Now, we will set the upper and lower Axis values for each Market.
- The share of Market A is 20%. So, the upper and lower values of Market A will be 20% and 0%.
- The share of Market B is 35%.
- So, the upper and lower values for Market B will be 55% and 20%.
- Similarly, you can fill in the upper and lower values of Market C and Market D.
- Then, we have to fill the axis values adjacent to the buffer rows.
- The value in these cells will be the same as the previous and the following axis values.
- Now, let’s find the midpoint values of each market.
- Write the following formula in I7 to find the midpoint for Market A.
=(I6+I8)/2
- This will give you the first midpoint which is 10%.
- Similarly, you can find midpoints for other markets.
Step-03: Add Label Marker and Label
- Now, we need to add Marker and Label in our helper table.
- In the Marker column, fill 100% in the midpoint rows.
- Then add another blank column Label.
- You can write the Labels manually.
- Or, you can use the following formula in O7 to get the Label.
=B5&TEXT(C5,"(#%)")
- Then, you can modify the cell references to add Labels for other midpoints.
- After that, we have to add the company shares as shown in the following image.
- Now, we can use this table to create a Mekko chart.
Step-04: Insert Stacked Area Chart
- To create a Mekko chart, we have to insert the data range I4:N21 in a Stacked Area Chart.
- First, select the data range I4:N21.
- Then, go to the Insert tab.
- And, from the Insert Line or Area Chart group, select the Stacked Area chart.
- The stacked area chart should look like this one.
Step-05: Change Chart Type of Marker Series
- Now, we have to modify the Stacked Area chart.
- First, select any of the upper triangle sections.
- Then Right-click on your mouse.
- Select Change Series Chart Type.
- A new window named Change Chart Type will appear.
- Change the chart type of the Marker series to Line with Markers and press the OK button.
Step-06: Add and Format Data Labels
- Now, we want to add data labels.
- Right-click on the Marker series.
- Then, select Add Data Labels.
- You can see all the data labels of the Marker column.
- Now, Right-click on any of the Data Labels.
- Then, select Format Data Labels.
- By doing so, the Format Data Labels window will open.
- From the Label Options, select Value from Cells.
- A new window named Data Label Range will appear where you have to select the Label range.
- So, select the data range O5:O21 and press OK.
- Then, uncheck the Value and set Label Position to Above.
- And you can see the data labels are aligned above the chart with proper labels.
- Now, we can hide the Marker lines.
- Select the Marker series and Right-click.
- Then, select Format Data Series.
- From the Fill & Line section select No line.
- Then, go to the Marker section.
- Select None from the Marker Options.
Step-07: Format Horizontal and Vertical Axes
- You can see, there are no Marker lines anymore.
- Now, we have to modify the Horizontal axis.
- Right-click on the Horizontal axis.
- And select Format Axis.
- Now, go to Axis Options and select Date Axis.
- As soon as you select the Date Axis, the chart will change into a Mekko shape.
- Then, you should change the Major and Minor Units to 10.
- Now, we need to format the Vertical axis.
- So, Right-click on the Vertical axis and select Format Axis.
- Then, from the Axis Options, set the Maximum as 1.
Step-08: Insert Chart Data Labels
- Now, we will add data labels to this Mekko chart.
- All the different colors represent series for different companies.
- Right-click on the yellow color series (Co. 1).
- And select Add Data Labels.
- This will show all the data labels for Co. 1.
- Now, Right-Click on any of these data labels.
- Then, select Format Data Labels.
- By doing so, the Format Data Labels window will open.
- From the Label Options, select Value from Cells.
- A new window named Data Label Range will appear where you have to select the Label range.
- So, select the data range S5:S21 and press OK.
- Then, uncheck the Value.
- You can see the data labels for Co. 1 are shown within the yellow chart area.
- Similarly, you can add the data labels for all other companies and it will look like the following image.
- To make this chart more beautiful, you can add Outline to this chart.
- Right-click on the chart area.
- Then select Outline.
- Choose a suitable outline color.
- And choose a preferable outline Weight.
- Then, press the OK button.
Final Output
Finally, after adding the outline, we got our final Mekko Chart.
Things to Remember
- Make sure to create a proper helper table. The values and formatting in the helper table are important. You may find difficulties if the table values are not taken properly.
- Keep a backup file of your original dataset before starting the helper table.
- You can fill in different colors and make other changes in your Mekko/Marimekko chart in Excel.
Frequently Asked Questions
1. What types of data are suitable for Mekko charts?
Mekko charts are useful for visualizing data distribution between two categories. Typically, market shares and market sales are represented in a Mekko chart.
2. What is the purpose of a Mekko chart?
The purpose of the Mekko chart is to show categorical data. It comprises of different stacked bars with varying widths. It is also known as a mosaic plot. This chart type is important for showing data distribution for different categories.
3. What is the meaning of Marimekko?
Mermekko is a famous dress known for its beautiful color combination and aestheticism. It literary means “Mary-Dress”. This dress was founded by a Finnish couple back in 1951. The chart got its name from the semblance of the “Marimekko” dress.
Conclusion
Thank you for reaching this far. We have shown you the way to create a Mekko chart in Excel. We hope the content of this article has been useful to you. If there are further queries or suggestions, you can mention them in the comment box. Have a nice day!