Marimekko Chart Excel (Table of Contents)
Definition of Marimekko Chart Excel
A two-dimensional stacked chart is most commonly used by a business analyst in consulting & finance companies. It is also referred to as Mekko or mosaic or matrix chart; this name is given to it because of the similar patterns in Marimekko fabric. Marimekko chart is frequently used to visualize categorical data for a pair of variables. Data is displayed or reflected as “Blocks”, which vary in height & width.
Marimekko chart helps to check out the companies’ relative positioning in different segments & Market segmentation analysis for any sector companies. In the Marimekko chart, we can represent or showcase two variables data, i.e. one variable through the vertical axis’s height and the other through the width of bars or columns.
Marimekko combines data from multiple charts (stacked column & bar chart) to a single two-dimensional stacked column chart, where the bar width reflects an additional variable, and each column represents company percentages for those specific segments.
There is no inbuilt Marimekko chart Option available in excel; it will take a huge amount of time to create it; in most cases, we have to create it with slight data manipulation variables.
How to Create a Marimekko Chart in Excel?
Let’s take an example to create a Marimekko chart in Excel for two variables.
Marimekko Chart – Segmentation Analysis for Different Companies and Their Relative Positions (without any Add-In Tool)
I have 4 different pharma companies with their sales data for four different drug class segments in the below-mentioned table. Now I have to create a Marimekko chart for these sales data showcasing the sales percentage across the different segment.
Here I have to showcase market share & distribution through the mekko chart; the first step is, to sum up, the total sales of each segment (in column G) & each company in a separate row (Row number – 8)
I need to convert each company’s total sales data across different segment to the percentage, which can be calculated by individual company sales divided by total sales (Percentage calculation is carried out from cells B12 to B15). you can observe, here the sales data is in ascending order (Highest sales to lowest sales)
A similar procedure is followed for each drug class segment sales data for different companies, i.e. sales data to percentage conversion.
The result is shown below.
Once we get the percentage sales of sales data for each company, to create a mekko chart, we need to create that many rows for each company, i.e. For the company sun pharma, I have created 50 rows because the percentage share is 50, a similar number of rows is created based on the percentage sales of each company so, the total hundred rows created, after this, I have to add the respective company distribution share for each company across different rows.
So, the total rows will be 100 rows data; now, for this set of 100 rows, we have to create a 100% stacked bar chart under the 2d column chart option in the chart tab.
Now, once the 100% stacked bar chart is created, we have to format it.
There is a gap between data series in the stacked bar chart; we have to format it, where we need to remove the gap width across data bars, by clicking on anywhere in data bars, and right-click on it, in that select an option format data series, where we need to update the gap width percentage from 150 to 0%
Once the gap width is updated to 0%, the chart below mentioned changes could be observed.
In the X-axis, you can observe the multiple similar names of company in the chart; it is because the no. of rows appears in the data table, i.e. 50 rows in the case of sun pharma. We can update it to a single company name record by keeping it at a middle portion and deleting the other records in the data table. To perform this, we need to delete the company’s multiple similar names and restrict them to a single entity in the data table.
Once the X-axis parameters are resolved, the data bars need to be formatted. You can observe the sales data for each segment of one company overlapping each other (Below mentioned screenshot). We need to get rid of this by creating boundaries across each company sales segment separately.
Boundaries across each company sales segment can be created to insert lines across each company borders.
Under the illustration, you need to select a shape; under that, click on a Line. That line appears in the chart area, where you need to format it and place it across each company border in the data bars.
I need to draw the line across the data bar’s border for a sun pharma company and need to format it.
The line background color needs to be updated or formatted from black to white under the shape outline option.
For a better appearance of the line across the border, we need to increase its weightage to 21/4 points under the shape outline options.
Once the line is formatted, we can copy and paste across each company data bar borders. I have removed the default Chart Title and changed it to the relevant title.
Now, the Marimekko chart is ready, where the bar width reflects or indicates each company sales performance in comparison to the other competitor companies for a given drug class segment, and column height represents the sales in that particular drug class segment for the individual companies.
Things to Remember About Marimekko Chart Excel
- Marimekko chart is primarily used for market analysis, i.e. market segmentation, % share of competitor companies in a market segment & individual share of companies in a market segment.
- There are various third-party utility or add-in tool & Power-user available in the market to automatically create a Marimekko chart by just inputting the raw data or variables most commonly used is Peltier Tech Charts for Excel 3.0
- Marimekko chart provides valuable input with a single chart, which helps the client with clear insight for future planning and executing it for improvement in company sales performance.
This is a guide to Marimekko Chart Excel. Here we discuss How to Create Marimekko Chart in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –