Bar Chart question

[excel_learnerz]

Hey guys,

I have a bar chart with two values for a given year and this done over a 5 year period.

For example blocks highlighted blue will be ascending over time with amounts increasing each year and orange will be decreasing each year

Can the difference between the total summed values for each be displayed (not on the bars per year itself more like to the right hand side like a legend)?
eg blue has 10k over 5 years and orange has 7k over 5 years so the one value 3k would be on the graph and changes as the graph changes

Its not letting me post an attachment

Thanks

 

[Dave Paradi]

If you want to do this as a column chart, it can be done like this.

The Diff Label data series is a line graph (using the Combo chart type that allows each data series to be a different chart type) and since it has only one data point, it is invisible. The value of that one point can be whatever you want, here I've set it to half the maximum column height so it is centered vertically. Cell E7 has this formula: =CONCAT("Total difference: ",SUM(B2:B6)-SUM(C2:C6)) that creates a custom string that is used as the data label for the point in the Diff Label data series (using the ability to add data labels from different cells introduced in Excel 2016). Since the label includes a value calculated from the data, if the data changes, the label automatically updates.

I hope that is the type of chart you were looking for.

 

[excel_learnerz]

Hi Dave,
thanks for that, it never would of occured to me to use a combo chart,
I am having trouble setting up the chart so the total difference appears, all i get is the actual value showing (3.75)
Can you help me with the set up

 

[Dave Paradi]

For the data label for the Diff Label series, here is the formatting in the Format Data Labels task pane.

Notice that it uses the Value From Cells option set to the cells E2:E7 (yes, it needs to include the whole range because it needs cells for all the other categories too even though there are no values in the cells) and not the Value option. This is how you can use values from another set of cells as labels in a chart. This feature was introduced in Office 2016. By setting the value of cell E7 as described in my previous reply, it allows you to have a custom label that includes the calculated range.

Hope that helps.

Dave