Showing the "total" value with a stacked BAR chart

  • Thread starter Thread starter Anne
  • Start date Start date
A

Anne

Does anyone know how I can get excel to display the total
in a stacked bar chart? I tried the solution that
someone posted for a stacked column chart, but that
doesn't seem to work with stacked bars. The third
series, representing the total, just keeps plotting
itself vertically while the other two series plot
horizontally. The result, as you can imagine, is a mess
and not what I was going for at all.

Thanks, Anne
 
Hi Anne

You can add a third 'invisible' series with stacked bars as well a
stacked columns, but to avoid the additional series orienting itsel
horzontally, you'll have to use an invisible bar, not a line. This ha
a couple more steps:

Add a third series to the chart whose values are the sum of the colum
elements
Right click on the new series an set the series axis to 'secondary'.
Right click on the series again, select format data series, select th
data labels tab, check 'value'
Right click on the series again, select format data series, select th
patterns tab. set line to 'none' and pattern to 'none',
Delete the secondary axis
If necessary, delete the additional series from the legend

The attached is an exampl

Attachment filename: stacked bar.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46078
 
You can try the following:

If your values are located in A1:C10, with column A bearing the label
and columns B and C containing the series to be stacked, then ad
column D (Total), where you type =sum(Bx:Cx) to sum the value columns.

Create a staked bar graph for A1:D10
Click on data series corresponding to column D - Format Data Seires
Axis, and choose secondary axis

Now add a data label to this series - with value
Choose the data series again ->Format Data Series->Patterns ->Set lin
and area to none.

Clear the secondary axis and teh legend entry.

- Leni
 
Hi Anne -

To embellish Nicky's and Lenin's solutions to allow the axis scaling to
work automatically, we'll use one more column. Let A be the category
labels, B and C be the stacking values, D be a column of zeros, and E be
the sum of B and C. Make your stacked bar chart with A:D, then use Rob
Bovey's Chart Labeler or John Walkenbach's Chart Tools to add the data
in column E as data labels to the series of zeros in column D.

- Jon
 
Back
Top