PivotTable Custom Sort Orders in Excel

Sometimes you want to sort a list or PivotTable in a special way that doesn’t fit the usual ‘A to Z’ or number order.

The most common need for this are days of the week or months in a year.  Neither of those orders make sense to a literal computer.

Excel has a Custom sort option available generally and it’s also available in PivotTables.

Right-click on a column/row to be sorted and choose Sort | More Sort Options | More Options.

Uncheck the ‘AutoSort’ check box.  That will enable the next option.

‘First key sort order’

The pull down list has the available custom sort orders.  Some are provided by Microsoft plus any you’ve added yourself.

‘First key’ doesn’t mean a physical keystroke.  It refers to the index key used by computers to quick sort anything.

Make your own sort order

To create your own sort order, go to Home | Sort & Filter | Custom Sort then click Add.

You can manually add a list by clicking the Add button

Add Excel custom sort order

It’s often easier to make the list in a separate worksheet then import that list.  Do this from a similar dialog box in a different part of Excel.  File | Options | Advanced | scroll down to General | Edit Custom Lists.

 

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.