Break down totals in chart

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am currently using Excel 2002 on a Windows XP system. I have a column chart
that displays a list of totals. How do I make the totals break down to show
all of the items in a different color within each bar on the chart? Do I need
to make a differnt series for each bar?
TIA
George
 
Hi, George,

What you want is a stacked bar chart.

If, for example, your data looks something like this:

Bar 1 Bar 2 Bar 3 Bar 4
Entry 1 10 21 29 42
Entry 2 20 31 39 12
Entry 3 30 41 9 22
Entry 4 40 11 19 32
Total 100 104 96 108

Select the data (do NOT include the Total row), and
create a stacked bar chart (select Bar Chart from
standard chart types, and stacked bar chart as the
subtype). In Step 2 of the Chart Wizard, select Series in
Rows.

That will give you your same bars, but they'll
automatically show each separate value within each bar as
a separate color, and let you show the data labels with
each of their individual values.

If you also want to show the Total value on each bar --
you'll have to use a workaround (stacked bars don't do
that automatically) -- but you have a couple of super-
easy options:

First option (I prefer this one):

You can create linked text boxes for the totals. Just
create a text box on the chart (from the Drawing
toolbar) - and with the text box selected, type = in the
formula bar. Then, browse to your first total cell in the
data, and select it. Press Enter and your first total
value will appear in that text box. You can move, format,
resize the text box as needed -- and repeat for each
total. Because they're linked, they'll update
automatically if your data changes, just like normal data
labels.

Second option (a bit more work, but you might prefer this
instead of adding drawing objects to your chart, as I
suggested above):

1. Include the Total row as a series when you create the
chart.

2. Format the total series to have no line and no fill --
and format that series' data labels to appear on
the 'Inside Base' (on the Alignment tab of the Format
Data Labels dialog box).

3. Change the y-axis (the value axis, which is the
horizontal axis in a bar chart) to accomodate just your
four series that contribute to the totals (i.e., in the
example data I used above -- I would double-click the y-
axis to open the Format Axis dialog box, and on the Scale
tab of that dialog box -- I would type something like 120
as the Maximum Value, because that's more than you need
for all existing bars. The only glitch here is that, if
your values increase, you might have to change this
Maximum Value on the Y-Axis scale to accomodate your new
totals. (Once you manually set the scale, it won't
automatically change to show changes in your data).

Hope that's helpful!

Best,
Stephanie Krieger
author of Microsoft Office Document Designer
email: MODD_2003 at msn dot com
blog: arouet.net
 
Here's a nicer way to get the total. Add the Total series when creating the chart
(as in your second approach), but select just the totals series, and change the
chart type to a line (Chart Type on the Chart menu). Double click on the line and
choose None for marker and line on the Patterns tab to hide it, and on the Data
Labels tab, add value labels. Double click on the labels, and choose the Above
position on the Alignment tab. This way you avoid messing with the Y axis scale.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Nice idea -- thanks, Jon!

Stephanie
-----Original Message-----
Here's a nicer way to get the total. Add the Total series when creating the chart
(as in your second approach), but select just the totals series, and change the
chart type to a line (Chart Type on the Chart menu). Double click on the line and
choose None for marker and line on the Patterns tab to hide it, and on the Data
Labels tab, add value labels. Double click on the labels, and choose the Above
position on the Alignment tab. This way you avoid messing with the Y axis scale.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______



.
Click to expand...
 
Back
Top