Study smart | Excel super-stars
How to use the Excel Sort function
by Traci Williams | excelace.co.uk
Introduction
Sort is one of the most commonly used functions in Excel as it is so obvious what it will achieve, i.e. Sort data into alphabetical / numeric / date or colour order.
It is super simple to use and can help get your data into a more organised and structured order, to work with more effectively.
This article will show you how to use the function, both simply and in a more advanced way and also discuss some of the pitfalls to be aware of.
Basics of Sorting in Excel
The first thing to do when using SORT is to select the data that needs to be sorted. This is THE most important step as making any mistakes here can really mess up the data.
e.g. In the image below, the ‘Starting Date’ column has not been selected, so if Sort is applied the data in this column will not be sorted with the rest of the data.
Top Tip
Use the scroll bars at the bottom and right of the screen to ensure that ALL rows and columns have been selected before applying Sort
It is also important to make the first row of the selected data, the row that includes the column headers (as in the above example). From ‘Data’ ribbon, select ‘Sort’ icon:
There are now 3 options to select:
Sort By – This will be the column
Sort On – This will be the criteria to use
Order – This will be the order to apply
This is how they appear:
In this instance, as the first row selected in the sort range contained the column headers, Excel has include them as the list to select from. This can make it far easier to make your selection when there are LOTS of columns to choose from. Also, Excel will keep this row constant on place, and not use it as part of the data to sort.
Next, we will make a selection in the ’Sort On’ section:
The options are: Cell Values / Cell Colour / Font Colour or Conditional Formatting Icon. ‘Cell Values’ is the default option, and the others are self explanatory.
Next, we will make a selection in the ’Order’ section:
The options here are: A to Z, Z to A or Custom List. Excel knows that the ‘Department’ column contains text. hence the options are alphabetical.
These are the options that would appear if columns containing Date or Numbers had been selected instead:
I have selected the following options, then clicked OK and this is how my data now appears:
The Data has now been sorted in Ascending alphabetical order of the Department column.
Advanced Sorting Techniques
It is also possible to Sort by more than one column at a time, so for example, I may want to see the above data sorted by Region AND Department to do this, we use the same method as above to make the first Sort selection, then press ‘Add Level’ to add a second criteria:
Here we can make the selection of Region in A to Z order and the data will appear as follows:
In this instance, the data is sorted FIRST in alphabetical order of Department, and when they are the same, the Region will be sorted into alphabetical order, as can be seen with HR / Marketing & Sales departments.
It is also super simple to switch the order of these Sort options, to show FIRST the Region in alphabetical order and then the Department. Simply select any cell within the Sort range, then select ‘Sort’ from the ‘Data’ ribbon. The previous Sort options will be displayed. Select one of the criteria so they are highlighted in blue and toggle with the arrows at the top of the screen:
Custom List
In the ‘Order’ section above, there is an option called ‘Custom List’ and this can be used to create a specific order that may not be alphabetical or numerical.
For example, we may want to display the lost of Departments with Sales & Operations at the top of the list…..even though that is not their correct alphabetical order.
To do this follow the guidance above but select ‘Custom List’ from the Order section. This will appear as follows, showing the default options (on the left) that are already available (these are just the days of the week and Months):
Click ‘Add’ once complete and the list will be confirmed and appear on the left hand side as a list to use in the future. Press OK, to select this Sort order to apply now:
Sorting Challenges and Solutions
Unfortunately there is no way to return data to it’s original state after using the Sort function, so it can be quite dangerous. Of course, we can always use the ‘Undo’ function or close the file without saving and this will revert the data, but once we have pressed Save, these options are lost and our only way to revert is to use an earlier version of the file. So it is imperative to check for accuracy after using Sort, before relying on the data.
Another issue that can occur is where the first row of the range selected is NOT the column headers, i.e. in the example below, row 1 is the first selected row, and it is empty:
The column list appears as ‘Column A, Column B’ etc, but more importantly, Excel will deem Row 2 as part of the data to sort.
There is a tick box (top right) here for ‘My data has headers’, but sadly in this instance, it will deem Row 1 to contain the headers and this is still empty, so won’t solve the issue either.
When Filters are applied to a cell, they also have the option to Sort at the top of the list as a shortcut. They will Sort the data within the current Filter range, so can be safer as you won’t need to select the data before applying. Unfortunately using this option, it’s only possible to apply Sort to one column at a time.
Conclusion
Sort is an extremely helpful tool for organising your data in so many different ways and is also relatively simple to use, with only a few pointers to remember to make it work effectively.
However, it can also ruin your data if applied incorrectly, so you must take care when selecting the data to apply the function to.
Quiz Time
Test Your Knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
What does sorting data in Excel accomplish??
2
Which of the following is NOT a feature of Excel’s Advanced Filter?
3
How can you apply a filter to multiple columns in Excel?
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).