How to Create a Marimekko Chart in Excel
If you’re new to the marimekko chart type, you’re in for a real treat. The marimekko is a visualization gem that allows you to break down a measure, like revenue or cost, into its components to understand each component's relative size. The marimekko combines data typically found in multiple charts (pie or bar) into a single, more powerful chart.
Restaurant Example
Nick Kokonas, the co-owner and co-founder of The Alinea Group of restaurants and the founder and CEO of Tock, Inc, a reservations and CRM system for restaurants, called the marimekko “one of the greatest visualizations" during his appearance on The Tim Ferriss Show. Check out the podcast here. The marimekko discussion begins around 1:55.
Nick introduced the marimekko chart to Tim Ferriss and explained how to use it for food cost analysis. He described the value of giving a chef a single chart instead of hundreds of receipts when reviewing cost issues. The description was outstanding! Here’s a chart similar to the one Nick mentioned in the podcast. This is also one of the featured charts in 10 Finance Charts, a guide to presenting financial insights.
Food Cost Marimekko
Download this marimekko chart in Excel and modify it using Mekko Graphics. Get a free, 30-day trial of Mekko Graphics here.
Learn more about when to use a marimekko chart and its sister chart, the bar mekko, by visiting The Power of Mekko Charts or Why Use a Marimekko Chart?
Follow These Steps to Make this Marimekko in Excel
The marimekko is not a chart type found in Excel. There are several steps you can take to hack the chart, but it's a cumbersome process. The steps below show you how to create this chart using the Mekko Graphics add-in for Excel.
1. Structure your Data
The cost data for 22 vendors (rows) is distributed across 8 food categories (columns) in the worksheet. Add a total row and use that row to calculate % of total. This will be shown below the chart to make it easier to understand the bar width. Highlight the data, including the 2 total rows, and choose Marimekko from the Mekko Graphics ribbon to insert the chart into the worksheet.
2. Determine How Data Will Appear on the Chart
Notice that the large Total series appears on the chart. To remove it, choose Bars and Series from the Mekko Graphics ribbon. On the Series Visibility tab, uncheck the box in the Chart column for the Total series and % of Total series. To show the % of Total series as a data row below the chart, check the Data column and choose a Number Format of % from the dropdown menu.
Next, consolidate the small series into an Other series to make the chart easier to read and to focus attention on the largest cost components. Under Bars and Series, choose the Series, Options tab. Click on the + sign under Other series and type in Other in the dialog box. Manually select series using the check boxes or add series based on values. In this case, enter a value of 25,000 and click Update so all series under that value are summarized as Other on the chart.
3. Format the Chart
Click on Chart in the Mekko Graphics ribbon to find the most commonly used formatting options.
Show Labels
On the General tab, determine which labels to show for each series. Check the boxes for Values and Calculated Percentage to include them on the chart.
Format Values
To format the numbers on the chart, move to the Values tab. Multi-select Grand Total, Bar Totals and Segment Values by holding down the shift key and selecting all 3. Then, choose $ from the Currency dropdown menu.
To show the calculated percentages on a separate line in the series labels, select Segment Calculated % on the Values tab and check the new line box (return symbol). Uncheck the box (%) to show the calculated percentages without parentheses.
Notice that the calculated percentages for each series are calculated as a percentage of the bar total. To show these values as a percentage of the grand total instead, scroll down to Calculated Percentage on the Values tab and choose Grand Total.
Sorting
Sorting the chart makes it easier to compare the relative sizes of categories and vendors. You can sort the chart without altering the underlying data in the worksheet. On the Sort tab, show the largest series at the bottom of each bar and the widest bar on the left of the chart.
You may want to show the Other bar as the last bar in the chart instead of placing it in its sort order. Choose Bars and Series and navigate to the Bars, Visibility tab. For the Other bar, choose Always on Right from the dropdown menu under the Include in Sort column.
Manage Labels
The final step is to clean up the labels. From the whitespace of the chart, right click and choose Select All Labels. Then, click on a single label and choose a font size from the floating menu to make all text on the chart the same size. You can move an individual label by dragging it or using the arrows to nudge it.
Updating the Chart
Now that the chart is complete, it’s easy to reuse it for the next month’s cost data. Simply copy the chart and paste it into a new worksheet. Choose Change Data Range from the Mekko Graphics ribbon and select the range for the new data.
To learn how to make Excel charts in less time, check out 10 Excel Tips You Should Know.
Using the Chart in PowerPoint
To use this chart in a PowerPoint presentation, just copy the chart in Excel and paste it into a slide. Resize the chart to fit the space on your slide.
The chart is now linked to the Excel data. A red border will appear around the chart if the Excel data changes. To update the chart, choose Link to Excel from the Mekko Graphics ribbon and select Refresh. Choose Remove if you do not want to retain the link to your Excel data.
You may want to increase the font size if you’re using a much larger chart in PowerPoint. Right click on the whitespace in the chart and choose Select All Labels. Then, right click on a single label to choose a font size.
