Bullet charts are most useful for showing progress against a given target. They are variations on bar (or column) charts and show the current position against a target.
It is also possible to provide additional information by, for example, adding threshold values outside of which a KPI will be shown in red.
You can also show “zones”, to give added context to the values. Threshold values can also be used to provide clear visual indication of which KPIs are not in the expected range.
You can also choose to normalise the KPIs by showing them as percentages of either the target or the highest zone value rather than as the absolute values. This can be useful if multiple targets are shown on the same chart. For example, monitoring revenue and number of new customers at the same time would probably result in a mismatch in scale and would therefore be better shown as normalised.
The simplest bullet chart simply needs a series name, an actual value and a target value. Selecting these cells and pressing the Bullet Chart button on the ribbon will create the chart. Additional KPIs can be added in the rows below.
Lower and upper threshold values can be added in the subsequent two columns. If the actual value is outside of these values then the colour will change to the theme negative indicator colour. If the actual value is within these values then it will be the theme positive indicator colour.
The upper value of each zone can be added in subsequent columns – there is no limit to the number of zones that can be added. Zones are shown as variations of the theme neutral colour.
The single-click bullet chart is not “live”, however, because the actual data being charted is plotted on different series depending on the relative value. This means that it won’t update with changing data. However, to achieve this, you can use the “=QC.BULLET(…)” formula.
The array formula “=QC.BULLET(…)” will take the same input data and create an output with all of the required series to make the bullet chart. The output height of the array formula will be the number of series plus a title row, while the width will equal 5 columns plus the number of zones in the input data.
The formula has only two parameters: the first is the input data as described previously; the second is a TRUE/FALSE parameter which specifies whether the outputs are normalised.
When normalised, if no zones are used, then the target value for each KPI is used as 100%. if zones are used, then the highest zone value is 100%.
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 “Bullet” button on the ribbon (as you do to make the single-click version) or right-click and the “Make Bullet Chart” option should be at the top of the context menu.
There are only two styling options for bullet charts which can be accessed through the “Settings” button in the Bullet dropdown menu on the Excel Ribbon.
- The Horizontal / Vertical radio buttons toggle whether to plot the chart as a bar chart (horizontal) or as a column chart (vertical)
- The normalise checkbox controls whether the bullet chart axis is expressed as percentages. For one-click charts, this will also auto-normalise the data. For formula-based charts, the normalise parameter in the function should match the status of this check-box otherwise non-normalised numbers will be plotted as percentages.