How to add labels to the Mosaic plot

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:

Calculate the X and Y positions for the labels:

where:

• X calculated as the middle of the width of each item plus the width of all previous items in the row:
  • 1^st Class Child: = A15 / 2
  • 1^st Class Female: = A15 + (A25 - A15) / 2
  • 1^st Class Male: = A25 + (A29 - A25) / 2
    
    
  • 2^nd Class Child: = A21 / 2
  • 2^nd Class Female: = A21 + (A27 - A21) / 2
  • 2^nd Class Male: = A27 + (A29 - A27) / 2
    
    
  • 3^rd Class Child: = A19 / 2
  • 3^rd Class Female: = A19 + (A23 - A19) / 2
  • 3^rd 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:
  • 1^st Class: = B14 / 2
  • 2^nd Class: = B14 + E14 / 2
  • 3^rd 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:
  
• Right-click on the chart plot area and choose Select Data... in the popup menu:
  

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

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

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

3.   Change the data series type

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

   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:

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:

Excel updates the new data series in the chart:

5.   Optionally, change the secondary axis

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

   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:

6.   Add the data labels

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

7.   Hide the new data series markers

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

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

8.   Format data labels

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

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:
    
  • In the Data Labels Range dialog box, select the appropriate data range:
    
  • Unselect all other checkboxes.
• Under Label Positions, choose the Center option:
  
• 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:

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:
  
• Under Label Positions, choose the Left option.

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

Make any other adjustments you find appropriate.