6 Sorting, Filtering, and Using Slicers

Two very common tasks when working with data are sorting and filtering. This chapter details how they apply to Tables. Generally speaking, it's not a good idea to have multiple Tables stacked horizontally when filtering. This is because a filtered row hides the entire sheet row, not just the rows of the Table being filtered. If Tables are to be stacked, it's generally recommended to stack them vertically; however, if you do this, their dimensions should be identical, or potential errors may arise when inserting Table rows. This chapter describes sorting and filtering pertaining to Tables; for more information on best practices related to these tasks, see Chapter 3.

Sorting

Tables provide sorting tools via the AutoFilter controls. When a Table is sorted, Excel automatically constrains the effects of the sort to the Table's data body range.

Sorting is an integral part of data analysis. Whether you're sorting text or numbers or sorting by color, Excel offers a wide variety of sorting methods to view Table data in many ways. In terms of the sort options available, there is no difference between sorting a standard range of cells and sorting a Table. There are three basic types of sorts:

Sorting by Color

When you're sorting by color, Excel displays a list of all cell fill colors used in the selected column. Excel lists only the cell fill colors applied—whether manually applied or applied with conditional formatting. When a manually applied cell fill color is overridden with a conditionally applied cell fill color, the underlying manually applied cell fill color is not considered when building the list or sorting the list.

You can see the sorting options by clicking the AutoFilter drop-down control on the right side of each header cell. The next figure shows the sort and filter menu you get when you click the AutoFilter drop-down.

AutoFilter drop-down menu sort commands.

When you expand the Sort by Color submenu, you see a list of unique colors in the selected column that are currently visible, including the option No Fill, which is any cell with no fill color applied, either manually or with conditional formatting.

Sort by Cell Color submenu.

TIP

There are two kinds of drop-down controls associated with cells: validation lists and AutoFilter controls. They look the same but are placed on different sides of the cell’s right border. AutoFilter drop-down controls are inside the cell’s right border, and validation list drop-down controls are displayed outside the cell’s right border. In addition, AutoFilter drop-down controls are always displayed, whether the cell is selected or not; on the other hand, validation list controls are displayed only when the cell is selected and active. The next figure shows these two kinds of drop-down controls on the selected cell.

Table headers with AutoFilter drop-downs showing, along with a data validation drop-down control for the selected cell. The AutoFilter control on the left is for filtering, and the validation drop-down on the right is for an in-cell validation list.

You can also access the sorting commands by selecting HOME | Editing.

Performing a Custom Sort

At the bottom of the Sort by Cell Color submenu is the command Custom Sort. If you select this command, Excel opens the Sort dialog, which allows you to sort on any number of columns at once (see the next figure).

Sort dialog box for custom sorting.

TIP

By default, a protected worksheet does not allow sorting in a Table, even if you choose to allow sorting when protecting the worksheet. A workaround is to turn off the Locked property of every cell in the header and data body range. You do this by selecting the header range and data body, right-clicking any cell in the selection, and selecting Format Cells. Then you navigate to the Protection tab, uncheck Locked, and click OK. Alternatively, you can select HOME | Cells | Format and uncheck Lock Cell.

Determining the Sort State

To determine whether a column is sorted, you can inspect the images in the AutoFilter drop-down controls. When a column is sorted, the icon changes to show an upward- or downward-pointing arrow.

Filters

In addition to allowing you to sort, the AutoFilter drop-down controls also provide access to tools for filtering a Table in a variety of ways. Each column supports AutoFilter conditions. You can filter a Table by making column filters active.

Filtering Tables works the same way as filtering standard worksheet cells. When you filter a Table, Excel automatically constrains the effects of the filter to the Table's data body range.

TIP

Excel allows only a single AutoFilter per worksheet, but Tables have their own AutoFilter in addition to the worksheet’s AutoFilter. It is therefore possible to have an AutoFilter for every Table plus one additional AutoFilter for the worksheet on a standard range.

You get the AutoFilter options the same way you get the sort options: by clicking the AutoFilter drop-down control on the right side of each header cell.

Excel allows you to filter in a number of ways, including these:

Filtering by Color

The Filter by Color command is available only if a column has any cells with fill colors other than those applied with the Table style (see the next figure).

The AutoFilter drop-down menu, with colors in the column and without.

Filtering Text

