Excel stack column: how to remove empty labels?
 

I am asking to the experts the following problem that I am unable to
solve.
I have the following worksheet:
Classes:_______Lecturer1(hours):________Lecturer 2(hours):
macro_______________20______________________30
micro_______________40______________________50
literat._____________0______________________20

I need to show with stack bars that lecturer 1 (first stack bar) has
20+40 hours and the label of any piece of the bar should have the name
of the classes. So the piece of the bar with height 20 should contain
the text "macro", the other piece of height 40 the text "micro", etc.

With Excel the latter can be obtained by showing the values of series.
Yet the chart shown contain also the labels of the series that have no
value in that column. Example: in the first stack Column on the top
there is also the text "literat." though there is any piece of
"literat." column in it.
How do I avoid (apart to cancel it manually) that the label "literat."
(or for the matter of any other class) appear in those columns that do
not contemplate hours of that class?
Thanks
AR

Excel stack column: how to remove empty labels?
 

Hi,

The series and category data labels in a stacked column will display
even if the cell is empty or contains NA().

One way around this would be to use helper columns of data and plot this
extra data as line charts. The formula would output empty or zero values
as #N/A, which will suppress the data labels in a line series.

So assuming your example data is in the range A1:C4 put the following
formula in E2 and copy down 2 and across 1.

=IF(B2=0,NA(),SUM(B$1:B1)+(B2/2))

Now add 3 extra data series to your chart. Change each one to a line
chart. Apply data labels to the lines instead of the stacked columns.
Format the lines to have no line or marker. You will also need to remove
the extra legend entries.

Cheers
Andy

wrote:
I am asking to the experts the following problem that I am unable to
solve.
I have the following worksheet:
Classes:_______Lecturer1(hours):________Lecturer 2(hours):
macro_______________20______________________30
micro_______________40______________________50
literat._____________0______________________20

I need to show with stack bars that lecturer 1 (first stack bar) has
20+40 hours and the label of any piece of the bar should have the name
of the classes. So the piece of the bar with height 20 should contain
the text "macro", the other piece of height 40 the text "micro", etc.

With Excel the latter can be obtained by showing the values of series.
Yet the chart shown contain also the labels of the series that have no
value in that column. Example: in the first stack Column on the top
there is also the text "literat." though there is any piece of
"literat." column in it.
How do I avoid (apart to cancel it manually) that the label "literat."
(or for the matter of any other class) appear in those columns that do
not contemplate hours of that class?
Thanks
AR


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Excel stack column: how to remove empty labels?
 

Thank you very much Andy Pope. It is not really immediate. But by trial
and error I hope to be able to implement your precious suggestions.
Thanks again.
AR