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?
