Excel Functions and Formulas – Sorting and Filtering Functions
Sorting and filtering functions are available built in with Excel and these are two of the most useful functions used commonly. These functions are used to do very simple sorting to complex filtering operations. These functions are very useful when operations are to be done on large data. Use the following data to illustrate the concepts.
It is always better to format field names or column headers differently from other data. You can make them capital or bolded or italicized. This will help Excel to differentiate data and column headers. If there are blank cells within the list of data as that also would be considered while sorting or filtering. Ensure that your data is consistent and clear. This will make the sorting and filtering functions perform accurately.
Excel allows you to sort by single fields or by multiple fields. In the example, if you wish to sort the details by name, then just click on any cell in name field and then go to HomeEditingSort&Filter Sort A to Z. You can also go to DataSort & Filter and then A to Z symbol. Now your list would be as follows.
If you wish to sort by multiple fields, then go to HomeEditingSort&FilterCustom Sort or DataSort & Filter Sort. You will get a window as given below.
See the check box at the extreme top right corner is checked. As the column headers are formatted differently than actual records, it is checked. Now, as a sort is already completed by employee name, we get the sort details of Employee column. If you wish to sort the details first by employee name, then by department, you need to add level for department. You can decide the order of sort (ascending, descending and custom list) and also decide based on what the sorting is to be done (cell color, font color etc). You can change levels using arrows, delete levels and copy levels.
You can also sort data using a custom list rather than going for ascending or descending order. For example, if you sort the data in ascending order based on Department, it would go in the order Banking, HR, Manufacturing and R&D. But, if you wish to sort data based on Department, but in the order HR, R&D, Banking and Manufacturing, then you can create a custom list for sort order. In the same window opened, if you select Order drop down, you could find an option Custom Lists. Click it and you will get a window as given below.
Now add HR, R&D, Banking, Manufacturing in the List entries: text box and then click Add and OK. Now, you could see the option in the Sort window. You can select the option for sorting the data based on Department
and your sorted data would look as follows.
If you wish to extract records that meet certain criteria, then you can go for filtering function. You can apply filter easily if the list is changed as a table. Click on any cell of the list and then go to InsertTablesTable. Excel will automatically open a window that shows range of cells that contain the list. You just need to ensure that range of cells shown is correct. Click OK and Excel will create a table corresponding to your list. Now, your table would be shown as follows.
You could see filter arrows in the top row. If you click any of these filter arrows, you could see filter conditions. You can deselect the records you do not want using these filters and get the records. Suppose, if you wish the details of people who work in Banking department, then click on Department filter arrow and it will open a window as given below.
Now, uncheck all filters except Banking and click OK. Now, your table would look as follows.
Now, don’t worry thinking that you lost all remaining data. You could see a special symbol on the Department filter. If you click on this, you will get the same previous window, where you can check all filters to get the details back. You can include multiple filters also. You can also add custom filters by going to filter window and then choosing appropriate Text filter or number filter and then specifying the condition.
After doing filtering, if you wish to convert table back to list, then go to DesignToolsConvert to Range. Your data would be converted as normal list.
Read More about Excel here