1.4 Sorting and Filtering

Often you will work with large data tables in Excel. In many cases, the data in these tables isn’t organized or sorted in a way that provides you much information. The sorting and filtering features of Excel allow you to reorganize and limit the items in a table to help you make sense of the table.

Sorting Items in a Table

Figure 1.27 depicts a portion of a table that lists the industry and financial information of about 20 companies. While the information on the table is useful, it is not presented so that it is easy to compare companies in the same industry. However, we can group the companies in the same industries together by sorting the companies by the "Industry" field; this is done by selecting a cell in the "Industry" column (column C) and clicking on the "Quick Sort Ascending" icon in the "Sort & Filter" menu group on the "Data" menu tab. Because we selected a cell in the "Industry" column before sorting, the items on the table will automatically sort by grouping all of the companies in the same industries together. Because we are sorting in ascending order, the companies in "Ind A" will appear before the companies in "Ind B". Notice that there is also a similar "Quick Sort Descending" icon for sorting table items in the opposite order.

Quick sorting is an easy way to rearrange a data table, but it is limited to sorting just one column at a time. But don’t worry; it is possible to sort a table on multiple fields at the same time. To sort on more than one field at a time, select a cell anywhere in the data table and click the "Sort" icon, found next to the "Quick Sort Ascending" button that we used earlier. The "Sort" dialog window is presented on Figure 1.28. You are able to add several fields, or levels, into a single sort. Figure 1.28 demonstrates sorting the data table from Figure 1.27 by "Industry" and "Stock price". When sorting on more than one field, the first level listed will be sorted first. Once that sort is complete the second level sort will be performed within the levels or categories of the first sort level. In this case, the table will be sorted by industry first. This will result in a grouping of the companies in the table by industry. As with the "Quick Sort" depicted in Figure 1.27, all of the companies in industry A will appear together, then the companies in industry B will be listed together, followed by the companies in industry C. After the industry sort is complete, the companies in each industry will be sorted and ranked according to stock price. The results of the multi-field sort are also presented on Figure 1.28.

Using the Filter Feature

Sorting can provide interesting insights not readily apparent in a data table. There are times that it is more helpful to reduce the complexity of a data table by removing or filtering out some information. The filtering feature in Excel allows you to display only specific information on a data table. For example, you may wish to only display the companies in "Ind B" from the data table on Figure 1.27. Figure 1.29 demonstrates the process for filtering out all other data in the table, with detailed instructions following Figure 1.29.

To select only companies in "Ind B", you would select a cell in the data table and click "Filter" in the "Sort & Filter" group of the "Data" menu tab. Filter icons will appear at the top of each column in the data table to the right of the column labels. Clicking on the upside-down triangle filter icon next to the "Industry" column will display the Filter menu list for the "Industry" column. Notice in Figure 1.29 the check boxes for each industry in the data table. By unchecking all boxes except the box for "Ind B" and clicking "OK," only those companies from "Ind B" will be displayed on the data table.

Practice Working with Sorting and Filtering

To view a video showing a correct method for completing the practice problems click here.

Task 1: Sort the table by "Final Score" in ascending order. Do this by clicking on cell L5 (or any other cell in column L of the data table) and then clicking on the "Quick Sort Ascending" menu item in the "Sort & Filter" group of the "Data" menu tab.

Task 2: Filter the table to show only female students. First, click on any cell within the data table. Next, click on the "Filter" menu item found on the "Data" menu tab in the "Sort and Filter" group. Click on the upside-down triangle filter icon to the right of the "Gender" column and make sure that only the box next to "F" is checked in the filter menu list. Now click "OK."

Click on the submit button to see how you did.