I'd like to show the data label for JUST the max value on a stacked column chart. The example that I've attached is just for three months, for ease of manipulation. Ideally, the finished chart would show 12 months of data.
Hi Andera
From your screenshot it looks like the chart came from a PivotTable and this can complicate matters.
Furthermore I don't know if the Month is a true date or just plain text.
I have added a couple of helper columns and dummy data.
See if my attachment helps (never done this before).
You may need to adjust the axis scale as well as the sorting order if more months are added.
You can refer here https://www.myonlinetraininghub.com/label-excel-chart-min-and-max for more ideas although it is for normal charts (not Pivot Charts).
Good luck
Sunny
I apologize, as I didn't explain myself entirely, nor did I supply an example workbook (thank you for going to the trouble of recreating data for your solution).
See attached file.
I'm hoping to get the data label to display, directly on the associated segment of the stack, for whatever month in the stack is the highest value (I did it by hand for the purposes of my example in the attachment).
I know that Pivot charts can be challenging, so I was working with data that was pasted as values.
Hi Andrea
Creating interactive custom labels is challenging enough and using a stacked bar chart makes it worse.
Since I was unable to find any ready-made solution, I created my own instead.
It involves some manipulation of helper columns but should give you some ideas on how to proceed from there.
Good luck
Sunny
Hi Andrea
I have updated the file with months from Jan-Dec.
Sunny
Oh, wow...very impressive. Would you mind explaining what the dummy column is doing and how it relates to everything else? What is causing the Max value label to appear ONLY on the corresponding month data segment? I see that the values in column S change, as new values are entered in the range E3:P10. And I now kind of understand what OFFSET is doing, but only if I really concentrate.
Hi Andrea
What I am trying to do is to make the Max series "float" above the Jan-December series.
For that to happen, I need to determine how much to "float". Let's take Seafood for example:
The max sales is in Mar so I need to add the total for Jan and Feb (Dummy) and stack Mar on top to make it "float".
Column S search for the location (column) of the maximum value (in this case 3) and so I need to add 2 columns starting from column E.
OFFSET will do the adding based on column S. Maybe I should just call Dummy as Float instead.
Both Dummy and Max is plotted on the secondary axis (I deleted the axis) and I added the data label to Max only.
I then adjust their Overlap and Gap Width and set their colors to no-fill to create the effect.
Hope I explained clear enough.
Sunny
I have no idea how your brain was able to manufacture that solution, but it's brilliant. Thank you very much for your assistance 🙂
I myself don't quite understand how my own brains work at times but I am happy that the solution is working for you.
Cheers
Sunny