Sorting and filtering table data –
Each item in the header row of a table contains a drop-down arrow known as a Filter Button.
When clicked, the Filter Button displays sorting and filtering options (see Figure below).
Sorting a table –
Sorting a table rearranges the rows based on the contents of a particular column. You may
want to sort a table to put names in alphabetical order. Or, maybe you want to sort your
sales staff by the total sales made.
To sort a table by a particular column, click the Filter Button in the column header and
choose one of the sort commands. The exact command varies, depending on the type of
data in the column.
You can also select Sort by Color to sort the rows based on the background or text color of
the data. This option is relevant only if you’ve overridden the table style colors with cus-
tom formatting.
You can sort on any number of columns. The trick is to sort the least significant column
first and then proceed until the most significant column is sorted last. For example, in the
real estate table, you may want to sort the list by agent. And within each agent’s group,
sort the rows by area. Then within each area, sort the rows by list price. For this type of
sort, first sort by the List Price column, then sort by the Area column, and then sort by the
Agent column. Figure below shows the table sorted in this manner.
Another way of performing a multiple-column sort is to use the Sort dialog box (choose
Home ➪ Editing ➪ Sort & Filter ➪ Custom Sort). Or right-click any cell in the table and
choose Sort ➪ Custom Sort from the shortcut menu.
In the Sort dialog box, use the drop-down lists to specify the sort specifications. In this
example, you start with Agent. Then click the Add Level button to insert another set of
search controls. In this new set of controls, specify the sort specifications for the Area col-
umn. Then add another level and enter the specifications for the List Price column. Figure
below shows the dialog box after entering the specifications for the three-column sort. This
technique produces the same sort as described previously in this section.
Filtering a table –
Filtering a table refers to displaying only the rows that meet certain conditions. (The other
rows are hidden.)
Note that entire worksheet rows are hidden. Therefore, if you have other data to the left or
right of your table, that information may also be hidden when you filter the table. If you
plan to filter your list, don’t include any other data to the left or right of your table.
Using the real estate table, assume that you’re interested only in the data for the
Downtown area. Click the Filter Button in the Area row header and remove the check mark from Select All, which deselects everything. Then place a check mark next to Downtown
and click OK. The table, shown in Figure below, is now filtered to display only the listings in
the Downtown area. Notice that some of the row numbers are missing. These rows are hid-
den and contain data that does not meet the specified criteria.
Also notice that the Filter Button in the Area column now shows a different graphic—an
icon that indicates the column is filtered.
You can filter by multiple values in a column using multiple check marks. For example, to
filter the table to show only Downtown and Central, place a check mark next to both values
in the drop-down list in the Area row header.
You can filter a table using any number of columns. For example, you may want to see only
the Downtown listings in which the Type is Condo. Just repeat the operation using the Type
column. The table then displays only the rows in which the Area is Downtown and the Type
is Condo.
For additional filtering options, select Text Filters (or Number Filters, if the column con-
tains values). The options are fairly self-explanatory, and you have a great deal of flex-
ibility in displaying only the rows in which you’re interested. For example, you can display
rows in which the List Price is greater than or equal to $200,000 and less than $300,000
(see Figure below)
Also, you can right-click a cell and use the Filter command on the shortcut menu. This
menu item leads to several additional filtering options that enable you to filter data based
on the contents of the selected cell or by formatting.
When you copy data from a filtered table, only the visible data is copied. In other words,
rows that are hidden by filtering aren’t copied. This filtering makes it easy to copy a subset
of a larger table and paste it to another area of your worksheet. Keep in mind, though, that
the pasted data is not a table—it’s just a normal range. You can, however, convert the cop-
ied range to a table.
To remove filtering for a column, click the drop-down in the row header and select Clear
Filter. If you’ve filtered using multiple columns, it may be faster to remove all filters by
choosing Home ➪ Editing ➪ Sort & Filter ➪ Clear.
Filtering a table with slicers –
Another way to filter a table is to use one or more slicers. This method is less flexible but
more visually appealing. Slicers are particularly useful when the table will be viewed by
novices or those who find the normal filtering techniques too complicated. Slicers are very
visual, and it’s easy to see exactly what type of filtering is in effect. A disadvantage of slic-
ers is that they take up a lot of room on the screen.
To add one or more slicers, activate any cell in the table and choose Table Tools Design ➪
Tools ➪ Insert Slicer. Excel responds with a dialog box that displays each header in the
table (see Figure below).
Place a check mark next to the field(s) that you want to filter. You can create a slicer for
each column, but that’s rarely needed. In most cases, you’ll want to be able to filter the
table by only a few fields. Click OK, and Excel creates a slicer for each field you specified.
A slicer contains a button for every unique item in the field. In the real estate listing
example, the slicer for the Agent field contains 14 buttons because the table has records for
14 different agents.
To use a slicer, just click one of the buttons. The table displays only the rows that have a
value that corresponds to the button. You can also press Ctrl to select multiple buttons and
press Shift to select a continuous group of buttons, which would be useful for selecting a
range of List Price values.
If your table has more than one slicer, it’s filtered by the selected buttons in each slicer. To
remove filtering for a particular slicer, click the Clear Filter icon in the upper-right corner
of the slicer.
Use the tools in the Slicer Tools Options Ribbon to change the appearance or layout of a
slicer. You have quite a bit of flexibility.
Figure Below shows a table with two slicers. The table is filtered to show only the records for
Adams, Barnes, Chung, and Hamilton in the Downtown area.
