Marimekko chart is also known as a mekko chart in excel, this chart is a two dimensional combination of both 100% stacked column and 100% stacked bar chart in excel, the creativity of this chart it is that it has variable column width and height, this is not a built-in chart template in excel but however there are other ways to make this chart in excel.
Excel Marimekko Chart
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 a percentage that is summing up to 100 in each market.
For example, in Market 1 Co., A has a market share of 30, but in Market 5, it has only 12. So like this data is.
Now in order to create the Marimekko chart, we need to rearrange the data; this includes a lot of complex excel formulasComplex Excel FormulasVLOOKUP, INDEX, MATCH, and IF AND are just a few of the advanced Excel formulae and functions available in Excel..
First, create a company list below.
In B10 & B11 enter values as zero.
Now in B12, apply the below formula.
This is used to create a running totalRunning TotalRunning Total in Excel is also called as “Cumulative” which means it is the summation of numbers increasing or growing in quantity, degree or force by successive additions. It is the total which gets updated when there is a new entry in the data. of market share once the formula is applied, copy down the formula to below cells until the B28 cell.
Now in cell C10, apply the below formula.
This is 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 chartArea ChartThe area chart in Excel is a line chart that shows the impact and changes in various data series over time by separating them with lines and presenting them in different colors. The line chart is used to create this graph. 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 applies the below formula.
In a cell, J2 applies the below formula and paste to other cells to the down.
Now in K2 cell, apply the 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, the Labels column enters the below formula and copy to other cells.
Once this table is set up, a copy of the data from I1 to N7.
Once the data is copied, select the chart and open paste specialPaste SpecialPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S. 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 the Mekko chart in excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –