PivotTable Custom Sort Orders in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

pivottable custom sort orders in excel 15664 - PivotTable Custom Sort Orders in Excel

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.

pivottable custom sort orders in excel 15665 - PivotTable Custom Sort Orders in Excel

You can manually add a list by clicking the Add button

pivottable custom sort orders in excel 15666 - PivotTable Custom Sort Orders in Excel

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.

 

subs profile e1563205311409 - PivotTable Custom Sort Orders in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address