Bar Chart question

excel_learnerz

Board Regular
Joined
Dec 12, 2018
Messages
73
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
If you want to do this as a column chart, it can be done like this.
Total-diff-on-side-of-columns-July-2-2019.jpg


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.
 
Upvote 0
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
 
Upvote 0
For the data label for the Diff Label series, here is the formatting in the Format Data Labels task pane.
Total-diff-on-side-of-columns-July-2-2019-Data-Label-setup.jpg


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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top