Excel Marimekko Chart
Marimekko chart in excel is a two way stacked chart i.e. the combination of column and stacked bar charts combined together in under one roof. Marimekko chart is also called a “Mekko” chart in general. This chart is used to display market share or sales data of different companies which are competing in the same market or category of products.
For all those who are excited about the Mekko chart with unfortunately there is no built-in chart with excel. So, if there is not a built-in chart of Marimekko how do we build this chart?
The answer is we need to recreate or restructure our data to create a Marimekko chart. In this article, we will show you how to rearrange the data to create a Mekko chart in excel. With the data first, we will create a stacked area chart than by making some tweaks to the chart we will be able to create a Marimekko chart.
In the below section of example, we will show you how to build a Mekko chart in excel.
How to Create a Marimekko Chart in Excel Spreadsheet?
Below is the example of a Marimekko chart in excel.
As I told, in the beginning, the Marimekko chart is very useful to show the performance of different companies who are competing in the same market sector. For this example, I have created a simple data sample as below.
This is the data of companies market share i.e. column 2. In each market, each company shares percentage which is summing up to 100 in each market.
For an example in Market 1 Co., A has the market share of 30 but in Market 5 it has only 12. So like this data is.
Now in order to create Marimekko chart we need to rearrange the data, this includes a lot of complex excel formulas.
First, create a company list below.
In B10 & B11 enter values as zero.
Now in B12 apply below formula.
This is used to create a running total of market share. Once the formula is applied copy down the formula to below cells until the B28 cell.
Now in cell C10 apply below formula.
This used to create three values stack where the initial value is always zero, 2nd and 3rd values is the repetitive value of Co., A share in Market 1 & Market 2. Like this going forward, it will create three values of each market sequence.
Once the above formula is applied to the cell C10 copy down and to the right as well.
Now the calculation is over, the next step is to insert the chart. Select the data from B10 to G28 and click on the recommended chart.
Go to the area chart and choose the below chart.
Click on OK, we will have a chart like the below.
Select the horizontal-vertical axis and press Ctrl + 1 to open format data series to the right.
Change the Axis Type to “Date Axis”, major is 20, minor is 100.
Now we have a nice looking chart like the below.
Now we need to insert data labels to this Marimekko chart. So we need to create one more table to the right of our first table.
In a cell, I2 apply the below formula.
In a cell, J2 apply below formula and paste to other cells to the down.
Now in K2 cell apply below formula.
Copy the formula to down cells and paste it to other companies’ column as well to the right.
Now in Y Axis Column enter 100 for all the cells.
In the Market, Labels column enter the below formula and copy to other cells.
Once this table set up a copy the data from I1 to N7.
Once the data is copied select the chart and open paste special dialogue box.
Choose, Categories (X Labels) in First Column.
If you are not getting the chart correctly then download the workbook and change the legends to your cells.
Now finally our Marimekko chart looks like this.
This has been a guide to Marimekko Chart. Here we discuss how to create Mekko chart in excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
- Create Grouped Bar Chart
- Create 3D Maps in Excel
- Create Stock Chart in Excel
- Create Tornado Chart in Excel
- Create Funnel Chart in Excel
- 35+ Courses
- 120+ Hours of Videos
- Full Lifetime Access
- Certificate of Completion
- Basic Excel Training
- Advanced Excel Training
- Basic & Advanced VBA Course
- Excel Dashboard Course
- Data Analysis in Excel
- Create VBA Applications