What are Sort and Filter?
Sorting and filtering are two entirely different concepts – almost opposite. But both allow you to look at your spreadsheet in different ways. Sorting allows you to see all your information a specific way. For example, the spreadsheet below is sorted by title:
How to Sort
- Click on “Sort and Filter”.
- In the Sort and Filter Context Menu, click on “Custom Sort” – this will give you the most options.
- In the “Sort By” field, click on the down arrow to see the options for sorting.
- Once clicked, a list will appear of the column headings of the spreadsheet.
- Choose the item for sorting, for example, rather than sorting by title – sort by date.
- Click OK to confirm the choice.
Excel will now re-organize the spreadsheet to display by the chosen field.
This is simply a reorganization of data. Everything that was there when the list was displayed in Title order is still there, but displayed by Date.
How to Perform a Multi-Level Sort
Excel also allows multi-level sorts, which can be quite powerful if you understand what is going on. In a multi-level sort, think of each additional level as “and then by”. To start, I’ve reorganized my spreadsheet by Director Name – just so it is in a neutral list to start.
Now, let’s create a list by Date, then alphabetical by Title.
- First, click on “Sort and Filter”, then “Custom Sort”.
- Change “Director Name” to Date. For your own spreadsheet, pick any column to sort by.
- Now click “add a level”, to add the next level of sorting. Remember, Excel will sort first by the first thing chosen, and then by the next level.
- Another Sort By line will pop-up, as before click the down arrow to choose the column to sort by.
- Click “OK”.
The result is a list sorted by Date first and then by Title.
It’s important to consider how you want to view data, especially with multi-level sorts. This sort lists the Date first, so you need several items with the same date, before the second level goes in to affect – the title. So, for example, if you had a product list and every product had a unique ID number, sorting by “Product ID” and then “Product Name” wouldn’t be terribly useful – since Product ID was unique – you’d simply have a list by Product ID. If you reversed that idea, and listed by Product Name then Product ID, the results would depend on the products you have – if there are several of the same product but by different companies, thus having different IDs, a list by Product Name and then by Product ID might be useful. (For example, if you had a high-end grocery and sold different types of cola and each had it’s own unique Product ID.)
Excel will allow up to three levels of sorting, but remember that you want both repeated information and unique information for the sort to be meaningful. An example of a good three-level sort might be if you had a list of all your music CDs. You could sort by (1) Genre or Type of Music and then by (2) Artist – the band or singer, and (3) then by album title. Since you are likely to have several types of genres in your music collection, many artists, but each album has a unique title, and in the cases where titles might be the same, the artist is probably different such a three-level sort should work.
How to Filter
Filtering is almost the opposite of sorting. Whereas with sorting you see all information presented in a different order – with filtering you are looking to “pull out” only certain types of information.
This sample excerpt spreadsheet of films in my DVD collection includes the genre of the film.
To turn on filtering, click the sort and filter button on the ribbon, then click filter.
Once filter is turned on you will see little arrows next to each column.
Click an arrow, such as the one next to “genre” in this example, and a “pick list” will appear showing all the categories in that column.
By default, every unique value is listed. To apply a filter, uncheck all but the information you want to see.
Then click “OK”.
As you can see, rather than listing all films, the list is now limited to only musicals. You can tell the list is filtered because (a) the row counter on the far left now shows skips in the number sequence, indicating lines not shown, and (b) the filter symbol shows on the “Genre” column header.
In order to see the whole list again, go back to the filter symbol, click the arrow and choose “Select All”.
To turn off filtering entirely, go back to the sort and filter button, “filter” will be highlighted, click to turn it off.
Filtering is best used to view only a certain class of information.
Also, if you have a Yes/No column in your spreadsheet, you can filter to only show “yes” or only show “no”. You can even filter to only show blanks or to not show blank lines, by adding and removing check marks in the filter list.
“Yes/No” filtering can be very powerful, and I use it at work all the time.
In this article, I’ve shown how to use sorting and filtering in Excel. Sorting and filtering are very useful and powerful tools for viewing the data in a spreadsheet. Mastering them will help you to get more out of your spreadsheet data.