How to sort values based on given order of categories in Excel?

sort
excel

#1

Hi,

In my data range, Department is a variable. Now I want to sort data range by department but the order of sorting would not be ascending or descending. It should be as per given order.

Data Range:-

Code       Sales        Department
A001       1234           Sales
A002        3456          Admin
A006        4567          Ops
A007        8956          Support
A008        7895          HR

Now I want to sort above data range by department and order of sorting must be Ops, Support, HR, Admin and Sales. After Sorting data range must be look like.

Code       Sales        Department
A006        4567          Ops
A007        8956          Support
A008        7895          HR
A002        3456          Admin
A001       1234           Sales

Please help to do this.

Thanks,
Mukesh


#2

While working, few days back I also got stuck with similar problem. But guess what? The answer is quite simple. The steps stated below helped me in solving this problem:-

Step 1: Select the complete data table
Step 2: Go to Sort in the Data Tab.
Step 3: Select Column - Department, Sort on - Values and Order - CUSTOM LIST.
Step 4: Now, Click on NEW list to create the custom list and write values in LIST ENTRIES box with separated by comma, in the required sequence of Ops, Support, HR, Admin, Sales.
Step 5: Click on ADD
Step 6: Click OK.

You will find that the department has been sorted in the required order. Done!

Hope this helps!
Thanks!