Microsoft Excel

Tables Part 7: Good-looking, well-behaved table formatting made easy …

If you’ve been reading the last few posts on tables, you may have noticed that the screenshots all had tables with formatting applied to the entire table.  You may have thought I spent lots of time manually formatting the tables to look nice for this blog but in fact I hardly lifted a finger - I was using a brand new table feature in Excel 12 called “table styles” which made it possible to add formatting with one click.  Table styles is a feature that provides a way to quickly format my entire table using a preset style definition.  It is similar in concept to the AutoFormat feature Excel had in the past, with a couple 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 – addition or deletion of rows or columns, sort, filter, etc.
• There will be a healthy variety available.  Excel 12 will ship with 30-40 table styles out of the box, and users will be able to vary the colours used by the style, so users will have a couple hundred styles available with one or two clicks of the mouse.  If none of the built-in styles tickle your fancy or suit your presentation, you will be able to create your own custom styles which are sharable with others.
• The UI for applying styles – galleries in the ribbon – is a big step forward.  This is a great example of the kinds of benefit the ribbon brings to Excel.
• They are part of a broader set of work that includes cell styles, chart styles, PivotTable styles, and document themes.  More on that in later posts, but the goal of all this work is to make it fast and easy to create professional-quality, consistent-looking documents.

Table styles are easy to apply – all you need to do is create a table, and a default table style is applied as soon as a table is created (don’t worry though, it doesn’t remove your existing formatting).  If you don’t like the table style, you can pick something different or remove it altogether.  The gallery of table styles is available directly from the table style ribbon.  By default, we show you a single row of styles (the number depends on the resolution of your screen – more resolution, more space for styles).

(Click to enlarge)

If you want to see other styles, you can either scroll, or click on the drop-down to see all the styles available.  (Note – the stuff you see in the screenshot are just place-holder styles that will be updated after further design work.  I am very interested to hear any comments or feedback about what sort of formatting people use for their tables.)

(Click to enlarge)

As I hover over each table style with the mouse, Excel shows me a preview of what my table looks like with that style.  The preview appears directly on the table in my spreadsheet – it is not a separate image.  This is another of the user interface advancements we have made with the new ribbon feature in Office.  If I like how the preview looks, I simply click on it to officially apply it to my table.  (This behaviour will show up a lot of places, and in more apps than just Excel.  I will review some further examples in later posts.)

(Click to enlarge)

To sum, one of the benefits of this feature is that professional-quality formatting is one click away.  The fun is only starting, however.  Once a table style is applied, it will grow and shrink with the table as the table changes (I showed examples of this in my previous posts).  Additionally, certain elements of table style formatting have other intelligent behaviors.  For example, if a table style specifies what we call “banded row” formatting, meaning that every odd row be one color and every even row be another color, then that banding rule will always apply no matter what I do to the table.  This means I can add and delete rows and my table still appears properly banded.  I can sort and filter a table and my rows will still appear properly banded.  (Note – filtering is one place where the technique of using “=MOD(ROW(), 2)" in a conditional formatting rule to generate bands doesn’t work – since filtering hides rows, the banding effect is destroyed.)  For example, if my table looks like this before filtering ...

Before Filter

... and I filter out all even-numbered rows, here is what my table will look like after I filer it:

After Filter

Table styles have several different options that can be turned on and off, and all of them have similar intelligent behaviors.  For example, a table style might define that the first column should always appear a certain way.  If the “first column” option is enabled, then Excel will always make sure the first column is formatted appropriately no matter which column is in the first position.  You can see the other table style options in the screenshot below – header and total rows, first and last columns, and banding on either rows or columns.  You can toggle these on and off for each table style.

(Click to enlarge)

One question we hear a lot when we are showing off table styles is how it relates to “direct” (user-applied) formatting and conditional formatting.  If a table style is applied, you are still free to format your table using either conditional or direct formatting.  Formatting that you directly apply to a table will always appear “above” formatting that is defined by a table style, and conditional formatting will always appear “above” direct, or user-applied formatting (bold for example).

Finally, I want to briefly cover the elements of a table style and the UI to create your own.  When creating a table style, you can specify formatting for the following elements (all are optional, and there is an order of precedence, so, for example, header row formatting shows up “above” 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 dialog that you use also lets you name the style, see a preview, and set the “Stripe Size” for bands (UI still being finalized). 

(Click to enlarge)

Note that part of what the "options" I describe above does is simply turn on or off formatting of these elements - for example, if you turn off the "First Row" check box, Excel 12 will not show any of the first column/first header cell/first total cell formatting.  (Some of you may have been noticed the table has a gradient fill.  That’s another new formatting capability in Excel 12.)

That wraps up tables for now.