OfficeTalk: Bringing Improvements to Tables in Excel 2007 (Part 6 of 6)

  • Article

Summary: Applying table styles in Microsoft Office Excel has never been easier than it is in Microsoft Office Excel 2007. With just one or two mouse clicks, you can create professional-looking tables. Read about these and other table style changes in this column. (5 printed pages)

Frank Rice, Microsoft Corporation

January 2008

Applies to: Microsoft Office Excel 2007

Contents

  • Overview

  • Table Styles vs. the AutoFormat Feature

  • Applying Table Styles Has Never Been Easier

  • Smart Table Style Options

  • Creating Custom Styles Simply

  • Conclusion

  • Additional Resources

Overview

If you’ve been reading the last few columns on the improvements to Microsoft Office Excel 2007 tables, you may have noticed that the screenshots all had tables with formatting applied to the entire table. To get the same results in previous versions of Excel, you have to spend time manually formatting the tables. Not so in Office Excel 2007 because of a new table feature called table styles that makes it possible to add formatting with one single click of the mouse.

Table Styles vs. the AutoFormat Feature

Table styles are a feature of Excel 2007 that provides a way to format an entire table quickly by using a preset style definition. It is similar in concept to the AutoFormat feature seen in previous versions of Excel, with a few key differences:

  • Table styles are dynamic, not a one-time formatting operation, so the formatting associated with the style behaves intelligently through many table actions such as addition or deletion of rows or columns, sorting, and filtering.

  • A wide variety of built-in styles is available. Excel 2007 contains 30–40 built-in styles for tables. You can customize the colors used by a particular style, so you essentially have hundreds of styles available with one or two clicks of the mouse. If none of the built-in styles suit your needs, you can create your own custom styles and share them with others.

  • The user interface (UI) for applying styles, known as galleries, allows you to preview the style on your own table before actually applying the style.

  • Table styles are part of a broader set of refinements that includes cell styles, chart styles, Microsoft PivotTable dynamic view styles, and document themes. The cumulative result of these changes is that it is fast and easy to create professional-quality, consistent-looking documents.

Applying Table Styles Has Never Been Easier

Table styles are easy to apply. All you need to do is create a table; Excel 2007 applies a default table style as soon as you create the table. If the default table style does not work for you, you can choose a different style or remove the style altogether. The gallery of table styles is available on the Table Tools tab that becomes visible when you click anywhere in a table.

By default, a single row of table styles is displayed. The number displayed depends on your screen resolution.

Figure 1. Table styles are displayed on the Table Tools tab

Table styles on tab

If you want to see other styles, you can either scroll or click the drop-down arrow in the gallery to see all the styles available.

Figure 2. Click the drop-down arrow to see all the built-in table styles

Click arrow to see built-in styles

Notice that as you move the pointer over each table style, Excel 2007 shows you a preview of what the table looks like with that style. The preview appears directly on the table in the worksheet—not on a separate image. If you like the preview, simply click the style to apply it to the table.

Figure 3. Applying the style occurs with a single mouse click

Apply style with mouse click

You can see that you can apply professional-quality formatting with a single mouse click. However, this is only a part of the story. After applying a table style, the table formatting grows and shrinks as the table changes. Additionally, certain elements of table style formatting have other intelligent behaviors. For example, if a table style specifies banded row formatting, meaning that every odd-numbered row is one color and every even-numbered row is another color, that banding rule always applies no matter what you do to the table. For example, you can add and delete rows, and the table retains the banding. You can also sort and filter the table, and the rows still appear properly banded.

Note

One exception to this rule involves filtering when using the formula =MOD(ROW(), 2) in a conditional formatting rule to generate color bands. Because filtering hides rows, the banding effect is destroyed.

Consider the following table shown in figure 4 before filtering.

Figure 4. The table before filtering

Table before filtering

After filtering out the even-numbered rows, the table looks like the one in Figure 5. Notice that the color bands are retained:

Figure 5. The table after filtering

Table after filtering

Smart Table Style Options

Table styles have several options that you can enable or disable. All of the options have similar intelligent behaviors. For example, a table style might specify that the first column should always appear in a certain way. If you enable the First Column option (in the Table Style Options group on the Table Tools tab), Excel 2007 always ensures that the first column is formatted appropriately regardless of which column is in the first position. You can see the other options on the Table Style Options group in Figure 6. You can toggle these on and off for each table style.

Figure 6. The options in the Table Style Options group

Options in the Table Style Options group

One question you may have regarding table styles is how they relate to user-applied formatting and conditional formatting. If a table style is applied, you can still format the table by using either conditional or manual formatting. Formatting that you manually apply to a table always supersedes formatting that is defined by a table style, and conditional formatting always supersedes manual (user-applied) formatting.

Creating Custom Styles Simply

Finally, I want to describe the elements of a table style and the UI to create your own styles. When creating a table style, you specify formatting for the following elements. All of these elements are optional, and there is an order of precedence. For example, "header row" formatting supersedes "whole table" formatting:

  • Whole table

  • First row stripe

  • Second row stripe

  • First column stripe

  • Second column stripe

  • Header row

  • Total row

  • First column

  • First header cell

  • First total cell

  • Last column

  • Last header cell

  • Last total cell

The New Table Quick Style dialog box allows you to name the style, see a preview, and set the stripe size for bands.

Figure 7. The New Table Quick Style dialog box

New Table Quick Style dialog box

Note

Part of what the options shown in Figure 6 do is to allow you to simply enable or disable the formatting of these elements. For example, if you clear the Total Row check box, Excel 2007 does not show any of the custom formatting that you specify for totals rows when you define a custom style.

Conclusion

Creating professional-looking table styles is now a simple operation. By using the features of the Office Fluent Ribbon, namely galleries, you can preview each style before actually applying it to your table. The possibilities for styling tables are virtually limitless and well worth the time to explore further.

Additional Resources

For more about tables in Excel 2007, see the following resources: