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.
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). |
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 enabled.
Table with Banded Rows, First Column, and Last Column enabled.
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.
- Table Element—You can choose from this list of 13 style elements (discussed later in this chapter). After you select the element to edit, you can click Format to open the Format Cells dialog box. Any style elements that have settings defined appear boldfaced in this list. Clicking Clear clears all settings from that style element and removes the boldface from the style element name.
- Preview—This area provides a small representation of what the Table style element looks like when applied.
- Element Formatting—This area shows the formatting applied to the selected element, including font color (specified in RGB format), borders, and fill color/pattern.
- Set as default table style for this document—Select this check box to set the Table style as the default Table style for all new Tables created.
Error message when you try to save a custom Table style with a name that already exists in the workbook.
|
CAUTION You can name a custom Table style the same as a built-in Table style name. However, it’s not a good idea to do so because it can be confusing. |
A Table style comprises multiple style elements that format the parts of a Table, such as the header row, odd and even rows, and odd and even columns. You can enable or disable each style element. If you disable one, the Whole Table style element applies to that part of the Table.
Each element can apply font, border, and fill formatting but not number, alignment, or protection formatting. Only style elements that are defined (enabled) in the Table style are applied when you apply that Table style to a Table. The four Table style options discussed earlier in this chapter also apply to custom Table styles and override how the Table style settings are applied to a Table on a Table-by-Table basis. If a Table style element has not been defined for the part of the Table covered by the Table style option, toggling that Table style option has no effect on how the Table style is applied to the Table.
Each Table style includes 13 style elements. You can enable each element by defining one or more style properties (font, border, or fill). You disable an element by selecting that style element and clicking the Clear button. These are the 13 style elements:
- Whole Table—Applies to the entire Table, unless superseded in another Table element.
- First Column Stripe—Applies to alternating columns.
- Second Column Stripe—Applies to alternating columns following the column(s) affected by the First Column Stripe style element.
- First Row Stripe—Applies to alternating rows.
- Second Row Stripe—Applies to alternating rows following the row(s) affected by the First Row Stripe style element.
- Last Column—Applies to the rightmost column in the data body range unless the Table contains a single column. If this element is defined, it supersedes the settings in the First Column Stripe and Second Column Stripe style elements.
- First Column—Applies to the leftmost column. If this element is defined, it supersedes the settings in the First Column Stripe and Second Column Stripe style elements.
- Header Row—Applies to the header row if the Header Row style is enabled.
- Total Row—Applies to the total row if the Total Row style is enabled.
- First Header Cell—Applies to the leftmost header cell if the header is enabled. If this element is defined, it supersedes the settings in the Header Row style element.
- Last Header Cell—Applies to the rightmost header cell if the header is enabled. If this element is defined, it supersedes the settings in the Header Row style element.
- First Total Cell—Applies to the leftmost total row cell if the total row is enabled. If this element is defined, it supersedes the settings in the Total Row style element.
- Last Total Cell—Applies to the rightmost total row cell if the total row is enabled. If this element is defined, it supersedes the settings in the Total Row style element.
Note that some of these style elements refer to a stripe rather than to a column or row. This is because they can apply to more than one column and more than one row. The default is a single column and a single row, but as many as nine (contiguous and visible) columns and nine (contiguous and visible) rows can be affected by changing the style element's Stripe Size setting (see the next figure).
How the first and second stripes are applied depend on each style element's stripe size. When the default of one is used for both the first and second stripes, the first stripe is applied to all odd columns or rows, and the second stripe is applied to all even columns or rows. If either the first or second stripe's stripe size is more than a single column or row, the results are different. For example, if the first stripe size is two and the second stripe size if three, the first two columns or rows are formatted with the first stripe style, the next three columns or rows the second stripe style, the next two columns or rows the first stripe style, and so on, alternating between the first and second stripe styles.
Stripe Size settings: 1 through 9.
|
NOTE Stripe formatting is applied only to visible columns and rows. Any hidden columns or rows are not counted in the stripe size. |
|
NOTE Most built-in Table styles define only the First Column Stripe and First Row Stripe elements. The striping effect is generally achieved by relying on the Whole Table element to format the columns and rows that are not affected by the first stripe element (that is, any columns and rows that would be affected by the second stripe element defined). Regardless of whether one or both of the first and second stripe elements are defined, toggling the Table style options Banded Rows and Banded Columns affects both style elements at the same time. |
|
TIP When you create custom Table styles, unless you have a good reason to do otherwise, it is easiest to follow the strategy used in the built-in Table styles and define only the first stripe element and let the Whole Table element format the other columns or rows. |
Each style element can apply font, border, and fill properties:
- As shown in the next figure, the Font tab lets you set these options:
- Font style
- Underline
- Color
- Effects (Strikethrough only)
- The Border tab lets you set these options:
- Line style
- Color
- Border part
- The Fill tab lets you set these options:
- Color
- Effects
- Pattern
Format Cells dialog box for customizing a Table style element.
If you want to apply cell format attributes that are not available in a Table style, you can do so by applying a cell style or manually applying any cell format settings.
Create New Theme Colors dialog box.
|
NOTE You can create custom styles for regular cells, PivotTables, Slicers, and Timelines. Creating and maintaining these styles is similar to creating and maintaining Table styles. |
Setting the Default Table Style
By default, the Table style Table Style Medium 2 is applied to all new Tables. However, you can change the default Table style for a workbook at any time; you can select any built-in or custom Table style as the default Table style. To change the default Table style, right-click the Table style you want in the Table Styles gallery and select Set As Default.
|
TIP When you customize a Table style by using the New Table Style or Modify Table Style dialog box, you can make that Table style the default Table style by selecting the check box Set as default table style for this document, as illustrated in the next figure. |
Check box to set a Table style as the default. All new Tables created will then use this style.
Modifying an Existing Custom Table Style
You modify an existing custom Table style by right-clicking that Table style in the Table Styles gallery and selecting Modify, as shown in the next figure. The Modify Table Style dialog box appears.
Right-click menu from a style in the Table Styles gallery.
When you create a custom Table style, it is saved to the workbook in which you created it. Unlike with workbook colors, fonts, effects, and themes, there is no native way to easily copy a Table style to another workbook. However, you can follow these steps to copy a Table style from one workbook to another:
1. Select the entire source Table with the desired Table style applied.
2. Copy the range (using CTRL+C).
3. Activate the destination workbook.
4. Add a new worksheet (optional).
5. Paste the source Table (using CTRL+V).
6. Delete the newly added Table or the worksheet into which it was copied (optional).
When the Table is copied into the other workbook, Excel brings the Table style with it; the Table Style remains after the Table in the destination workbook is deleted. This method is implemented as a VBA macro in Chapter 9.
|
NOTE The new file formats allow you to go under the hood of an Excel file. With the file closed, change the extension from XLSX or XLSM to ZIP. (XLSB files are handled differently.) Then you can open the file and navigate to see different parts of the spreadsheet, which is stored in Extensible Markup Language (XML). As a precaution, save a backup copy of your file before you explore. The XML for custom Table styles, Slicer styles, and Timeline styles is stored in the workbook file in the same node (“//styleSheet/tableStyles/tableStyle”), inside the “/xl/styles.xml” file. |