How to Insert Dynamic Labels Inside Stacked Column Charts in Excel
HTML-код
- Опубликовано: 15 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Enhance your Excel charting skills with this valuable tutorial focused on adding dynamic series labels inside a stacked column chart.
⬇️ Download the workbook here: pages.xelplus.com/stacked-cha...
Here's what you'll uncover:
▪️ Dynamic Series Label Placement: Learn the method to dynamically position series labels within the last stack of a stacked column chart.
▪️ Creating a Scatterplot Series for Label Positioning: Understand how to use a scatterplot series to mark the exact positions where you want your text labels.
▪️ Adjusting Series Data for Accurate Positioning: Discover how to adjust the X and Y values for your series data to place labels precisely in the middle of the stacks.
▪️ Hiding Scatterplot Points: Find out how to hide the scatterplot points, leaving only the dynamic labels visible.
▪️ Formatting Data Labels for Readability: Get tips on formatting your data labels to make them stand out and be easy to read against the chart background.
▪️ Testing and Adjusting the Chart: See how to test and adjust your chart to ensure labels move dynamically with the data as it changes.
▪️ Enhanced Chart Readability: Learn how this technique makes it easier for viewers to read and understand the data without referring to the legend.
Get access to the full Excel Dashboard course here: www.xelplus.com/course/excel-...
This technique works great for stacked column charts. Instead of using the default Excel legend, add a dynamic series to the chart that controls the position of the series labels.
To learn how to add total values to a stacked column chart, take a look at this video: • How to Add Total Value...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/stacked-chart-totals-file
At first I just downloaded your workbooks and use them as templates. Then I realized it's best to learn and do things myself. Now I create charts from scratch, step by step, beginning to end, using your instructions. Thank you Leila, I know you have to put in a lot of effort making these videos. You're my Chart Savior.
Thank you so much, Leila. Dynamic chart is great. I love your VDO, it brings my chart to another level.
You're very welcome. I see, you are interested in Charts too - it's one of my favorite topics :)
I'm glad to hear your reports are improving - thank you for your comment & support.
Awesome as Always
This is bringing my charts to another level ! Thank you for the tips, keep up the good work !
Great to hear! You're welcome.
Thanks a lot Leila, your lectures have enhanced the efficiency of my work. God bless you
Very happy to hear that Robert.
Amazing, thank you sooo much.
I have improved a lot in presenting charts.
That is an awesome tip!!! Thanks : )
You're welcome Mike. Excel charts are a lot of fun to work with. They remind me of lego blocks....or maybe it's because of the thumbnail image :)
Yes, I agree about the Legos... Excel is fun like Legos, videos games, math and many more fun things!
Very true :)
As always you have the best tips presented in such an easy, fun way to learn them. Kudos! I have learnt so many new ways to present data in a more dynamic and impressive way. Keep up the good work.
Thanks Sher! Happy to hear that.
Really Enjoyed it thanks
Excellent!! Thank you!
Really loving the videos. Thank you so much.
Glad to hear that Kevin. You're very welcome :)
Leila never thought about it like that... Brilliant!
thanks you are only one who teach about visuable chart.
You're welcome Seoin. I'll make sure to add in more tips & tricks :)
Thanks a lot for the awesome videos, Leila. They're incredibly useful. I'm learning a lot from you. Just a shout out for the Mac users. I've found it impossible to add two scatter plot series to the same chart and it took me a long time to figure out an alternative way of achieving the same results (unsurprisingly, Excel for Mac seems to be a lot less user-friendly, IMHO). It seems that a slightly different method is required and this is the only one I've found to make it work:
1- Create a chart including the main data series and the total series as stacked columns first
2- Select the total series and change it to line chart type
3- Add the legend series (use the same values for X and Y as shown in the video). It should appear as a line chart. Change it to scatter plot and attribute it to the primary axis if it's gone to the secondary.
Make all the other adjusts as shown by Leila in the video (add labels, make total series and legend series invisible, etc.). More advanced Mac users may have found a more direct approach than mine.
Please, Mac users, keep sharing your ideas when Excel for Mac requires a different approach. I find it a lot more difficult to navigate, to be honest! And thank you again for the great videos, Leila! I will certainly do one of your courses in the future.
Fabulous. Reminds me of some of the techniques that you showed us in your Udemy Chart course! Thank you for playing my music in the background. I'm really touched by that!
You're welcome Michael. It's a great melody - thank you for sharing it with me :)
Thanks a lot Leila. Very helpful.
You're welcome Hassan.
Excellent and very helpful. thanks!!
You're welcome Zaha.
Excellent video and smart tricks. Thanks
You're very welcome Lotfy.
great video
i have learnt a lot from your videos
keep sharing the good stuff
You're welcome Imran.
Thanks for the share.
Amazing 🤩
Awsome leila...that is a big trick
Glad you like it Mohamed.
thanks
Dear ma'am
I regular attender to your video .everything is good and wonderful
Thanks a lot.in the next coming days I will demand new video for my use
Thanks for the video . i have a question on how to highlight the max value in different colour using pivot chart.
Dear Leila, thank you for the great channel. I need you to help about how can I have countries flags in my chart
The best! Beauty and Brain @ its finest! :-)
This made me laugh! Glad you like it.
made u laugh? how so?
In a nice type of way.... I don't associate myself with either the beauty or the brain :)
Very good explanation. Sometimes for very small values excel makes the height of the stack too small to fit the data label font. Is there a way to change the stack size (height) ?
Good stuff. More charting techniques and tricks.
Thanks Sal. Charts are also my favorite - will for sure add in more. Anything in particular you're looking for?
You're welcome. Looking for how to create vertical and horizontal thermometer charts, bullet charts etc. Have a drop down list to show different charts.....a scroll bar that controls the chart values.....finding the right colors for a chart? When to use them?
That's a good list. Thanks Sal. A lot of these are part of my online charts course on Udemy but I'll make sure to add some to RUclips as well :)
great video Leila as always. 2x quick questions please.
1. If I have only 2x stacked column charts, how to I move them closer to each other please as the space between them make the chart too big and space wasting?
2. Also, can I do a stacked bar chart with one of the elements a line chart with no line but just a line marker please? Thanks
Hi Leila, this all is not possible if we create graph from pivot table, is that right? Thank you in advance
A nice video
Thank you!
Leila Gharani mam please make a video how to insert Standard error bar in stack bar
Hi, I have a larger data set that requires a pivot table and chart. Can this be done with a pivot table and resulting chart? Thank you.
Nice video Leila, is it possible to put the legend in the right side. Thank you in advance
I just figured it out... on the x axis of the data label instead of adding +1, subtract -2 or whatever to get it to where you need it
@@andrewchiles Thank you :)
Hi Leila, sorry to reach out to you, but I can't find a solution to a problem I have with data labels in my charts. I have two header rows, the first with an abbreviated category and the second row contains the category description. The rows below the headers show how many occurrences of each category there are for each location (The header column) I want my data labels to show the number of occurrences and the abbreviated code from the first header row. I still want to see the full description as the legend from the 2nd row. So as an example, I have a location called "Depot", with 3 incidents under the header "Staff Injury" (The abbreviated code above Staff Injury is "H"), so I want the data label to show H-3 and the legend to show the longer description of "Staff Injury" Does this make sense? Again, really sorry to ask you, but I have searched google with no luck. Thank you. Darren.
excellent tip madam.... thanks.
madam can u please make vedio for searching drop down list...
You're welcome Alpesh. Not sure what you mean by searching drop down list - could you give me more detail on that?
Leila Gharani
ok madam
we make drop down list by data validation..but suppose ,in that list there are thousands item , so we have to look all list up to end to find specific name, but if we type initial letter of name so relete to that letter all name come up dynamic.
hope you got my point
thanks
Hi Alpesh - Unfortunately there is no built in way to do this - no formulas etc, but VBA would be able to handle it....I'll add this to my list. Thanks for sharing.
ok...madam.
waiting for next video
If one hides the data labels columns or groups them together and closes the grouping the labels disappear. Upon expanding the group or unhiding the data label column, the labels reappears in the chart. How can one hide the data label columns (by grouping or hiding) while the new label name remains displayed in the chart?
Dear Leila, I do not get the same results as in your example. At least with regard to the x y scatter graph. As soon as I select Change Chart Type for the x y scatter the x-axis values dramatically change. For example, the x-axis with three months of 2022 automatically starts from 1905 when the reference year should be 2022 for the minimum and maxim bounds. This change results in the x y markers appearing on the left of the chart and the rest of stacked column charge squeezed to the right.
please make a video how to insert Error bar in a stacked graph..
Sure - but what would use the error bars for?
Leila Gharani I want to show abundance of 3 species in 5 different site..so I can show abundance of 3 species in each site mean with standard error
Could you have not grouped a text box with the series of the stacked column chart so that it would become dynamic? Not sure if that would work. If I have Excel 2010, what makes a text box with a formula pointing to the labels different than method you employed?
Unfortunately you can't group with the stacks. You can group with the chart object, but the size of that doesn't change.
There is no major difference between the version I showed to the one you mentioned - it just takes a bit longer to click on each text box and add the formula in. The one I showed, is also dynamic so if you have a new stack added in the middle, the stack label is automatically taken over, whereas with Excel 2010 you have to manually create the formula reference.
Thank you for these amazing technique! But i stuck at how can you add X-Y series into the chart? I tried but it's always add only one series.
Yes - it only adds one series if the last series you had was a column or line chart. What you need to do after you add the one series is to change the chart type to a scatter plot - after you change to scatter plot you get the option for both x and y - you need to go back to "select data" and edit the series. You're then see the options available....
Bingo! I've just did it last night! Thank you
That's great Pham!
Good trick but it is more confusing as I need to infer that Blue represents Europe for all the series by looking at the labels which are next to the last series only. So more guess work. First chart with the legend still feels better.
in this chart how would I do below:
I want Label (Country name) in each Year's Bar
+++
Below to each country name I need to show its %contribution in that particular year
(Example : for 2017 Bar Labels I need are : Europe 74%, America 19%,...)
Plz plz reply.
Hi Leila, I need a small help with graphs, can I email you my requirement please ?
Never ever ever will I say I’m “proficient in Excel” again
Haha. There's always more to learn :)
Is there anyway to doing this for a 100% stacked chart?
Yes - you can use the same method. It works for 100% stacked chart as well.
Leila Gharani I'm finding it difficult to calculate X values for 100% stacked chart, by the way I'm doing it for balance sheet chart, I'll try this method for dummy data. Hope I'll find the problem. Thanks for prompt reply. Your videos are really really and really useful. Keep up the good work.
X values depend on the number of categories you have on the horizontal axis - I assume you have a 100% stacked column chart right? how many categories do you have on the x axis? You just need to add 0.5 to it. If you download this workbook from the link in the descriptions and then change the dummy values to represent a 100% stacked chart, you'll see that you don't need to make any adjustments to the logic shown. Now unless you have a 100% stacked "bar" chart and not a column chart, then it's a different story....
Glad you find the videos useful Muhammad :)
Leila Gharani my chart is horizontal, so in my case X values are Y values in your case.
Can I have your email ID? So that we can exchange new ideas?
Cross-referencing shall be 100% avoided in presentations and presentation-like reports.