1.6 Sorting and Filtering

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 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 in 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 a data table is sorted 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 in Figure 1.28.

Sorting can provide interesting insights not readily apparent in a data table. It is sometimes 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 in 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 the checkboxes for each industry in the data table in Figure 1.29. Uncheck all the boxes except the box for "Ind B" and click OK to display only those companies from "Ind B" on the data table.