You have, say, plan bank balances for different scenarios and also a minimum or target value.
Wouldn’t it be great, if you could show these clearly in a chart and the bar colours would automatically change, depending upon whether the scenario value was above or below the minimum?
Well, you can! I will now show you how, step-by-step.
1. Set up your data with two extra rows
- Have scenario values in one row and the minimum values in another
- Two extra rows should show values (i) equal to or above the minimum and (ii) below the minimum. We will show these as two separate series in the chart, so that we can give them different colours
2. Create your chart
- Insert a stacked bar chart (not a clustered bar chart)
- Right-click in the chart and choose “Select Data…”
- For the Legend Entries (Series) select or add the three series: Above minimum, Below minimum and Minimum
- For the horizontal-axis values add e.g. the scenario names
3. Format your chart bars and line
- Right-click on a bar for the “Minimum” series and choose “Change series chart type…”
- For the series Minimum select “Line”
- Right-click on a bar for the series “Above minimum” and select “Format data series…” or left click and use the shortcut Ctrl 1
- Change the colour e.g. to light green
- Do the same for the series “Below minimum” and change the colour e.g. to pale red
- Do the same for the line series “Minimum” and change the line colour e.g. to dark red and maybe make it a dotted line
4. Add and format data labels
- Right-click on each of the three series in turn and select “Add data labels”
- To avoid zeros appearing for “Above minimum” when the value is below minimum (and vice versa) format the data label numbers so that zeros do not appear: right-click on a data label and select “Format data labels…” or left click and use the shortcut Ctrl 1. Enter the custom format #,##0_(;(#,##0); if your thousand separators are commas or #.##0_(;(#.##0); if they are dots and click “Add”. This format has three components: positive numbers; negative numbers; zero (blank)
- Finally you can select individual data labels for the series “Minimum” and delete all but the last one, which you can format to show to the right
5. Add chart title and tidy up the legend
- Add a clear chart title include units (e.g. EURk)
- Select and delete the legends for “Above minimum” and “Below minimum” as the chart title is clear enough
Et voilá… your great chart is complete!
If you want, you can save your chart as a template and use it again.
For general tips on the best chart type to use, see my best chart blog.
