By default, Pivot tables share the same pivot cache. If you want two pivot tables with different groupings, you need to make sure that you create two different pivot caches. These will take up more memory, which is why Excel defaults to using one pivot cache.

In Excel 2003 pivot tables were created with the Pivot Table Wizard. This Wizard contains a question in the first step that only appears when you create more than one pivot table. If you keep that option **unselected**, Excel will create a new pivot cache after issuing a warning.

In Excel 2007 or later, the pivot table wizard is not normally used, so this option is not readily available.

But here's the good news: You can open the Pivot Table Wizard with the keyboard shortcut `Alt` - `D` - `P`. Alternatively, you can add it to a custom group on the ribbon or put it in the QAT. It's in the "Commands not in the ribbon" list.

The keyboard shortcut comes from the Excel 2003 menu bars, Alt - D to open the Data menu, then P to select the Pivot chart wizard:

**Use a calculated field**

The best way I've found to achieve this is to add a calculated field to the table that produces the correct sort order, and then to hide that column.

The sort order field needs to work by scaling each sort column up or down so that no column's bit of the output sort value overlaps with any other.

So I scale OrderCount up by a big number, and total Value down by a big number:

```
= (OrderCount*1000) + (Value/1000)
```

For Jemaine's figures above, this will produce a value of `3000.18`

. Bret would be `3000.07`

, Murray/Brian `1000.09`

etc - the correct order.

If I wanted to add a third sorting condition I'd have to scale that down by an even bigger number:

```
= (OrderCount*1000) + (Value/1000) + (3rdCondition/1000000)
```

**Adding the Calculated field**

To actually implement this in the pivot table, I need to add a column to my data table so that the calculated field can count orders. I've simply added an `OrderCount`

column and set the value to `1`

for every row:

Now we can add the calculated field. Select this option from the ribbon and enter the formula:

My pivot table now looks like this, sorted correctly.

**Tidying Up**

This SortOrder column might confuse other users, so I can just hide the whole column.

However, I often prefer not to hide columns (personal preference) so to make this column unobtrusive I can rename the column to

(ie a blank space), and format the number column for the SortOrder values to a custom format of `"";"";""`

. If Excel autoadjusts column widths this makes the column barely noticeable.

## Best Answer

Try this:

On the actual pivot table, right click the columns months, select

`Ungroup`

Let me know if this does not work and I will remove this answer.