I work in the Auto Industry where I have to create a Pivot Table based on Auto information each month. The Sort Order of the Pivot Table needs to be

Model Series
Model ID Code
Trim Level

This is the order of sort if the data is sorted through Excel because it allows for all Accord 2 Door, 4 Cylinder to be followed by 4 Door 4 Cylinder. I also use the Model ID code to make sure the Trim levels sort properly since the Trim level itself doesn't sort properly based on their names but I don't want the Model ID Code to show or stay in the Pivot Table. The Pivot Table tries to keep all 2 Door Accords together whether they are 2DR 4CYL or 2DR 6CYL but I need to Cylinders to stay grouped.

Then I can't seem to sort by Model ID code without it actually showing in my Pivot table. My end result should be something like the below example but Automatically sorted by the Pivot and not manually by me.

Accord    2    4    LX-S     CVT
Accord    2    4    EX     CVT
Accord    4    4    LX     CVT
Accord    2    6    EXLV6  AT6
Accord    4    6    EXLV6N AT6 

The data used to sort the Trim level terms are called Model ID codes that sort nicely in Excel, for instance in the Accord 2DR 4Cyl:


These Trims would not naturally sort in this manner since their letters might push them down. This codes needs to be used to sort properly but not viewable in the Pivot Table.

I have been sorting in Excel and then manually removing the column and moving things around to make the table appear in the correct order. Any help with this is GREATLY appreciated :-)

  • Thank You DavidPostill, I forgot about how it wouldn't list without doing what you did. Thank you for your help in the formatting of my question. – Rebecca Apr 19 '16 at 20:58

If you are using a newer version of Excel, it sounds like you should be using a Table instead of a PivotTable.

Here is some example raw data, based on your comments in the question ...

enter image description here

.. click anywhere in the dataset, then select the Insert Ribbon. Click on Table ...

enter image description here

... then click on the Home Ribbon. Click Sort & Filter, choose Custom Sort ...

enter image description here

... add and modify criteria, in the order you want them. Above, I tried to follow some of the comments from your question. Finally, just hide the column you don't want to appear in your report by selecting the column, right click, and choose "Hide". ...

enter image description here

... Adding data and re-applying the filters afterwards is also pretty straight forward.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.