Microsoft BI Client Tools: Easing the Transition from Excel 2003 to Excel 2007 – PivotTable Layout and Design

Being that we are now in the year 2009, you would think that most people are currently using or have been using the Office 2007 suite for a couple of years now. The truth is that there are many businesses “in the wild” that are still standardizing on the Office 2003 suite.

Why? Well there are a variety of reasons. Some places might cite cost to upgrade (as in dollars), where others might cite backwards compatibility with legacy applications. Some others might just say that end users “won’t understand” the new ribbon interface, and they might think that the pain and time of training and helpdesk support outweighs the benefits of using Office 2007.

Over the past year I have been in three different places and they all are standardized on Office 2003, and it puzzles me that it isn’t a harder push to upgrade. The benefits of Office 2007 are huge, once you get used to the new interface, and I could go into the benefits but that is probably another blog post, but Outlook 2007 a GTD (getting things done) booster.

As a Business Intelligence guy, it really works for me if every user is on the same client tool, same interface, some quirks and same training, etc. Excel 2007 adds many things when using cubes and pivot tables, and especially with SQL Server Analysis Services 2005, it is a no brainer to use Excel 2007 with SSAS 2005.

In trying to get users of cubes using 2007, there are a few things that I have encountered that can make the transition easier, and today I am going to talk about PivotTable layout and design.

Users of Excel 2003 are used to a pivot table that is laid out in a tabular form, and no subtotals, and maybe grand totals or not. When they use Excel 2007 by default, the are shell shocked by the default pivot table layout and get confused and maybe even sometimes “scared” of what they have gotten into with 2007.

Well, the thing is, it is really easy to get your pivot table to look like a 2003 pivot table in 2007. When you insert a pivot table into Excel, you see this kind of layout.

You can see under the “Pivot Table” tools there is an “Options” and a “Design” tab. Click on the “Design” tab before you set up any dimensions or measures or filters on you pivot table.

These settings on the design tab you can set how you want your Pivot Table to look. To make it “2003 style”, on Subtotals, pick “Do not show subtotals”, On Report Layout, choose “Show in tabular form”. If you don’t to see Grand Totals, then you can turn those off as well. And you can fiddle with the various design options as well.

One thing not on this tab is changing the setting for the +/- on the rows. On the Pivot Table options tab, under the Pivot Table name way on the right, there is an options button.

Here you can tweak other various settings, but you can uncheck “Display expand/collapse buttons” to remove the +/-. As you can see, you can also make the Pivot Table a “classic pivottable” if you really want.

Moving from Excel 2003 to Excel 2007, at least in the Pivot Table and OLAP cube browsing area, shouldn’t be a hard move, and you shouldn’t be scared of it, as you can see you can make your pivot tables look like 2003, or go wild and shift to the new 2007 style.