How to Make a Panel (Matrix) Chart in Excel

Step-by-step

A panel (or matrix) chart puts several years side by side in one chart, with a divider between them, so you can compare categories without a tangle of lines. The example compares the monthly sales of three supermarket categories over four years. It’s really one line chart, cleverly formatted, plus a scatter series whose error bars draw the dividers.

1. Stagger the data

Give each category two columns and alternate the years between them: one year’s values in the first column, the next year’s in the second, with blanks in between. The gaps are what separate the panels.

2. Shorten the month labels

Select the month cells, open Format Cells > Date (or Custom) and use the single-letter month format, mmmmm. The axis shows J, F, M… and saves space.

3. Insert a line chart

Select the data and go to Insert > Line. Move it and make it bigger. You’ll already see the panels, but in six colours.

4. One colour per category

Right-click a line > Format Data Series > Fill & Line and set its colour, then give the category’s second line the same colour: blue for beer, wine and spirits, orange for bread and bakery, green for cleaning products. Delete the title and the extra legend entries.

5. Work out the divider positions

A year has 12 months, so the dividers go at 12.5, 24.5 and 36.5. With 12 in a cell such as K1 and the counters 1, 2, 3 next to them, use a formula like =$K$1*J4+0.5 and copy it down. Put 0 next to each as the Y value.

6. Add them as a scatter series

Copy the positions (K3:L6), select the chart and use Paste Special to add a new series with the categories in the first column. Select it via Format > Current Selection, then Change Chart Type > Combo: set it to Scatter on the Secondary Axis. Delete the extra horizontal axis that appears.

7. Draw the dividers with error bars

With the scatter series selected, go to Add Chart Element > Error Bars > More Error Bars Options. Choose Plus, No Cap and a fixed value of 1. Set the secondary vertical axis maximum to 1 so the lines reach the top, and set its tick marks and labels to None.

8. Clean up

Delete the horizontal error bars, the scatter legend entry and the gridlines. Apply a shape style if you like and remove the chart border.

Go further

My Excel Charts, Infographics & Data Visualisation course teaches 20+ advanced charts like this from a blank sheet.