When a column contains mostly text values, Excel displays the Text Filters menu, with these filter options:

Filtering Numbers

When a column contains mostly number values, Excel displays the Number Filters menu, with these filter options:

Filtering Dates

When a column contains mostly date values, Excel displays the Date Filters menu, with these filter options:

Filtering Values

At the bottom of the AutoFilter menu is a list of all the unique values in that column. If the column contains mostly date or time values, the list is organized in a hierarchical manner, starting with years at the highest level, months next, then days, and so on. You can select any combination of values. You can use the search box at the top of the list to quickly narrow down the list of values to a specific set that matches the entered search value.

Determining the Filter State

When a Table is in a filtered state, the row headers on the left side of the worksheet window change from black to blue, as shown in the next figure.

Row headers showing that a filter has been applied.

TIP

The figure above uses blue to indicate filtered rows, but the Windows color scheme actually controls what color is used here. You can customize these settings to change the look and feel of Excel and any other application that uses these settings.

Another way to tell if a filter is applied (and to tell which columns are filtered) is to inspect the images on the AutoFilter drop-down controls. When a column is filtered, the icon changes to show a funnel, as you can see in the next figure.

Filter applied to the "Region" column, as indicated by the image on the AutoFilter drop-down control.

NOTE

When a filter is applied to a Table, if you hide the header row (via {TABLE TOOLS} DESIGN | Table Style Options | Header Row), Excel removes the filter and disables the Filter Button control. There is no workaround for this.

Removing Filters

You remove a filter from a column by selecting Clear Filter From "Column Name" from the AutoFilter drop-down menu. If the column is not filtered, this command is disabled.

Using Slicers

Introduced in Excel 2010 for PivotTables only, Slicers are visual interactive filtering controls. Slicers expose a single column's unique values in a grid of buttons that you can click to include those values in the filter. By default, if you click a button, Excel includes that value and excludes all others. Pressing the CTRL key while clicking maintains the existing set of selected values and toggles the status of the clicked button. Excel 2013 includes Slicers for Tables.

NOTE

Excel Online provides only limited support for Slicers: You can use them, but you can’t maintain them.

To add a Slicer, you select {TABLE TOOLS} DESIGN | Tools | Insert Slicer. The Insert Slicers dialog box appears, as shown in the next figure. In it, you select the column(s) for which you want Slicers and then click OK. Excel creates one Slicer for each selected column.

Insert Slicers dialog box.

After you create a Slicer, Excel places it on your worksheet. The next figure shows an example of a Slicer for the "Region" field of a Table.

Example of a Slicer on a worksheet.

The next figure shows the {SLICER TOOLS} OPTIONS ribbon tab, which is a contextual tab.

{SLICER TOOLS} OPTIONS contextual ribbon tab.

You can add duplicate Slicers for the same column. When you do, by default the Slicers are linked to the same data source, and you cannot change this link.

You cannot filter a column's values that are filtered out by filters in other columns. Slicers have options to indicate what is currently available to be filtered in a column. You can tell Excel what to do with values that are not available by selecting one of the three check boxes in the Slicer Settings dialog: Hide items with no data, Visually indicate items with no data, or Show items with no data last. The next figure shows these three controls. In this figure, unchecking Hide items with no data enables the next two check boxes. Checking the Visually indicate items with no data check box enables the Show items with no data last check box.

Slicer settings to hide items with no data.

The SLICER TOOLS | OPTIONS ribbon tab includes options for customizing the visual style of a Slicer, such as the number of columns, dimensions, and other aspects of the Slicer.

Table Slicers are the same as PivotTable Slicers and thus share many things in common in the user interface. For example, the right-click menu of a Table Slicer is the same as the right-click menu of a PivotTable Slicer (see the next figure). The only difference is that the command Report Connections is disabled for Tables because this is a feature only of PivotTables.

Slicer right-click menu.

Customizing Slicer styles is similar to customizing Table styles. For more information, see Chapter 7.

If a Table's header row is disabled, you cannot use Slicers on that Table. All Slicers that are linked to a Table whose header row is disabled show as Out-of-date, with an UPDATE button, as shown in the next figure. You can click the UPDATE button to enable the Table's header row.

Slicer from a Table with a hidden header row.

NOTE

A Timeline is a variant of a Slicer that is specifically for dates. Timelines are available only for PivotTables, not for Excel Tables.