A cycle chart is designed to show trends and variation across time. Consider data that might usually be shown on a column chart like this:
There’s nothing wrong with this data visualisation but there is quite a lot of information to take in and to pick out the patterns or trends. The multi-spark chart gives an alternative presentation.
In this visualisation, the average value in each month is shown in the red line, and the annual variation is shown as a mini spark chart within each month. It’s quite easy to see which months are above or below average in the most recent period.
Creating the chart is simple – it uses the same data in a table as the standard column chart. Each row in the table will be a category on the X-Axis, while each column will be a point in the individual spark charts.
The single-click multi-spark chart is not “live”, however, because the actual data being charted is a set of derived values and not the raw points themselves. This means that it won’t update with changing data. However, to achieve this, you can use the “=QC.CYCLE(…)” formula.
This formula only has a single input parameter, which is the same data table as before (including optional title row). The output is a table with all of the constituent parts of the multi-spark. The height of the output array will vary with the data while the width will always be five columns.
Like most of the other QuickChart formulae, this is an array formula, so you must use “Ctrl-Shift-Enter” when you complete it, and select the full range required (although the most recent versions of Excel provide much better support for array formulae). More details on array formulae here.
Once you’ve entered the formulae, just select a cell in the formula range and press the “Cycle” button on the ribbon (as you do to make the single-click version) or right-click and the “Make Cycle Chart” option should be at the top of the context menu.
There are only two formatting settings for the Cycle chart which are configured in the Settings dialog box, accessible through the Cycle drop-down menu on the Excel Ribbon.
- Show Labels on Final Sparkline – this will add the column headings as labels on the right-most spark chart (illustrated below)
- Show Legend – This will show the legend on the chart when it is created.