Hi Leslie,

If this is a continuation of the self-sorting problem with stacked and
non-stacked columns, here's a solution that will sort each column left-
to-right with the largest ones on the left. In addition, for any
columns that are stacked, the rows are sorted in descending order, so
that the largest values are at the bottom of the chart.

Suppose the raw data are in G7:O10:

G H I J K L M N O
7 11.90%3.50% 2.90% 3.30% 4.70% 2.90% 6.80% 8.40% 6.40%
8 3.70% 0.80% 42.80%
9 0.30% 1.20%
10 0.40%

[Hopefully, the above alignment will work out]

Then, first we sort the data in each column.

In G14, enter =IF(ISNA(INDEX(G$7:G$10,RANK(G7,G$7:G$10))),"",IND EX(G
$7:G$10,RANK(G7,G$7:G$10))). Copy G14 to G15:G17. Copy G14:G17 to
H14:O14.

Next, we get the sum of each colum. In G13, enter the formula =SUM
(G14:G17). Copy G13 to H13:O13.

Next we sort the data based on the total of each column. This is
basically what I posted some time back.

In G19:O19 enter the numbers 1,2,...8,9
In G20 enter the formula =INDEX($G$19:$O$19,RANK(G13,$G$13:$O$13,0))
Copy G20 to H20:O20.

In G21 enter the formula =INDEX($G$13:$O$17,ROW()-ROW($G$21)+1,MATCH
(COLUMN()-COLUMN($G$21)+1,$G$20:$O$20,0)) Copy G21 to G22:G25. Copy
G21:G25 to H21:O21.

Plot G22:O25. Use G21:O21 as labels for the top series.

Self sorting column chart. Largest columns to the left. In each
column self sorting stacks. Largest values at the bottom.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Jon,

The chart I'm concerned with has columns only. Six of them are based on one series only, so I've described those as "simple" columns. The remaining three are based on either two, three or four series, so I've described those as "stacked" columns. The chart type I selected in the chart wizard was the "stacked columns" subtype. However, I arranged the data in the source worksheet in an unusual staggered fashion
which permitted the mixing of simple and stacked columns in the one chart. I discovered that method from others. You may have been one of those others or, if not, you at least have in the past drawn people's attention to the method and pointed them to other sources for it.

Two more things about the chart are that: first, the extreme righthand column, which is one of my three "stacked" ones, is plotted against a secondary y axis; and, secondly, to be able to show data labels at the top of all of my columns, including the "stacked" ones, I created invisible columns above the visible ones and of the same value as the visible ones and put data labels inside the bases of those
invisible columns.

When I try to manipulate one of my "stacked" columns on the primary y axis by changing the plot orders of the series formulas of the series involved, the stacks jump around within the column just as desired.

However, when I try to manipulate my leftmost "simple" column on the primary y axis by changing the plot order of its series formula from 1 to 2, the change in number shows up (and the next column's plot order, that column also being a "simple" column, changes to 1), but the columns stand still.

Perhaps the chart has some peculiarity of construction which prevents me from switching simple columns by changing plot orders of their series formulas. If so, I'll give up on the idea of automating movement of columns or parts of columns in the chart.

Leslie