MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Aggregate Totals on Column-Chart


Posted by Agraham on November 05, 2001 6:32 AM

I have a series of numbers I am plottint - i.e.:

2001 2002 2003 2004
Catagory A $10 $11 $12 $13
Catagory B $15 $18 $11 $10
Catagory C $22 $18 $14 $10

I want to show the aggregate total at the top of each column. I don't want the individual catagory totals on the graph. Is there a way to do this automatically?


Posted by Dan on November 05, 2001 9:10 AM

Where are you having trouble? You can omit any table data from showing on a chart. Are you using the chart wizard to select the data to plot?

Posted by agraham on November 05, 2001 9:18 AM

I'm not having any trouble graphing the numbers. I want to report the total of each stacked column on the graph (i.e., in my example, I would have a column equal to $47 for 2001, $47 for 2002, $47 for 2003, and $33 for 2004). I can use the Data Labels to report the value on the graph, but it does so for each catagory. I only want the total. Is this possible?


Posted by Dan on November 05, 2001 10:03 AM

Sorry if this is a double-post!

Ahhh. I think I see now. You have a stacked column graph and you want to simply add the totals on the chart. You could try this:

First, you are going to need to sum the year categories in cells on your table. The chart won't do this calculation.

Next, include this total in your chart. What should happen is that the total will appear as the top category of each column, but don't worry about that right now.

Right-click on the colored part of the bar that corresponds to the total and choose "Chart Type". Then select the first Line graph.

You should now have the totals displayed as a line graph above your columns. Right click on the line and select "Format Data Series". Under the "Patterns" tab select "none" for both Line and Marker. Then under the "Data Labels" tab, select "Show Value". Then click OK.

Finally, right-click on one of the values and select "Format Data Labels". Under the alignment tab, select "Center" under "Label Position".

Does that get you what you want?


Posted by Dan on November 05, 2001 10:04 AM

Ahhh. I think I see now. You have a stacked column graph and you want to simply add the totals on the chart. You could try this:

First, you are going to need to sum the year categories in cells on your table. The chart won't do this calculation.

Next, include this total in your chart. What should happen is that the total will appear as the top category of each column, but don't worry about that right now.

Right-click on the colored part of the bar that corresponds to the total and choose "Chart Type". Then select the first Line graph.

You should now have the totals displayed as a line graph above your columns. Right click on the line and select "Format Data Series". Under the "Patterns" tab select "none" for both Line and Marker. Then under the "Data Labels" tab, select "Show Value". Then click OK.

Finally, right-click on one of the values and select "Format Data Labels". Under the alignment tab, select "Center" under "Label Position".

Does that get you what you want?


Posted by agraham on November 05, 2001 1:13 PM


I think I follow your logic. But.....

When I try to turn the totals column into a line, it turns all my data into lines.

Posted by agraham on November 05, 2001 1:19 PM

I was using a 3D chart - It works with a 2D. Thanks!

Posted by Dan on November 06, 2001 5:10 AM

If you were pretty set on using a 3d chart, you could probably get a similar effect. Instead of changing the totals columns to a line, try and just skip that step and leave it as a column but make it invisible. It may mess up your scale on the y-axis, but you can change that in the Format Axis property. It may take a little more work, but you could probably experiment a little and get what you need.