Matrix Chart

Introduction

In December's article, Pie of Pie Charts and other Pie Chart Tricks, we saw how pie charts can effectively show proportions of constituents comprising a larger quality. We saw how pie-of-pie charts can further break down one of these proportions one level deeper, and we learned a technique to use multiple pie charts to break down all proportions to the deeper level (shown below).

 
Multiple Pie-Of-Pie Chart showing
proportion of market by region, and proportion of region by product.

An alternative to a multi-pie-of-pie chart is a matrix chart comprised of stacked columns or bars. While the size of each stack is proportional to the breakdown of the data in one dimension, standard stacked charts have uniform column widths or bar heights (see below).

A matrix chart enhances a standard column or bar chart by making the column widths or bar heights proportional to another variable, as shown below.

This article has step-by-step instructions for creation of a matrix column chart, and shows an example of an enhanced matrix chart.

Mechanics of Matrix Charts

A column-oriented matrix chart can be constructed in one of two ways, illustrated in the sidebar below.

1. a chart can be constructed as a stacked column chart with a large number of columns; groups of these columns are treated together to together to give the appearance of variable width columns.
2. a chart can be constructed as a stacked area chart, and by tricking Excel into using a time scale axis, construct a variable width column.

A row-oriented matrix chart can only be made using a stacked row chart, because an area chart can only have a horizontal category axis.

Preferred approach
The column chart technique requires a large source data range, for the 100 or more rows of data needed to provide sufficient resolution for the variable width columns. The column chart approach also requires several dummy series to provide borders around the variable width columns. The area chart technique needs a much smaller and easier to maintain data range, and only the dummy series that provide the axis labels.

Sample Data

The first thing we need to do is condition the data, so it's suitable for our matrix chart. This exercise will start with the table of data shown below in A1:E5 of our worksheet, with columns for the regions and rows for the products. The data is for an imaginary engineered materials producer, with products in chemical, laminate, and pharmaceutical markets.

Matrix Chart Data

This example builds a chart with three series: Chemicals, Laminates, and AMG. The chart has three pseudo categories: Americas, Europe, and Asia.

The data above is a good input for various stacked or clustered column, bar, or line charts, but for our Column Matrix chart, we need to rearrange the data, as described in the Mechanics of Matrix Charts sidebar above. J1:L1 contains the Series names for our three series. I2:I11 contains the cumulative regional totals as the numerical categories for the chart (Americas, Americas + Europe, Americas + Europe + Asia). J2:L11 contain the values for each of our series for each section of the chart. A more complex range could contain formulas to calculate these values, but this range was constructed by hand. The worksheet range is shown below:

Building a Column Matrix Chart

The first step is to select the data in the previous table and construct a stacked 100% area chart.

Select Chart Options from the Chart menu, click on the Axes tab, and change the Category Axis option from Automatic to Time Scale.

Double click the vertical axis, and on the Patterns tab, choose None for Major and Minor Axis Ticks and Tick Labels. Repeat this on the horizontal axis (select it and use the F4 key, which is a shortcut for Repeat the Last Action).

Dummy series will be used for the axis labels of the chart. For each set of labels, a range is needed that contains the X values, the Y values, and the labels. Below are the ranges needed for this chart. The zeros put the dummy series against the vertical or horizontal axis; while the decimal fractions are determined by formulas to put the labels in the center of the appropriate block along the axis.

The vertical axis position for the Laminates label, for example, must be in the middle of the Laminates-America data block, which is stacked on the AMG-America block, and this is normalized to a range of 0 to 1. The formula then is:

     =((AMG-Americas) + (Lam-Americas)/2) / (Americas Total)
     =((149,759) + (169,229)/2) / 440,876
     = 0.531609

The horizontal axis position for the Europe label, by the same token, must be in the middle of the Europe data column, which is to the right of the Americas column, and this also is normalized to a range of 0 to 1. The formula then is:

     =(Americas + Europe/2) / (Grand Total)
     =(440,876 + 330,392/2) / 1,027,898
     = 0.589623

Naturally you would to use links to cells which contain these values instead of hard-coding the values, unless you don't ever expect the values to change.

Select and copy the first two columns of the V Labels region (don't select the column of labels). Select the chart, and from the Edit menu, select Paste Special, and choose the New Series and Categories in First Column options. The new series doesn't appear, since it's another area stacked on the chart, and the values are so much less than those already in the chart.

Select the new series: select it from the dropdown box on the Chart command bar. With the series selected, choose Chart Type from the Chart menu, and select any of the XY Scatter types. The series appears along the vertical axis (square symbols), and new secondary axes are drawn along the top and right edge of the chart.

Select and copy the first two columns of the H Labels region (don't select the labels). Select the chart, and from the Edit menu, select Paste Special, and choose the New Series and Categories in First Column options. The series appears as a new Scatter type along the bottom of the chart (diamond symbols). Excel remembers that the last series was changed to a Scatter type, so it doesn't have to be changed.

Data labels are applied to the data points of the scatter series. Custom labeling of dummy series points can be done manually, most of these tricks rely on Rob Bovey's Chart Labeler, a free add-in available for download at http://appspro.com, or John Walkenbach's JWalk Chart Tools, another free addin from http://j-walk.com/ss/excel. These utilities are easy to install and easy to use.

Use one of these handy add-ins to apply the labels from the worksheet cells to the two scatter series.

Hide the scatter series: double click one, and on the Patterns tab, select None for markers and lines; then select the other and press the F4 key. Hide the secondary axes: double click one, and select None for Major and Minor Tick Marks and Tick Labels; select the other one and press the F4 key.

Below is a prototype developed for an old project. Chart and axis titles have been included, and letter and number labels across the top and right will be replaced by descriptive labels like those on the bottom and left of this article's chart. The arrow indicate the annual growth of each segment of the market.

Jon Peltier is president of Peltier Technical Services, Inc., which provides custom Excel solutions to help clients in engineering, finance, marketing, and other fields analyze, visualize, and understand information more quickly and more thoroughly.

Peltier Technical Services uses the power of Excel's VBA programming environment to integrate Excel's worksheet functionality and charting capabilities with the presentation and reporting abilities of PowerPoint, Word, and other applications. The company web site also houses one of the most extensive collections of information about Excel charting, including tutorials, tips, special techniques, and sample VBA code for generating customized charts and charting features.