Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
Check out Bill's new book on Charts and
Graphs for Microsoft Office Excel 2007
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?
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?
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?
When I try to turn the totals column into a line, it turns all my data into lines.
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.