Excel Tables
Page 8
Excel 2007 introduced sorting by color.
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:
By color
By various text, number, and date filters
By value
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:
Equals (a value)
Does Not Equal (a value)
Begins With (a value)
Ends With (a value)
Contains (a value)
Does Not Contain (a value)
Filtering Numbers
When a column contains mostly number values, Excel displays the Number Filters menu, with these filter options:
Equals (a value)
Does Not Equal (a value)
Greater Than (a value)
Greater Than Or Equal To (a value)
Less Than (a value)
Less Than Or Equal To (a value)
Between (two values)
Top 10 (shows the top/bottom number items/percent, where number can be set to any value or percentage)
Above Average (shows the values above the average value)
Below Average (shows the values below the average value)
Filtering Dates
When a column contains mostly date values, Excel displays the Date Filters menu, with these filter options:
Equals (a specific date)
Before (a specific date)
After (a specific date)
Between (two specific dates)
Tomorrow
Today
Yesterday
Next Week
This Week
Last Week
Next Month
This Month
Last Month
Next Quarter
This Quarter
Last Quarter
Next Year
This Year
Last Year
Year to Date
All Dates in the Period (with options for quarters 1–4 and with the months of the year displayed in a subordinate menu) NOTE
For most of the text, number, and date cases, Excel displays a dialog for entering one or more values pertinent to the filter. The type of filters provided depends on the number of value types in that column (text, numbers, or dates); the value type that occurs most determines the type of filter.
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.
7 Table Formatting
Excel allows you to use Table styles to apply font, border, and fill style elements to entire Tables. You can use them to format rows and columns in a banded manner that makes it easier to work with the Table data.
The most common Table style is banded rows, where every odd visible row has one cell fill color and every even visible row has a different cell fill color. Applying banding like this makes it easier to follow a single row the length of the Table. As rows are added, removed, sorted, and filtered, Excel continuously applies a Table style so that, regardless of what actions are taken and which rows are visible, every odd visible row is displayed with one cell fill color and every even visible row is displayed with the other cell fill color.
Table Styles
Excel provides 61 different built-in Table styles: 22 light, 28 medium, and 11 dark. You can add any number of custom Table styles to the list of available styles.
Table Styles Gallery
The existing built-in and custom Table styles are presented in the Table Styles gallery. You can reach this gallery by selecting HOME | Format as Table or {TABLE TOOLS} DESIGN | Table Styles, as shown in the next figure.
Table Styles gallery.
To see the full Table Styles gallery, shown in the next figure, you click the More button on the right scroll control.
Expanded Table Styles gallery from the {TABLE TOOLS} DESIGN tab.
Notice that, as you move the mouse over a styles in any of these views, Excel temporarily applies that style to the selected Table if the Live Preview option is enabled (which it is by default). To toggle Live Preview mode, you select FILE | Options | General | Enable Live Preview.
CAUTION
Live Preview may not be available if a computer’s hardware doesn’t meet minimum specifications. For Office 2013, you need a 1 GHz processor or faster; 1 GB of RAM for 32-bit Office and 2 GB RAM for 64-bit Office; 3.0 GB of free hard disk space; graphics hardware acceleration (DirectX 10 or above supported graphics card); and Windows 7 and above or Windows Server 2008 R2 and above (64-bit only).
Once you apply a Table style to a Table, Excel continuously reapplies the style to the Table as rows and columns are added and removed and as rows are sorted and filtered.
TIP
You can use a Table style to format a regular range of cells by converting that range to a Table, applying the Table style, and converting the Table back to a range. The formatting remains but is not dynamically reapplied as the range of data is modified (adding and removing rows and columns or sorting and filtering rows).
Table Style Options
Four Table options control how a Table style is applied to a Table. You find these options in the group {TABLE TOOLS} DESIGN | Table Style Options:
Banded Rows—Applies the Table style's alternating row format to the data body range.
Banded Columns—Applies the Table style's alternating column format to the data body range.
First Column—Applies the Table style's first column format to the data body range.
Last Column—Applies the Table style's last column format to the data body range.
Table Style Options ribbon group used to format Table elements, found by navigating to {TABLE TOOLS} DESIGN | Table Style Options.
By default, Banded Rows is selected and the other three are not. Table style options are applied on a Table-by-Table basis; they are independent of the Table style itself. The following figures illustrate the same Table style with different Table Style Options settings enabled.
Table with no style options enabled.
Table with Banded Rows enabled.
Table with Banded Columns enabled.
Table with First Column enabled.
Table with Last Column enable
d.
Table with Banded Rows, First Column, and Last Column enabled.
Creating Custom Table Styles
You can add any number of custom Table styles to a workbook. Custom Table styles behave similarly to built-in Table styles, but you can define the style elements as you like. You can't modify built-in Table styles, but you can copy them to custom Table styles, as discussed in the following sections.
Adding a New Custom Table Style
There are three ways to create a custom Table style:
Select HOME | Styles | Format as Table | New Table Style.
Select {TABLE TOOLS} DESIGN | Table Styles | New Table Style.
Right-click any Table style in the gallery and choose Duplicate.
Duplicating an existing style is the easiest way to get started. Otherwise, the Table style is empty. To see how this works, right-click a Table style in the gallery and choose Duplicate. Excel gives you the right-click menu shown in the next figure.
Right-click menu from a style in the Table Styles gallery.
If you choose Duplicate from the right-click menu, a Modify Table Style dialog appears. The controls for this are identical to those in the New Table Style dialog, which is shown in the next figure.
New Table Style dialog box.
When you create a new custom Table style, you use the New Table Style dialog box, shown in the previous figure, which offers the following items:
Name—You can fill in any unique Table style name. All custom Table style names must be unique in the workbook; if you try to reuse a name, you get the error message shown in the next figure.