Excel: Pivot Tables SORTING

Keeping the formatting for pivot table data

Pivot Tables in Excel are very very useful. But they have a few annoying quirks, especially to do with formatting. For some general formatting tips, see the attached PDF from:

http://www.journalofaccountancy.com/Issues/2012/Oct/Excel-PivotTable.htm

Sorting pivot table data to preserve original data order

Pivot Tables in Excel have an annoying "feature" where they are automatically sorted by whatever mechanism that Excel sees fit, including by month (but not by date) or as numbers, or even as custom lists.

If you need to sort data by the original "data source order", there are a few ways, none of which work:

So, how can you sort Pivot Tables properly?

Here's an example:

Say if I have the following simple example data, and a pivot table created from it.

BUT I want to sort the internal rows by North, South, East, then West.

Right now, if you look under Oranges and Pears (the red boxes), it orders them in alphabetical order (East - North - South - West). Not very nice! I want it ordered in the original order as shown under "Sales Region" (i.e. North - South - East - West)

Here are the steps:

1. The trick is to create a new column in your source data, its ordinal number. You can use a formula like "=row(A2)" to return the current row. In the picture above this is the yellow highlighted column called "Ordinal".

2. Drag the Ordinal field into the "Σ Values" section of the Pivot Table. It will turn into "Sum of Ordinal".

3. Change this field from "Sum of Ordinal" to "Min of Ordinal" (to make sure that Excel uses the first instance that it appears):

4. And now to sort! Click in the Pivot Table, then right-click on North, then Sort, then More Sort Options:

5. Change it to "Ascending (A to Z) by: Min or Ordinal":

6. Click More Options.

7. Change AutoSort to "Sort automatically every time the report is updated".

• 1. Click OK. The pivot table is now sorted correctly.

Some more helpful hints concerning formatting Pivot Tables is below: