Analyzing Data with Tables and Charts in Microsoft Excel 2013

Sorting and filtering data

In this section we discuss how to create order out of even the most chaotic worksheet data. You can enter or import that data in any order or even at random. Once it’s arranged in rows and columns, you can rearrange it as needed. You can sort by numbers, text, or dates. You can also reduce clutter by filtering a list to show only data that matches conditions you define. These options work on simple data ranges and on tables.

Sorting a range, region, or table

You can sort a range, region, or table by using values from one or more columns. That’s true regardless of the data type. In a membership roster, for example, you can sort the list in alphabetical order using the Last Name column, or by date, oldest to youngest, using the Birthday column, or by number if you’re using the Donations column to track progress in a fundraising drive.

To sort the current region, click a single cell in the column you want to sort by and then click Sort & Filter in the Editing group on the Home tab. The choices at the top of the list vary to reflect the data type. For text, Sort A To Z and Sort Z To A are available, as shown here. For dates, the choice is Sort Oldest To Newest and Sort Newest To Oldest. For numbers, the choices are Sort Smallest To Largest and Sort Largest To Smallest.

If you prefer to sort the current column with a single click, use the commands in the Sort & Filter group on the Data tab, or pin the Sort A To Z and Sort Z To A buttons to the Quick Access Toolbar.

To sort by multiple columns, click Sort on the Data tab; or click Sort & Filter on the Home Tab and then click Custom Sort; or right-click a cell in the table or range, click Sort, and then click Custom Sort.

Any of those roads take you to the Sort dialog box, shown in Figure 13-6. In operation, it’s pretty straightforward and easy to figure out. You build a list of sort levels, each based on a column, and then define the sort order for each level.

Excel sorts dates, times, and numbers exactly as you would expect, depending on the sort order you select—Newest To Oldest, Smallest To Largest, and so on. The rules for text are slightly more complicated. For A to Z (ascending) sorts, numbers come first, then most punctuation characters, and then letters in ascending (A–Z) order. The sort is not case-sensitive, so capital letters and lowercase letters are considered the same for sorting purposes. Apostrophes and hyphens are typically ignored except when two strings of text are otherwise identical; in that case, the one that contains the additional punctuation follows the one without. The precise order for punctuation places the space character first, then uses the same numbering as the Unicode character set to determine the order of additional nonalphabetic characters.

Filtering data in a table

As a list gets longer and longer, it becomes more difficult to see patterns associated with subsets of that data. That’s when filtering the list becomes useful, hiding rows except those that match criteria you specify. If you’re analyzing data from a dozen schools, stores, or customers, each with a unique identifier in a common column, you can filter the worksheet to show only the names you select from that column. You can filter on numbers and dates as well. For example, to create a filtered list of products that are out of stock (or nearly so), you can include only rows where the value in the QtyOnHand column is below 3.

If the number of choices in a column is limited, or if you know exactly which names or values you want to include, you have two options.

The first option uses a feature called slicers, whose capabilities have been expanded in Excel 2013 to work with ordinary tables as well as PivotTables. A slicer is essentially a floating list, built on the fly, that lists all the unique items in a column. By clicking items in the slicer, you instantly filter the table to show matching rows.

To add a slicer to a worksheet, click to select a cell within a table and then click Slicer in the Filters group on the Insert tab. Select the name of the column you want to “slice,” and click OK. The result is a list like the one shown here, which scrolls if necessary so that you can see and click items to use as a filter.

Ctrl+click to select or clear multiple items from the filter list. Click the Clear Filter button in the upper-right corner of the slicer (or press Alt+C) to clear your selections and display the lists sans filter.

When you click a slicer box, Excel displays a context-sensitive Slicer Tools Options tab on the ribbon, where you can adjust the name displayed in the slicer’s title bar, change the height and width of individual buttons, and display buttons in multiple columns. (Similar controls allow you to adjust the height of the slicer box itself, but it’s easier to drag the borders of the slicer to change its height or width.)

And you’re not limited to a single slicer. If you use two or more slicers together, they cooperate neatly. In Figure 13-7, for example, we’ve filtered the list using three company names. Doing so caused the Country Code slicer to display matching values at the top of the slicer, where we are free to click (or Ctrl+click) to filter the list further.

The second option, which works with or without slicers, is to filter the contents of one or more columns directly. Click the arrow to the right of the column heading and pick from the list at the bottom of the menu, as shown in Figure 13-8.

The values in this list are drawn from the contents of the current column. Clear the Select All check box at the top of the list to clear all items, after which you can select a few specific items from the list. Click Select All again to reselect all items. When you have a mix of manually selected and cleared check boxes, the Select All box is a solid square as in Figure 13-8.

If the list of items is too long to be easily manageable, use the search box on the menu to restrict the list of items to those that match whatever you type. The text you enter doesn’t have to be a complete word or phrase, and the search results show any match regardless of whether it’s at the beginning, middle, or end of a cell’s contents. Thus, entering dat returns A. Datum Corporation and Consolidated Messenger.

For dates that range over a long period of time, Excel automatically collapses the choices in the filter list, allowing you to choose entire years or to expand the list to include months or even days within a year. Here, for example, we started with a list of stock prices that includes data from every trading day for 25 years. By typing Jan in the search box, we filtered the list to show only the dates from January.

If our goal is just to compare data from the month of January for the past five years, we can click Select All Search Results to clear the current list, then manually include 2012, 2011, and so on. Or we can manually choose certain years. When the selection is complete, select Add Current Selection To Filter, and click OK.

In addition to item-by-item filtering, you can create a custom filter for any column to show or hide rows in the list according to the criteria you specify. The exact set of options depends on the data type. The menu option above the search box reads Text Filters, Number Filters, or Date Filters, depending on the contents of the current column.

Options on the Text Filters menu all lead to the Custom AutoFilter dialog box, where you can define one or two criteria for your filter. You can base criteria on the exact cell contents (Equals, Does Not Equal) or on what the cell begins with or contains. The following example finds all rows where the contents of the Vendor column begin with B, C, D, or E.

For a column that contains numbers, most of the options on the Number Filters menu lead to the same Custom AutoFilter dialog box. The menu contains three additional choices that work only with numbers. The Above Average and Below Average options work as expected, instantly filtering the list to show only those items that are above or below the arithmetic mean. The other choice, Top Ten, is misleadingly named. It opens the Top 10 AutoFilter dialog box, with Top 10 Items selected by default. However, you can select any number between 1 and 500; you can choose to show the Top or Bottom entries that match that value; and you can change Items to Percent. In this example, we’ve filtered the list to show only those records with values in the bottom 20 percent.

The options on the Date Filters menu are probably the most extensive. You can quickly define a range of dates, choose relative dates (Yesterday, This Quarter), or create a custom filter. Figure 13-9 provides one example of the full range of options, where we’ve used check boxes to narrow the list to the three most recent years and then used the Date Filters menu to specify that we want only dates from Quarter 1 in those years.

If you choose the Custom Filter option, the dialog box resembles the one available for text and numbers, with the small but crucial difference that it includes calendar controls to use when you are picking dates.