Small Multiple of Bar Graphs or Line Charts in Excel (Trellis / Coplot)

Small multiples—an expression coined by Edward Tufte—is a series of small frames sharing the same graphical design structure, yet showing different data as the eye moves from one panel to the next.

Small multiples are efficient architecture for showing a large amount of multivariate data all in one eye span—hence enabling comparisons.

Unfortunately, Excel charting library doesn't include native small multiples. Users are either obliged to resort to VBA or to take the time consuming task of creating each and every chart, formatting them and arranging them in a suitable way to show patterns. If the data changes, the process has to be repeated. Not fun. Not Efficient.

So here I'm offering you an Excel template that takes a data set as an input and creates a small multiple of bar graphs or line charts with the following capabilities:

- Handles up to 144 categories—hence 144 panels. Shape of matrix is limited to 12 rows (12x12 matrix) but can handle up to 144 columns (1x144 matrix) and everything in between (i.e. 5x9 matrix).

- Sort categories by alphabetical order, mean, median, sum, last data value, CAGR or as per the original data source.

- Sort categories by descending or ascending order.

- Define the scales (tick-mark spacing) and their number formatting.

- Optimize the vertical scale of the line chart to fit the data—or to constrain it to start at 0.

- Adjust the margin from the edges of the panels to the data to fit the category labels.

- Add reference lines to each panel such as mean, median or user defined.

- Adjust the above settings with a user-friendly interface.

- Data entered into Excel Table—hence the chart and formulas update automatically when the datasource expands or shrinks.

- The size of the data matrix is only limited by Excel's charting and calculating capabilities.

- Fully customizable chart using standard Excel functionality enabling you to design a large variety of charts.

- Unlocked workbook/worksheets with ability to expand template to suit your needs.

System requirements

The template relies heavily on Excel's Dynamic Arrays capabilities, only available to Microsoft 365 subscribers.

What's the refund policy?

If what you see is not what you expected, just reply to the download email within 30 days, and you'll get a full refund. No questions asked.