How to Perform a Custom Sort in Excel

By | 31st March 2015

How to Sort in Excel

It is very easy to perform a custom sort in Excel. You have several options available, but the easiest is probably to click in the column you want to sort and press the Sort A to Z or Sort Z to A button either from the Sort & Filter group on the Home tab or from the dedicated Sort & Filter group from the Data tab:

How to use Custom Sort in Excel

In either case, the data in the column you are clicked in will be sorted in either an ascending or descending manner. As a point of interest, the one thing you should NOT do is to select the cells you want sorted – if you do this you run the risk that Excel will sort only that data and ignore the rest, making it useless.

Using a Custom Sort on More than one Field

The limitation with sorting in this way is that you can only sort one field at a time. If you want more than one column sorted you must perform a Custom Sort in Excel 2010. Simply use the Custom Sort button from the drop down under Sort & Filter instead.

custom_sort

This will open the Sort dialog box and allow you to add extra levels to the custom sort.

SortFilterDialog

Across the top of this box you will see a number of buttons for adding, deleting and copying levels and another that opens an Options dialog. Next to this is a small checkbox that should automatically be ticked if the data has headers, or field names, in the top row.

myDataHasHeaders

Underneath these are a number of small windows from which you can make selections on how the custom sort should arrange the data. The first window labelled “Column” shows all the available fields in a drop down list, and the next one, labelled “Sort On” offers to sort not just on Values, which is the default, but also on cell colour, font colour and cell icons – these refer to the icons or colours applied if conditional formatting has been used.

SortDropDowns

The third window labelled “Order” has the direction of sort, from smallest to largest or vice versa, or by sorting according to a custom list. Excel 2010 is smart enough to distinguish between numerical and textual fields, so in this field you will get A to Z or Z to A if the field contains text rather than a number.

Perform a Custom Sort on Multiple Fields

Making a selection from each of these allows you to sort the data in any order you wish. For example, if I wanted to sort the data by country I would choose these options:

FirstLevelSort

However, I can add as many additional levels of sorting within this order as I want.

So I may decide that I want to see the Airport field sorted next and then the Holiday Type field:

Sort3Fields

I can carry sorting if I want to – the old limit of 3 fields has been removed.

If you would like to read more articles like this one on performing a Custom Sort, or watch tutorial videos on Microsoft Office visit our main website: http://www.netresulttraining.co.uk.

To see other posts on Sort & Filter in Microsoft Excel visit our blog

 

2 thoughts on “How to Perform a Custom Sort in Excel

  1. Gina

    I can only sort 3 columns in my spreadsheet. What am I doing wrong?

    Reply
    1. David Post author

      Hi Gina, it sounds like you are using an older version of Excel. I don’t think you are doing anything wrong, but in the 2007 version the limit of sorting only 3 columns was lifted and ever since you have benn able to sort on as many columns as you want. Check which version you’re using – if it’s a work computer you should ask for an upgrade 😉

      You might also consider filtering rather than just sorting, or better still, turn your data into a table for really good control… Check out my tutorials for more info.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.