Well, pivot tables are very, very powerful. Let us face it, we are able to take 12,000 rows of data and create all this different summaries just by dragging fields around the pivot table field list. However, the thing that you will notice about the pivot table is that it is very Spartan. It is just black and white with these simple boarders. In Excel 2003, you might have tried the auto format button on the pivot table toolbar and you have very quickly learned that the auto format in Excel 2003 was horrible because it would actually rearrange your table. Microsoft heard our pain and so they are going to give us actually functional formats for pivot tables.

Let us go look at the design tab of the ribbon. On the right hand side we have the pivot table styles gallery. Now a gallery is a new control in the Excel 2000 ribbon. Right now we see seven different styles. There are three control buttons along the right hand side of the gallery. If we choose the second the button, we will see the next seven styles or use the bottom button to open the entire gallery and see all the styles available to us. Excel provides styles that are light, medium and dark. There are over 50 different styles available. Now these styles are tied to the current document theme and I will show you in a minute how to change your theme if you do not like any of the colors that are here. However let me choose one of the styles from the gallery.

Wow! That was bright; you know old habits die hard, I did not have to select anything from the gallery. Let me show you how live preview works. I will open the gallery again and before I commit to anything, I can just hover, hover, hover, hover and when I find one that is not as quite as bright, I can choose that. The other cool thing is we can modify those pivot tables styles. You will notice in the left of the gallery, in the pivot table style options, there are four check boxes. If I turn on banded rows, you will see that Excel added very thin lines between each of the rows.

Now if you are looking for a style that supported banded rows, you should have selected that item first and then gone into your gallery. The gallery will change to give you an idea of which styles actually select banded rows. I need to select just the one I previously selected. Hover over that and you will see that we actually have the banded rows effect, click that one to select it. In a similar fashion, you could turn on banded columns or the accent color for the row headers or the column headers. Again, you want to choose these check boxes before going to the gallery in order to see how the style will actually look.

Now it is great that Excel gives us 50 plus different styles and allows us to modify them with banded rows, banded columns, and column headers and row header. But, some people will say, “I do not like the colors that are in the pivot table styles.” We have a brand new concept in Excel 2007 called document themes. Now, if you have ever used PowerPoint, you know that you can change the slide theme. And, when you change the slide theme, you may not have noticed that you get different effects, different fonts and different colors. While PowerPoint 2007 ships with 20 themes, Word 2007 and Excel 2007 ship with the same 20 themes.

If we go to the page layout ribbon, there is a drop down showing the other 19 building themes. You can choose a different color, hover, hover, hover until you find a color scheme that you like. It is also possible to change the colors or the effects without changing the entire theme using the colors drop down. This takes those 50 styles times’ 20 different color schemes, gives you over a thousand color variations that you can use for formatting your pivot table. Now, I have to warn you that when you change the document theme, it does not just change this pivot table. It changes many elements in the workbook. It will change all of your charts. Any of your smart art and any cells that have been formatted with a particular cell style will change based on the colors on the theme.