Categorized | pivot table

Pivot Table in Excel 2007 – Comparison with Excel 2003

Pivot Tables underwent a bit of a change in Excel 2007. Our reader Miron commented on the Pivot Table section of my review of Excel 2007 saying, “Excel 2007 ruined a really good tool.” Excel 2007 did shake up a lot of things that we were comfortable with – including charts and pivot tables which were probably some of the most frequently used features. But not all have been misses and I think changes have made the pivot tables better (there … I said it) . Here’s my view on how pivot tables in Excel 2007 stack up against those in Excel 2003.

Creating the Pivot Table

We took our sample set with 108 rows and 5 columns as shown below.

Creating a basic pivot is somewhat simpler in Excel 2007. Select the data range and click the Pivot Table button on the ribbon. Press ok and you are done.


The drag and drop features are slightly different. While in Excel 2003 one would drag the field from the pivot table field list straight into the diagram, in Excel 2007, the drag and drop takes place within the field list window. A small checkbox allows the user to defer the actual rendering of the pivot table when working with large data sets.

pivot-table-wizard-excel-2007 title=

One major advantage is the “summarize data by option” that you get when you right click on a data field in the pivot table in Excel 2007. A right and a left click is all that it takes to summarize data up by count, by sum, by average and what not ! And I think that really does speed up things up (try doing this with a pivot table with more than 30 fields and you’d begin to shake your head up and down :-))

Design and Layout of the Pivot Table

Both the tables below have the same layout and Excel 2007 pivot table looks like a clear winner. The row fields have option of being collapsible which is a great if you want to structure the data in a meaningful fashion. A single click and you can hide the portions of data that you want. The total appear against the (default) name and a separate total row is eliminated. Contextual tool tips are also a great time saver when you have the scroll across a really large table.


You can hide child row fields in excel 2003 by double clicking the parent row field. However that is fraught with risk. The reader can completely miss out the fact that a portion of data is hidden underneath.

The default pivot table layout in Excel 2007, when you have more than one data field, is to have them placed side by side. This is a bit different from the stacked layout used in a pivot table created in Excel 2003 – and the better for it. The pivot table in this orientation is much more compact and the reader is able to grasp the information in a shorter span of time.


Calculated Field and Calculated Items

The first thought that came to my mind when I tried to create one in excel 2007 was – where the heck are they?Hidden under the now-you-see-it-now-you-dont ‘options’ ribbon is the formulas button that lets you create calculated fields and calculated-field-pivot-table-excel-2007 items in the pivot table. It appears only when you select a cell within the pivot table – a point that dawned on me after spending a few perplexed seconds. Now that was way simpler in excel 2003. Turn on the pivot table toolbar and with two mouse clicks, you are there. The interface pretty much remains the same and I really don’t find it intuitive. That’s part of the reason one doesn’t see this very useful feature being used too often.

Pivot Table Response Time (with VBA)

I checked the speed with which a pivot table can be created using the standard VBA code. This code was run on an unsaved excel workbook so any advantages / disadvantages accruing from the format (.xlsm, .xls) etc (in my opinion) could have been nullified. The average time to create a pivot table with the layout shown below stabilized at 0.671 seconds. Here’s the vba code that we used to create the pivot table in Excel 2007:

Sub Create_Pivot_Excel_2007()
t = Timer
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R109C5", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=ActiveSheet.Cells(1, 1), TableName:="PivotTable3", DefaultVersion _
    With ActiveSheet.PivotTables(1).PivotFields("Product")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables(1).AddDataField ActiveSheet.PivotTables(1).PivotFields("Sales"), "Sum of Sales", xlSum
    With ActiveSheet.PivotTables(1).PivotFields("Region")
        .Orientation = xlColumnField
        .Position = 2
    End With
    With ActiveSheet.PivotTables(1).PivotFields("Store")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(1).PivotFields("Manager")
        .Orientation = xlRowField
        .Position = 1
    End With
MsgBox Timer - t
End Sub

I then tested Excel 2003 for the speed with which it can create a pivot table.

Sub Create_Pivot_Excel_2003()
t = Timer
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R109C5").CreatePivotTable TableDestination:=ActiveSheet.Cells(1, 1), TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
'----------SAME AS ABOVE-----------
MsgBox Timer - t
End Sub

And here are the results. As you can see, Excel 2003 really whips its younger counterpart by a wide margin. (Note: Other parameters such as RAM, CPU power can and will affect results)


Similar tests with a simple refresh opertaion on the pivot table showed that Excel 2003 beat Excel 2007 by a factor of 5. (I haven’t tested performance with external data sources or with moving the fields around within the table and the results there could indicate otherwise.)

So I think everything put together, pivot tables came out looking much better through the MS wringer. But what’s your experience been with them? How do you find the changes? Have they made your life simpler or even more difficult? Comments are open.

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Jose wrote:

    don’t like the pivot tables in 2007 … the pivots in 2003 had a much cleaner crispier display and are way easier to work with.

  2. deanM wrote:

    First of all a cool site … I have been a regular reader. Pivot tables look better in Excel 2007 and retain everything that was there in 2003. I think it takes some time to get used to using them but I have found them every bit as good and many time, better.


  3. Ajay wrote:

    @Jose – I don’t think that I would fully agree with you there. The default pivot table formatting in Excel 2007 is a a bit better i would say. Welcome to da TaB!

    @dean- I agree :-)

  4. Dale wrote:

    How can I move side by side data to a stacked configuration, when i have more than one data value??? (2007)

  5. Sharon wrote:

    I have an excel sheet that has a pivot table created in excel 2003, and now I am using the 2007. When I try to refresh all my numbers round up or down but still leave 3 places after the decimal except they are zeros? what can I do to get this to refresh correctly

  6. George Cardenas wrote:

    I updated a 2003 excel pivot table using VBA, however upgrading to 2007 has messed up the code.

    Before I was able to automatically delete the old data and import new data and then refresh the pivot table, did all that using VBA

    Now the code does not automatically adjust for new rows. After I run my code, I have to go into the data source and manually change the ending field of that data source, then I have to manually do a refresh for the code to work.

    So no it does not work the same.

  7. Ramesh wrote:

    Hi, Thanks for the post. Well explained tutorial. You could include downloadable files too..


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel