Sorting Pivot Charts (and tables)

[Shumande]

How does excel order pivot charts and pivot tables and how can I re-sort them?

I often create pivot (bar) charts where each bar shows a particular measure for a dozen companies. Underneath each bar I put data about the company. For example, the bars might show average daily share volume (traded on the stock markets) and underneath I show the size of each company, its name and its ticker symbol.

Sometimes I want to order the companies/bars alphabetically and sometimes I want to order them by company size. I figured out how to change the order by dragging cells in the associated pivot table, but there must be some way to automatically re-order pivot tables and pivot charts. Anybody know?

Thanks very much in advance.

[eliW]

On 2002-04-24 22:50, Shumande wrote:
How does excel order pivot charts and pivot tables and how can I re-sort them?

I often create pivot (bar) charts where each bar shows a particular measure for a dozen companies. Underneath each bar I put data about the company. For example, the bars might show average daily share volume (traded on the stock markets) and underneath I show the size of each company, its name and its ticker symbol.

Sometimes I want to order the companies/bars alphabetically and sometimes I want to order them by company size. I figured out how to change the order by dragging cells in the associated pivot table, but there must be some way to automatically re-order pivot tables and pivot charts. Anybody know?

Thanks very much in advance.

Hello Shumande,
Yes there is a quite easy way to do this:

1) Assume, in column A company NAME, in column B company SIZE, in column C your DATA (measures).

2) Now open the PivotTable wizard and select all 3 columns, Put into ROW area both NAME, and SIZE fields and inDATA area of course the DATA field.

3) Click on NAME field twice, mark SUBTOTALS -NONE, proceed to ADVANCED option, and choose SORT - Acending and confirm.
Do the same on the second field - SIZE.

4) Exit the wizard and make your BAR chart on all 3 columns.

The X axis will be arranged according to the firs field that you put in the ROW area in th wizard (NAME).

To arrange according to the other field SIZE, just click on field, enter the wizard, switch between fields at the ROW area (by dragging) and exit. Your chart will be re-orderd automatically.

Hope that this is clear,
Eli

[Shumande]

Thanks Eli. In hindsight it was easy. I really appreciate your help.