Excel provides a number of basic capabilities for sorting and filtering data in a worksheet. These capabilities are accessible via the Data menu tab.
Example 1: Sort the data in the range A3:D10 of Figure 1 by income
Highlight the range A3:D12 and select Data > Sort & Filter|Sort. When the dialog box shown in Figure 2 appears choose Income for the Sort by field and make sure that the My data has headers field is checked.
Once you click on the OK button the data in range A3:D12 will be overwritten with the data in sorted order as shown in Figure 3.
Now suppose that in the case of ties we wanted the entries to be sorted in alphabetic order by the person’s name. Notice that three people have income of 35,000 and two have income of 45,000. In neither case are the entries in alphabetic order by name. To perform a multilevel sort you need to press Add Level button in Figure 2. The dialog box will change to give you the opportunity to supply two sort keys. Fill in the entries as shown in Figure 4.
This time the data will be sorted as shown in Figure 5.
Observation: If you want to remove any rows from a range with an empty cell or a cell with non-numeric data, you can sort the range first. All the rows with an empty cell or cell with missing data will be moved to the end of the list where they can easily be removed. This is a way of removing rows of data with missing elements.
Example 2: Remove any duplicates from the Income column of Figure 1.
Highlight the range D3:D10 and select Data > Data Tools|Remove Duplicates. The result is shown in Figure 6.
Figure 6 – Data without duplicates
Example 3: Extract from the data in Figure 1 all the people who have income between 35,000 and 45,000 inclusive.
Highlight the range A3:D12 and select Data > Sort & Filter|Filter. The data will change as shown in Figure 7.
Click on the downward arrow in cell D3 and then select Number Filters on the dialog box that appears and then Greater Than Or Equal To… When the dialog box shown in Figure 8 appears fill in the fields as shown as press the OK button.
The result is shown in Figure 9.