• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to chart stacked bar with headers in the graph?

Z

zwj808

New Member
I have an Excel file containing multiple series of data(in rows, one row for a series only), and each of the series has different headers and length of data.
With such a structure of data, how should I chart all series with a horizontal stacked bar having headers on corresponding bars as shown like the image below?
Currently, I have to put headers on one by one using separate textboxes.83291
 

Attachments

  • Book1.xlsx
    15.3 KB · Views: 2
zwj808
... by using Labels.
There are two samples
above - I left Your version too ( there are some challenges, as You'll notice ).
below - I used only labels.
 

Attachments

  • Book1.xlsx
    21.8 KB · Views: 5
vletm said:
zwj808
... by using Labels.
There are two samples
above - I left Your version too ( there are some challenges, as You'll notice ).
below - I used only labels.
Click to expand...

Hi vletm,
Thanks for the demonstration. First time knowing the data label options.
But, do I have to change label reference column by column? or there is other faster way to do that?
 
zwj808
What do You mean?
I gave two samples.
The 1st based Yours - with some challenges
and 2nd one without those challenges.
You should able to send a sample from Your needs?
.. as well as sample from Your used data.
 
vletm said:
zwj808
What do You mean?
I gave two samples.
The 1st based Yours - with some challenges
and 2nd one without those challenges.
You should able to send a sample from Your needs?
.. as well as sample from Your used data.
Click to expand...
Thank you. I've figured out how to replicate labels from the example you provided.
But I got another question, is there a better way to hide labels if corresponding values are zero?
For example, if value of label A (now is 100) becomes 0, then label A goes off the chart, because now if set the value to 0, label A stays on the chart?
Now my solution is to create another table for all labels (like you did), then put IF function to if value = 0, then put "" in the table of labels.....
 
zwj808
Because, Your question based my samples, I can try to reply.
All depends of Your data ... its layout - how to use those labels.
By using as my samples - there will be manual tasks to do. Is it okay?
Why do would like to hide some data, if those are ... zero? Aren't those real values too?
 
vletm said:
zwj808
Because, Your question based my samples, I can try to reply.
All depends of Your data ... its layout - how to use those labels.
By using as my samples - there will be manual tasks to do. Is it okay?
Why do would like to hide some data, if those are ... zero? Aren't those real values too?
Click to expand...
Yes, it is still much better even with some manual steps.
Why I want to hide zeros is just want the chart looks better, because I found if there are zeros, labels stay on the chart, stacking each other, like the arrow pointing.
 

Attachments

  • WeChat Image_20230313145959.png
    WeChat Image_20230313145959.png
    30.3 KB · Views: 6
zwj808
Your snapshot ...
As it was a sample, that 'orange' label range has missed to modify to show correct values.
Of course, You can write a formula for those labels or
as You've done with Series 1's 4th value ... left it empty.
If Your data's layout would be other, then those label would be smoother to use.
 
vletm said:
zwj808
Your snapshot ...
As it was a sample, that 'orange' label range has missed to modify to show correct values.
Of course, You can write a formula for those labels or
as You've done with Series 1's 4th value ... left it empty.
If Your data's layout would be other, then those label would be smoother to use.
Click to expand...
oh right. Thanks a lot.
That's cool.

I am good with that, thank you very much.
 
zwj808
One more point ...
Is it safe to hide those zero's?
If there are values like 450000, 1 and 100000
... then should You hide 1 or what to do?
Or if there is some huge values 10000, 2, 3, 4, 5, 6 and 30000 ... the case is same ... or is it?
Be careful.
 
vletm said:
zwj808
One more point ...
Is it safe to hide those zero's?
If there are values like 450000, 1 and 100000
... then should You hide 1 or what to do?
Or if there is some huge values 10000, 2, 3, 4, 5, 6 and 30000 ... the case is same ... or is it?
Be careful.
Click to expand...
Yes, it is a good reminder, and I've figured that I would keep the label in chart even it has a very small but valid number.
And in the case of my actual data, 0 means nothing for that category, so it is safe to hide.
 
You must log in or register to reply here.
Back
Top