How to add labels to the Mosaic plot

Excel 2016
This is the third and last part of the tip How to create a Mosaic plot in Excel (the second part is How to create a step Area chart for the Mosaic plot in Excel). The step area chart created in the previous part has invalid labels for both axes and no data labels:
The Mosaic plot with separators in Excel 365

There are at least two types of labels that are parts of the mosaic plot:

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

Labels for the mosaic pieces

1.   Prepare the new data

For every mosaic piece from the prepared data:

The new data 3 for Mosaic plot in Excel 365

Calculate the X and Y positions for the labels:

The new data for Mosaic plot in Excel 365

where:

  • X calculated as the middle of the width of each item plus the width of all previous items in the row:
    • 1st Class Child: = A15 / 2
    • 1st Class Female: = A15 + (A25 - A15) / 2
    • 1st Class Male: = A25 + (A29 - A25) / 2

    • 2nd Class Child: = A21 / 2
    • 2nd Class Female: = A21 + (A27 - A21) / 2
    • 2nd Class Male: = A27 + (A29 - A27) / 2

    • 3rd Class Child: = A19 / 2
    • 3rd Class Female: = A19 + (A23 - A19) / 2
    • 3rd Class Male: = A23 + (A29 - A23) / 2

    • Crew Female: = A17 / 2
    • Crew Male: = A17 + (A29 - A17) / 2
  • Y calculated as the middle of the height of each item plus the height of all previous pieces in the column:
    • 1st Class: = B14 / 2
    • 2nd Class: = B14 + E14 / 2
    • 3rd Class: = B14 + E14 + H14 / 2
    • Crew: = B14 + E14 + H14 + K14 / 2

2.   Add the new data series to the chart

   2.1.   Do one of the following:

  • On the Chart Design tab, in the Data group, choose Select Data:
    Select Data in Excel 365
  • Right-click on the chart plot area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 365

   2.2.   In the Select Data Source dialog box, click the Add button:

Select Data Source dialog box in Excel 365

   2.3.   In the Edit Series dialog box, choose cells with categories (Y) and click the OK button several times to close dialog boxes:

Edit Series dialog box in Excel 365

Excel adds the new data series to the chart and breaks the layout, but don't worry:

The new data series in the mosaic plot in Excel 365

3.   Change the data series type

   3.1.   Right-click on any data series and choose Change Series Chart Type... in the popup menu:

Change Chart Type popup in Excel 365

   3.2.   In the Change Chart Type dialog box:

  • Be sure that for the all data series except the last one have 100% Stacked Area chart type,
  • Choose the Scatter type for the newly added data series and select the Secondary Axis checkbox:
The Change Chart Type dialog box in Excel 365

4.   Change the new data series with the labels

   4.1.   Open the Select Data Source dialog box.

   4.2.   In the Select Data Source dialog box, under Legend Entries (Series), select the new data series and click the Edit button.

   4.3.   In the Edit Series dialog box, choose cells for Series X values (X) and click the OK button several times to close dialog boxes:

Edit Series dialog box in Excel 365

Excel updates the new data series in the chart:

The labels in the mosaic plot in Excel 365

5.   Optionally, change the secondary axis

   5.1.   Right-click on the secondary vertical axis and choose Format Axis... in the popup menu:

Format Axis popup in Excel 365

   5.2.   On the Format Axis pane, on the Axis Options tab, in the Axis Options section, under Bounds, type 100 in the Maximum field:

Format Axis pane in Excel 365

6.   Add the data labels

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

Add Data Label in Excel 365

7.   Hide the new data series markers

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

Format Data Series in popup menu Excel 365

On the Format Data Series pane, on the Fill & Line tab, in the Marker Options section, select the No Marker option:

Format Data Series pane in Excel 365

8.   Format data labels

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

Format Data Labels in popup menu Excel 365

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

  • Under Label Contains:
    • Select the Value From Cells option:
      Labels Options in Format Data Labels pane Excel 365
    • In the Data Labels Range dialog box, select the appropriate data range:
      Data Labels Range dialog box in Excel 365
    • Unselect all other checkboxes.
  • Under Label Positions, choose the Center option:
    Format Data Labels center in Excel 365
  • Make any other adjustment you desire.

Labels for the vertical axis

9.   Add the new data series for data labels

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

   9.2.   In the Edit Series dialog box:

  • In the Series X values field, type = {0,0,0,0} (so, the new data series will be shown on the vertical axis),
  • In the Series Y values field, select the data range or cells that were used for the other labels:
Edit Series dialog box in Excel 365

10.   Add the new data labels and format them

   10.1.   Add data labels, remove markers for the new data series like it did for the data labels.

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

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

   11.   Change the title, remove unnecessary items from the Legend and hide axes:

A Mosaic plot in Excel 365

Make any other adjustments you find appropriate.

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.