How to add labels to the Marimekko chart

Excel 365
This is the third, last part of the tip How to create a Marimekko or Mekko chart in Excel (the second part is How to add separators to the Marimekko chart). The area chart created in the previous part needs data labels:
The Marimekko chart with separators in Excel 2016

There are at least two types of labels that the chart needs:

  • Data labels at the center of the mosaic piece,
  • Horizontal axis labels instead of 0-20-***-100.

To add the labels for the mosaic pieces, do the following:

   1.   Create a new data range to calculate the positions of each label:

For every piece of the chart:

The new data for Marimekko chart in Excel 2016

Calculate the X and Y positions for the labels:

The data for Marimekko chart in Excel 2016

Where:

  • X values are calculated as the middle of the width of each column plus the width of all previous columns:
    • A17: = A10 / 2
    • A18: = A10 + (A12 - A10) / 2
    • A19: = A12 + (A14 - A12) / 2
  • Y values are calculated as the middle of the height of each mosaic piece plus the height of all previous pieces:
    • B17: = B4 / E4 / 2 * 100%
    • B18: = B5 / E5 / 2 * 100%
    • B19: = B6 / E6 / 2 * 100%
    • C17: = (B4 / E4 + C4 / E4 / 2) * 100%
    • C18: = (B5 / E5 + C5 / E5 / 2) * 100%
    • C19: = (B6 / E6 + C6 / E6 / 2) * 100%
    • D17: = ((B4 + C4) / E4 + D4 / E4 / 2) * 100%
    • D18: = ((B5 + C5) / E5 + D5 / E5 / 2) * 100%
    • D19: = ((B6 + C6) / E6 + D6 / E6 / 2) * 100%

   2.   Add the new data series to the chart:

You can add the data series one-by-one or use the following method to add them all together:

   2.1.   Copy the new data range to the Clipboard by clicking Ctrl+C.

   2.2.   Select the chart.

   2.3.   On the Home tab, in the Clipboard group, select the Paste dropdown list and then choose the Paste Special...:

Clipboard in Excel 2016

   2.4.   In the Paste Special dialog box, be sure that selected:

  • In the Add cells as group, the New series radio button,
  • In the Values (Y) in group, the Columns radio button,
  • The Series Names in First Row checkbox,
  • The Categories (X Labels) in First Column checkbox:
The Paste Special dialog box in Excel 2016

Excel updates the chart after adding the new data series and you need to fix the formatting:

The new data series in Excel 2016

   3.   Change the chart type:

   3.1.   Right-click on the chart and choose Change Chart Type... in the popup menu:

Change Chart Type popup in Excel 2016

   3.2.   In the Change Chart Type dialog box:

  • Select the Combo tab,
  • Select the Custom Combination chart type,
  • Ensure that for the first three data series you selected 100% Stacked Area chart type,
  • Choose the Scatter type for the newly added data series (the Secondary Axis checkboxes are automatically selected).

   4.   Add the data labels:

   4.1.   Right-click on each new data series and choose Add Data Labels -> Add Data Labels in the popup menu:

Add Data Label in Excel 2016

   4.2.   Right-click on the new data series and select Format Data Series... in the popup menu.

   4.3.   On the Format Data Series pane, on the Fill & Line tab, in the Marker Options section, select the No Marker radio button:

Format Data Series pane in Excel 2016

   4.4.   Repeat the previous steps of this section for all new data series.

   5.   Format data labels:

   5.1.   Right-click on the new data labels and select Format Data Labels... in the popup menu:

Format Data Labels in popup menu Excel 2016

   5.2.   On the Format Data Labels pane, on the Label Options tab, in the Labels Options section:

  • Under Label Contains:
    • Select the Value From Cells radio button:
      Format Data Labels pane in Excel 2016
    • In the Data Labels Range dialog box, select the appropriate data range:
      Data Labels Range dialog box in Excel 2016
    • Unselect all other checkboxes.
  • Under Label Positions, choose the Center option:
    Format Data Labels pane in Excel 2016

   5.3.   Repeat the previous steps of this section for all data labels.

To add the labels for the horizontal axis, do the following:

   6.   Add a new data series to show labels:

   6.1.   In the Select Data Source dialog box, under Legend Entries (Series), click the Add button:

Select Data Source dialog box in Excel 2016

   6.2.   In the Edit Series dialog box:

  • In the Series X values field, select the same data range that was used for other labels,
  • In the Series Y values field, type = {0,0,0} (so, the new data series was on the horizontal axis):
Edit Series dialog box in Excel 2016

   7.   Add data labels, remove the markers for the new data series like you did for the data labels.

   8.   On the Format Data Labels pane, on the Label Options tab, in the Labels Options section:

  • Under Label Contains, check the Value From Cells radio button and select the appropriate data range:
    Data Label Range dialog box in Excel 2016
  • Under Label Positions, choose the Below option.

   9.   Remove the horizontal axis, the secondary vertical axis, unnecessary labels in the Legend.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.