Data Sorting in Excel

Using Data Sorting in Excel including customised sort orders

• 5:31
• Skill Level: 3
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Data Sorting in Excel

In this segment you’ll learn how to do Data Sorting in Excel and how to develop customized sort orders

In this example you have a list of Cities, Sales People in those cities, the Products they sold, and the Sales Total. You’d like to sort it in various ways, to perform a sort,

• you highlight all the cells that you want sorted,
• you click Data
• and then Sort,
• and this will pop up, and what it asks you is, what must it sort by and which way must it go. ascending or descending.
• And you’ll see here that my list has, and in this case its automatically guessed that there’s a header row here,
• you could say no header row and you’ll see it tries to sort everything, and in this case this line would here would also be mixed up,
• if you click here it will realize that this is also a header row and it will also sort these out.
• So for example we can sort by, lets say City,
• and we want to go in ascending order,
• if we click ok you’ll see that all this data is sorted in alphabetical order,

It is important to note that when you highlight the cells, you must highlight all the cells you want sorted. So for example

• if we only highlighted that section
• and we said Data
• Sort,
• and this time we work on Sales Person,
• in ascending order,
• if you watch this first row here you’ll see that this is JNB, Trevor, Standard Product, for 4000,
• when we click ok,
• you’ll see that although the Sales have stayed the same the information has moved around and they are no longer in the correct rows,
• so if we undo,
• so you must remember to highlight the entire section before you sort,

U can also perform multiple sorts,

• so we’ve highlighted our cells ,
• if we go to Data
• Sort
• you can say well, I first want to sort by lets say Sales,
• and I want it to be in descending order,
• and then I want to sort by the Sales Person in ascending alphabetical order,
• and then maybe by the City
• again in ascending order,
• and if we click ok,
• you’ll see that its listed now first by Sales, then by Sales Persons name if there’s a tie, and then by the City
• if we go back to the Sort box, you may have noticed that there’s and options button here,
• if you click it, you’ll see that you can set the first key sort order between normal,
• but if you click on it there’s a whole bunch of other possible orders,
• so in this case there’s days of the week, month etc, so using this feature you can set up to have a personalized sort order,

So for example, how would you do this, if instead of doing this in alphabetical order, what you would like to do is say have New York, then London, then Johannesburg, then Paris. To customize the sort order,

• cancel this
• and go to Tools
• Options, and you’ll see there’s a tab here called Customize Lists
• if you click on it you’ll see all those lists we’ve shown there,
• and what you can now do is add your own personalized list, you can now add your own customized list,
• click in the list entries, and type the order you want it to be in. So we said we first want New York, then London, then Johannesburg, and then Paris.
• If you click add, you’ll see your list now appears here
• If we say ok
• We highlight the cells again,
• and say Data
• Sort
• now lets make that City
• remove that one
• remove that one
• Well make it ascending for the City
• and well say Options
• and when you click in here
• you go to the bottom you’ll see that our list is now there, you’ll click on that list,
• you’ll say ok, and if you watch here,
• when you click ok, you’ll see that our new order has come in, there’s no alphabetical, it is the order we recommended.
• New York, London, Johannesburg, Paris