Thursday, February 26, 2009

5 Things Wrong with Excel Pivot Tables

Ok, so I'm pissed off at Pivot Tables.

Don't get me wrong, I love Pivot Tables; I use them all the time to figure out what all the crappy data that the client gave me actually means.  But a couple of defaults that really annoy me, and I don't know how to fix them. I'm hoping that some real Excel wizards out there can point me towards solutions for 5 issues.

Situation summary: We have a large datacube of raw data, containing revenue figures by year for a variety of products, both historical and forecast.  The data set is approximately 5,000 rows, and we want to create a summary, by product, of revenues from 1999-2007, in order to figure out what's going on.

Fig. 1. Our Data Set

The first thing I do is put the Products category on the left side. Then, I drag over the first data column - 1999REV.

Issue #1: Because there are some blank rows in 1999REV, Excel sets the field to "Count" instead of "Sum." If I change 1999REV to Sum, remove the field, and add it back in, it is set to "Count".

Q: Is there any way to change to set the default field calculation to "Sum"?

Fig. 2. Field is counted, not summed.

To change this, you have to do a bunch of mouse clicks, or use the keyboard (Alt+P, N, Tab, Up Arrow, Enter).

Fig. 3. Changing the field to sum, not count. Alt+P, N, Tab, Up Arrow, Enter. Don't ask me why I know this.

Next, I want to start adding the REV2000 through REV2007 fields.

Issue #2: Once you add more than 1 data field, Excel, for reasons unknown to me, groups them horizontally, instead of vertically. For the life of me, I can't think of a single reason why anyone would ever want to look at the data this way.

Q: When putting multiple data fields in a pivot table, can you set the default order as vertical (by columns) instead of by horizontal (by rows)?

Fig. 4. Pulling over a second data field.

Ok, so we can fix that by dragging the data fields over to the column area.

Issue #3: I need to do my "sum, not count" change again. You can only change the calculation of one field at a time.

Q: Is there any way to change the calculation of multiple fields at once?

Fig. 5. Grouping the data vertically instead of horizontally.

Issue #4: Excel shows all the digits it can, making the table hard to read. Who can read a table that has 4 numbers past the decimal showing?

Q: Can Excel pick up the formatting of the source data?

Fig. 6. Reformatting the data so you can actually read the numbers.

Now I need to add the REV2001 to REV2007 fields, and just like before, manually change each one to "Sum", not "Count". As I add them, Excel automatically resizes the columns to read the entire column.

Issue #5: This spacing breaks the Gestalt law of Proximity - or said more plainly, the spacing makes it harder for me to understand the data. I can shrink the columns, but if I modify the pivot table, they automatically expand out again. The titles are always going to be much longer than the numbers.

This is due to Autoformat Table (in the "Table Options" menu within Pivot Tables) being checked. This is always checked in a new pivot table.

Q: Can Autoformat table be unchecked by default?

Fig. 7. The auto-formatted table is difficult to read because the numbers are spaced so widely

Fig. 8. Turning off auto-formatting.

Finally, I get to the solution. All that effort is mechanics, and now gets me to the important part, which is to understand the data.

Fig. 9. My end goal.

All this extra effort is, to use Lean Terminology, "Waste," that can be eliminated without reducing the quality of the final output. So, for the Excel Experts out there:

Summary of Questions:
Q1: Can you set the default field calculation to "Sum"?
Q2: Can you change the calculation of multiple fields at once?
Q3: When putting multiple data fields in a pivot table, can you set the default order as vertical (by columns) instead of by horizontal (by rows) for new Pivot Tables?
Q4: Can Excel pick up the formatting of the source data and apply it to the pivot table field?
Q5: Can Autoformat table be unchecked by default for new Pivot Tables?


Anonymous said...

Consultant Ninja said...

ActiveSheet.PivotTables("PivotTable1").HasAutoFormat = False

ActiveSheet.PivotTables("PivotTable3").DataPivotField.Orientation = xlColumnField

ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of 2001REV").Function = xlSum

It's easy to whip up a macro to run these, but that doesn't save any time. They need to be inline, seamless, so that when a new pivot table is created they are invoked.

Consultant Ninja said...


that add-in solves Q2, but not all the rest.

Is there any sort of VB code method call for
OnPivotTable() or OnFieldAdd() (in essence, a way for a macro to run when a pivot table is selected, or a field is added?)

Paul Kokovas said...


I can sympathise with your frustration! I've come across many of the same limitations but I've not been able to find a way to overcome them whilst making it appear as native operation of MS Excel.

Alternatives I've tried in the past:

1). Search the Internet for Excel plug-ins. Usually they will fulfil a single purpose but not solve the problem entirely. Plus you have to pay for the good ones!

2). Write a macro that you can apply to pivot tables you create. Basically you can automate all of which you mentioned ... but you may find yourself morphing into a VBA developer over time. There is nothing worse than trying to debug VBA code when you have a deadline.

3). Develop a template. Format the pivot table output exactly the way you like and reference an "input" sheet (and turn off Auto-format). You can also add pre-canned analysis and chart features on that pivot table too. That way when you change the data in the input sheet, you only have to refresh the pivot table. You may have to make tweaks over time, but I found that I would get to an 80/20 happy medium after about 3 goes at a specific problem space.

I have a bias towards the last option as I object to paying for plug-ins that overcome feature limitations in already purchased software ... and I don't particularly enjoy VBA coding! Plus you can hand to final result to someone with moderate Excel skills and they can use and build on it.

Anonymous said...

Keyboard Shortcut to turn off auto-formatting:

Alt+P, O, Tab, A, Enter.

Thursday, February 26, 2009