How to create a step Area chart for the Mosaic plot in Excel

Excel 2016
This is a second part of the tip How to create a mosaic plot in Excel. The first part describes how to prepare the data for the step Area chart created in this part.
The new data 3 for Mosaic plot in Excel 2016

To create the step Area chart, do the following:

   1.   Create a simple, non-formatted area chart:

   1.1.   Select a data range (in this example, B13:L29).

   1.2.   On the Insert tab, in the Charts group, click on the Insert Line or Area Chart dropdown list:

The Insert Line or Area Chart button in Excel 2016

From the Insert Line or Area Chart dropdown list, select 100% Stacked Area:

The 100% Stacked Area Chart in Excel 2016

At first glance, Excel creates a little strange area chart for this data, but don’t worry (it will become normal after a few customizations):

A little strange area chart in Excel 2016

   2.   Customize the horizontal (category) axis:

   2.1.   Do one of the following:

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

   2.2.   In the Select Data Source dialog box, under Horizontal (Category) Axis Labels, click the Edit button:

Select Data Source dialog box in Excel 2016

   2.3.   In the Axis Labels dialog box, choose cells with categories (X values) for this axis and click OK several times:

Axis Labels dialog box in Excel 2016

   2.4.   Right-click on the horizontal axis and choose Format Axis... in the popup menu:

Format Axis in popup menu Excel 2016

   2.5.   On the Format Axis pane, on the Axis Options tab, in the Axis Options section:

  • Under Axis Type, select the Date axis radio button,
  • Under Bounds, be sure that:
    • In the Minimum field, the value is the 1st day in Excel:
      • 00-01-00, if you use the date format mm-dd-yy,
      • 01-00-00, if you use the date format dd-mm-yy,
    • In the Maximum field, the value of the 100th day in Excel (the 4th of September):
      • 09-04-00, if you use the date format mm-dd-yy,
      • 04-09-00, if you use the date format dd-mm-yy,

    Note: Unfortunately, at the moment, there are no other ways in Excel to set the calculated axis from 0 to 100. Thus, this workaround means that you use the calculated amount from 0 to 100 (from the 0 to the 100th day because Excel treats dates as numbers).

  • Under Units, type 20 days for the Major units:
Format Axis pane in Excel 2016

Excel changes the original chart to the following step area chart:

The new step Area chart for Mosaic plot in Excel 2016

   3.   Customize the data series:

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

Format Data Series in popup menu Excel 2016

   3.2.   On the Format Data Series pane, on the Fill & Line tab:

  • In the Fill section, select the Solid fill option and choose the color you prefer:
    Format Data Series pane in Excel 2016
  • In the Border section, select the Solid line radio button and choose the color and width in the appropriate fields:
    Format Data Series pane in Excel 2016

   3.3.   Repeat the previous steps of this section to match data series visual appearance:

The Mosaic plot with separators in Excel 2016

To fix the labels of the chart, see the third part of this tip How to add labels to the mosaic plot in Excel.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.