Monthly Archives: September 2015

Excel – How to Create a Trellis Chart

There are many ways of displaying data within Excel, and the basic charts that come as standard are perfectly adequate for most jobs. A line chart or clustered column chart is often the best way to display data over time. They are both clear and simple to interpret and at a glance can show you a trend or pattern.

There are times, though, when you might want to do comparisons of data. Having multiple lines or columns can sometimes work, but there may be times when you want to compare two set of data side by side.

This is where a Trellis chart may be useful.

A Trellis chart allows you to place to data points on the same line showing a direct comparison between the two data points. Let’s say we want to compare sales of a number of products over a two year period.

We could use either a line or column/bar chart. It works, but perhaps having the data for each customer on a separate line might be better.

Using standard fare columns and line charts

Using standard fare column and line charts

First of all we need to set up our data correctly in order to create the Trellis chart.

In a column to the left of your data add a series of zeros. This will make more sense a few steps further on.

Our zero values t help create data points for labels

Our zero values t help create data points for labels

Add two more columns to the right of your data and label these the same as your two original data columns – in this example 2014 and 2015.

In your new columns rank your rows in descending order.

Setting a rank for each row

Setting a rank for each row

Next add one more column and let’s call it LABELS. Add the same descending rank numbers again to this one too.

Adding a ranked labels column

Adding a ranked labels column

Our data table is now set up.

Select all the data from column C to F and click on INSERT, and from there select SCATTER chart, which will give you this;

Initial chart appearance

Initial chart appearance

Click on the chart to activate it, and go to the FORMAT tab. From the CURRENT SELECTION group on the far left, use the drop down to select elements from the chart.

The drop down to select an element of the chart

The drop down to select an element of the chart

First, select LABELS. You should now see this;

Re-selecting our data

Currently selected data for labels

Grab the PURPLE box that is currently around the 2015 series, and drag it over to the column made up of zeros in column A, so you should end up looking like this;

...after the change has been competed

…after the change has been completed

Now select 2014 series and this time drag the PURPLE box over the data in column B. Your chart should now be taking shape;

ScreenHunter_204 Aug. 31 11.49

Starting to take shape

Remove any vertical grid lines, as these are distracting as we want to be able to compare two data points along a line rather than on a grid.

No vertical gridlines

No vertical grid lines

At the moment the LABELS on the Y axis don’t tell us anything useful, they are merely markers that have helped us to align the data points. We need to attach some labels that tell what each line represents i.e. the customer name.

Create some custom labels in some blank cells. If you have 2013 (or later, assuming the same functionality is carried over) click on the chart, then click on the plus symbol and click the arrow to the right of DATA LABELS and select MORE OPTIONS. Untick the Y VALUE option to remove the current labels and tick VALUE FROM CELLS instead.

Then highlight the cells containing the custom labels you created a moment ago. Make sure you select LEFT as the position. You may need to adjust the PLOT AREA to make sure the labels are clear of the chart. Format the Y axis so that so LABELS are set to NONE. Finally make the markers “invisible” as we don’t need to see them, but they do need to remain in place, and remove LABELS from the LEGEND. Your chart should now look like this;

With added custom labels so it makes more sense

With added custom labels so it makes more sense

Feel free then to customise your chart as you see fit to make sure people understand what you are showing them.

The completed Trellis chart with a bit of customisation

The completed Trellis chart with a bit of customisation

If you don’t have Excel 2013, you can still do all this, but you will need the X Y CHART LABELER add-in by Rob Bovey. For more information on this useful little add-in see my blog

So that’s how you can build a Trellis chart. It’s an alternative chart to a column or line chart to compare values. Like all custom charts (or even just charts in general), it’s about using the most appropriate chart for the job, rather than just using it because you know how to, but this one does have its uses and can help to tell a visual story.