There are many built-in Excel tools to help with data management and the sorting and filtering features are among the best. The filter tool gives you the ability to filter a column of data within a table to isolate the key components you need. The sorting tool allows you to sort by date, number, alphabetic order and more. In the following example, we will explore the usage of sorting and filtering and show some advanced sorting techniques.
For today’s example, we will use the following spreadsheet:
As you can see, the order dates, order numbers, prices, etc. are all out of order. Let’s get started on running some sorting and filtering techniques.
Let’s say you had the spreadsheet above and wanted to sort by price. This process is fairly simple. You can either highlight the whole column or even click on the first cell in the column to get started. Then you will:
- Right click to open the menu
- Go down to the Sort option – when hovering over Sort the sub-menu will appear
- Click on Largest to Smallest
- Select Expand the selection
- Click OK
The whole table has now adjusted for the sorted column. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. This will ensure the data in that row carries over with sorted column data.
The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.
To do this using the filter you would:
- Go to the Data tab on Excel ribbon
- Select the Filter tool
- Select Eastern Company from the dropdown menu
- Select Dylan Rogers from the Salesperson dropdown menu
Boom – you now have the exact number of sales Dylan Rogers made to Eastern Company.
The Sort & Filter Tool
In addition to the right-click menu sorting option and the Filter tool on the Data ribbon, Excel has a Sort & Filter tool that allows for custom sorting.
In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges.
But notice how this example is either/or. What if you wanted to sort by date and by price? This where the Custom Sort option really comes in handy. After selecting your first sorting conditions, you can add a level to get event more accurate data:
As you can see, Excel offers a variety of sorting and filtering tools to help you refine your data and keep it organized. We hope you found today’s tips useful. Now go out there and get your data sorted!
Use Learn Excel Now to help with all your Excel questions and training needs. We’re not just experts in Excel, there is content, free resources, and training courses available for Word, Outlook and more.