Small Multiples in Excel – Small But Good
“Small multiples” are charts that display a range of data in several smaller charts of the same type. This is often easier to read than mixing the same data in a single, more complex chart. The informational value and overview are the result of the aggregate charts in this case.
The specific application scenario determines whether to use a small multiple or a single chart with several data ranges. Stacked column or bar charts visually highlight the sum. Small multiple charts place the visual focus on the individual data ranges.
If the company revenue is displayed as a stacked bar chart (1 chart with 4 data ranges), for example, then total revenue is easy to see, but less so the precise development of individual product groups.
On the other hand, if 4 single charts (with one data range each) are displayed for the product groups, then revenue performance for each product group can easily be gauged. Additional details such as deviation indicators can easily be implemented at the product group level.
To maintain a good overview, however, you need to standardise the individual charts making up the small multiple. A consistent axis scale is especially important. Highlighting improves the informational value, e.g. using the signal colours green and red to mark positive (desired) or negative (undesired) deviations.
Unfortunately, this type of chart isn’t part of Excel’s native functionality. Nevertheless, you can easily implement custom small multiples in just a few steps.
Create Chart
- Chart Type: Cluster Bar
- Data ranges: main data range and min/max data range for a consistent axis scale
Format chart
- Add Data Labels
- Format Data Series… Series Overlap: 100% / Gap Width: 50% / bar colour: Solid Fill = green, Invert if negative, second colour = red / min/max colour: no fill
- Format Axis… Axis Options: Values in reverse order / Line: Solid Line, black / Label Position: Low
- Format Chart Area… Fill: No fill / Border: No line / Font size: 8 pt
Position chart
- Align chart area (outside) and drawing area (inside) to the cell using the Alt button
Copy charts
Adjust the data source for each chart
Done 🙂
Tip: In one of our next posts, we’ll be presenting a second way of realising small multiples even quicker for simple application scenarios.