Excel Marimekko Chart

Unfortunately, there is no built-in chart with Excel for all those who are excited about the Mekko chart. 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. This article will show you how to rearrange the data to develop a Mekko chart in Excel. With the data first, we will create a stacked area chart. Then by making some tweaks to the chart, we will be able to create a Marimekko chart.

In the below section of the example, we will show you how to build a Mekko chart in Excel.

How to Create a Marimekko Chart in Excel Spreadsheet?

Below is an example of a Marimekko chart in Excel.

Example

As we told you initially, the Marimekko chart is very useful for showing the performance of different companies competing in the same market sector. Therefore, for this example, we have created a simple data sample as below.

It is the companies’ market share data, column 2. In each market, each company shares a percentage that sums 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.

To create the Marimekko chart, we need to rearrange the data, including many complex excel formulasComplex Excel FormulasVLOOKUP, INDEX, MATCH, and IF AND are just a few of the advanced Excel formulae and functions available in Excel.read more.

First, create a company list below.

In B10 and B11, we must enter values as zero.

Now in B12, we must apply the below formula.

=INDEX(SUBTOTAL(9,OFFSET($B$2,0,0,ROW($B$2:$B$7)ROW($B$2)+1,1)),QUOTIENT(ROWS(B$12:B12)-1,3)+1,1)

It 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.read more of market share. Once the formula is applied, copy down the formula to the below cells until the B28 cell.

Now in cell C10, apply the below formula.

=IF(MOD(ROWS(C10:C$10)-1,3)=0,0,INDEX(C$2:C$7,QUOTIENT(ROWS(C10:C$10)-1,3)+1))

It is used to create a three values stack where the initial value is always zero, 2nd and 3rd values are the repetitive value of the company A share in Market 1 and Market 2. Like this going forward, it will create three values for each market sequence.

Copy down and to the right once the above formula is applied to cell C10.

Now that the calculation is over. The next step is to insert the chart. Select the data from B10 to G28 and click on the “Recommended Charts.”

Now, we must 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.read more and choose the below chart.

Click on “OK.” We will have a chart like the one below.

Select the horizontal-vertical axis and press “Ctrl + 1” to open the “Format Data Series” to the right.

Change the “Axis Type” to “Date axis,” major is 20, and minor is 100.

Now, we have a nice-looking chart like the one 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.

Cell J2 applies the below formula in a cell and pastes it to other cells to the down.

Now in the K2 cell, apply the below formula.

Copy the formula to down cells and paste it to other companies’ columns to the right.

In the Y-Axis column, we must enter 100 for all the cells.

The “Market Labels” column enters the below formula and copies it to other cells in the market.

Once this table is set up, we must copy the data I1 to N7.

Once the data is copied, select the chart and open the 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.read more dialog box.

Choose “Categories (X Labels) in First Column.”

If we do not get the chart correctly, download the workbook and change the legends to your cells.

Now finally, our Marimekko chart looks like this.

Note: We have changed colors.

This article is a guide to the Marimekko Chart. We discuss creating the Mekko chart in Excel, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Grouped Bar Chart in ExcelStock Chart in ExcelTornado Chart in ExcelCreate a Funnel Chart in ExcelCOS Excel Function