{"id":15,"date":"2012-07-15T22:11:41","date_gmt":"2012-07-15T14:11:41","guid":{"rendered":"http:\/\/excelchamp.net\/?p=15"},"modified":"2022-07-11T06:27:44","modified_gmt":"2022-07-10T22:27:44","slug":"not-ascending-not-descending-just-plain-custom-sorting-in-excel","status":"publish","type":"post","link":"https:\/\/www.excelchamp.net\/not-ascending-not-descending-just-plain-custom-sorting-in-excel\/","title":{"rendered":"Not Ascending… Not Descending… Just Plain Custom Sorting in Excel"},"content":{"rendered":"

It is pretty easy to sort data in Excel.<\/p>\n

The sorting option<\/strong> is available on the Home<\/em> <\/strong>Tab of Excel 2007 or Excel 2010. See image below from Excel 2010.<\/p>\n

\"Home<\/a><\/p>\n

Just select the data, and click on the Sort & Filter<\/strong> Button on the Home<\/em> <\/strong>Tab.<\/p>\n

\"Sorting<\/a><\/p>\n

\n

You can sort A to Z (Ascending order)<\/strong> of the selected column, which is the default\u00a0Sorting option<\/em><\/strong> in Excel 2003, 2007 & Excel 2010<\/strong> too.<\/p>\n

But you could also sort in the\u00a0Descending\u00a0Order first (Sort Z to A), <\/strong>just by selecting this option from the Sort<\/strong> button on the Home Tab.<\/p>\n

Between these two Ascending & Descending options, you can get almost all kinds of data sorted.<\/p>\n

However, you will come across situations, when neither of these options will give you the desired result. This happens when you want to sort in a particular fashion – which is neither ascending nor descending.<\/p>\n

What do you do then?<\/p>\n

Here’s where Custom Sort<\/strong> comes into play. This hidden gem has been available in Excel since ages (a.k.a. since Excel 95, 98… days). However, most people are stumped when it comes to using it.<\/p>\n

Say for example, that Department wise Spending are provided in an Excel file below.<\/p>\n

\"Sorting<\/a><\/p>\n

You want to sort on departments, and you want them in this order listed below.<\/p>\n

    \n
  1. Sales<\/strong><\/li>\n
  2. Marketing<\/strong><\/li>\n
  3. Service<\/strong><\/li>\n
  4. Human Resources<\/strong><\/li>\n
  5. Accounts<\/strong><\/li>\n
  6. Information Technology<\/strong><\/li>\n<\/ol>\n

    Clearly, this can’t be done using Ascending Sort or Descending Sort.<\/p>\n

    Custom Sort<\/strong> comes to the rescue.<\/p>\n

    Go to Sort & Filter<\/strong> button on the Home<\/strong> Tab of Excel 2010. Click on Custom Sort<\/strong> from the drop down menu. A new popup window will open.<\/p>\n

    \"Custom<\/a><\/p>\n

    If your data has headings, you can select the\u00a0check box\u00a0“My data has headers<\/strong>“. If you do not have column heading, then do not choose the “My Data has headers” option.
    \n\t\t

    \n\t\t\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t\t
    \n\t\t\t
    \n\t\t\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t

    Want to Improve Your Excel Skills?<\/strong><\/span><\/p>

    Learn the Key Features of Excel Quickly & Easily, by <\/span>Joining the
    Online Training<\/strong> on <\/u>Basic \/ Intermediate Excel<\/u>.<\/strong><\/a><\/span><\/p>

    To Get Most out of Excel, Learn the\u00a0 Pivot Table techniques in our
    Pivot Table Masterclass Training<\/a><\/u><\/b>    !<\/strong><\/a><\/span><\/p><\/div>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t
    \nIn the Sort by Column<\/strong> dropdown, choose Department as the option to sort on.<\/p>\n

    In the Order dropdown<\/em><\/strong>, choose “Custom List…<\/strong>“.<\/p>\n

    \"Custom<\/a><\/p>\n

    Click on NEW LIST<\/strong>, and press Add<\/strong>.<\/p>\n

    The cursor will move to the Empty Box in the popup. Key in the list of departments here – one per line, in the SORT order you need. Then Click OK.<\/strong><\/p>\n

    \"List<\/a><\/p>\n

    Click OK on the Custom List Popup, and close all\u00a0pop ups.<\/p>\n

    You will now notice that the list of Departments has been sorted, as per your specified order<\/strong> – which was neither ascending nor descending.<\/p>\n

    \"List<\/a><\/p>\n

    As you can see, using the Custom Sort is pretty easy. In fact, Microsoft Excel has been using this trick to sort Months, Weekdays, Quarters<\/strong> etc. since ages too. It’s time you learnt about this nifty technique too.<\/p>\n

    Do try it out, and let me know about your experience with Custom Sort. I’ll be glad to receive your comments and suggestions.<\/p>\n

    Additional Resources:<\/strong><\/p>\n