Search Not Just Numbers

Wednesday 3 April 2019

Excel Tip: Sorting a list - an introduction

Sorting a list is a very common use of Excel but there are a few things to consider when you do, and a few extra features that you may not be aware of.

The first task is selecting the data you want to sort.

If your data is in a table, if you select any cell in the table, Excel will assume that you want to sort the whole table.

If the data is not in a table, Excel will still guess what you want to sort, however I think it is better to pro-actively choose, rather than assume that Excel will get it right!

Make sure you highlight all of the rows in the range you want to sort. You can highlight whole columns by clicking on the column letters, which will ensure that all rows are included (not a good idea if you have other data below your list though).

Also, make sure that all the columns in your data are included, otherwise you could end up irrevocably breaking up rows of data, by sorting some columns and not others!

Once you have selected your data, click one of the sort buttons.



Clicking either of the two small buttons will sort based upon the first column of your data, either ascending or descending. The larger sort button (ringed above) gives you a lot more flexibility.

Clicking this will reveal the following box:


The first thing to check is that the tickbox "My data has headers" is correct, as, if ticked, this will treat the first row of your range as headers and not include it in the sort.

You can then choose which column to sort on. This is a dropdown of the column headings (if the tick box is ticked), or the column letters if not.

For the purposes of this introduction, we will assume that you want to sort by the contents of the cell (Cell Values in the Sort on dropdown), then in the final box you can select the order you want to sort in. The options will change, depending on the type of data in the column being sorted, e.g. A to Z for text, Smallest to largest for Numbers, Oldest to newest for dates, and all of their opposites.

Then click OK and your range will be sorted.

In a future post we will dig a bit deeper into the sorting options, including how to sort on multiple columns.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".