How do I fix my Stacked column chart with grand totals?

I have a series of data which includes different branches of the company (row
header) and different years (column headers). I want to create a stacked
chart with no labels or values shown, but with the data table at the bottom
of the chart.

Ideally, I would like the branches to be listed in alphabetical order (as
they appear in the original data worksheet). At the bottom of the data table
I would like to have the grand total (again, as it appears in the worksheet).
The glitch is that I do not want to have the grand total data included in
the chart (I only want it for information).

I have come up with a work-around solution to kind-of get what I want.
Instead of having the grand total at the bottom of the data table, I put it
at the top. I then select the grand total bars in the chart, select Format
Data Series, and choose 'none' for the border and area. I then change the
y-axis.

This work-around seems to do the job, except for two problems:

(1) I can't go back and undo what was done (e.g. I can't access the grand
total in the chart anymore;

(2) The grand total appears at the top of the data table, which is
counter-intuitive - usually people add up going down, not up.

Can anyone suggest another way to get Excel to do what I want it to? I
would greatly appreciate your help. BTW, we're using Excel 2000 in our
office, if that makes a difference.

Make the Grand Total series a Line chart instead of another column: select
the series, and on the Chart menu, click on Chart Type and choose a line
chart type. Then format this as an invisible series, with no lines and no
markers. This series is automatically placed below the column types in the
data table.

Data tables are notorious for their utter lack of flexibility. Tushar Mehta
talks about this and suggests an alternative, creating a table in a nearby
region of the worksheet, on his web site:

http://tushar-mehta.com/excel/newsgr...able/index.htm

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

"Jennifer" <Jennifer @discussions.microsoft.com> wrote in message
news:[email protected]...
>I have a series of data which includes different branches of the company
>(row
> header) and different years (column headers). I want to create a stacked
> chart with no labels or values shown, but with the data table at the
> bottom
> of the chart.
>
> Ideally, I would like the branches to be listed in alphabetical order (as
> they appear in the original data worksheet). At the bottom of the data
> table
> I would like to have the grand total (again, as it appears in the
> worksheet).
> The glitch is that I do not want to have the grand total data included in
> the chart (I only want it for information).
>
> I have come up with a work-around solution to kind-of get what I want.
> Instead of having the grand total at the bottom of the data table, I put
> it
> at the top. I then select the grand total bars in the chart, select
> Format
> Data Series, and choose 'none' for the border and area. I then change the
> y-axis.
>
> This work-around seems to do the job, except for two problems:
>
> (1) I can't go back and undo what was done (e.g. I can't access the grand
> total in the chart anymore;
>
> (2) The grand total appears at the top of the data table, which is
> counter-intuitive - usually people add up going down, not up.
>
> Can anyone suggest another way to get Excel to do what I want it to? I
> would greatly appreciate your help. BTW, we're using Excel 2000 in our
> office, if that makes a difference.

Thank you, Jon!