Figure 906 Sales are back to 127, but you can’t see the product details.

Alternate Strategy: Instead of trying to use a calculated pivot item, you can add a Plant column to the original data. You can then produce a report that shows both the plant location and the products made at the plant, and the total will be correct (127).

Figure 907 Adding plant info to the original data set solves the problem.

Calculated pivot items sound like they should be useful, but they are not. You should avoid using them.

Figure 908 Group the selected cells.

The result appears to be chaos. You will be able to fix this problem, but let’s take a look at what happened below. There is a new virtual field called Office2 in the pivot table. Three cities belong to a value called Group1. Every other office in the pivot table is assigned to an Office2 equal to the office name. Note that the grand total of 2927 did not change.

Additional Details: If your VP of Sales is like my VP of sales, he will decide to re-balance the territories (several times, right?). This process is fairly easy. First, click Expand Entire Field. Then, choose the offices in column B that should be re-grouped. Below, he asked you to add Orlando to the Gulf Coast group.

Hint: after the tenth iteration, try adding some formatting to the pivot table. Maybe he will think this one looks better.

Figure 914 Territory balancing is an iterative process.

CALCULATIONS OUTSIDE OF PIVOT TABLES

Problem: I need to add a calculation in the grid outside of the pivot table that points to cells in the pivot table. Whenever I copy the formula, I get the exact same result!

Strategy: This started happening in Excel 2002. It is very annoying. I call it the GetPivotData bug.

Here is how it happens. In the figure below, you’ve already grouped daily dates to months and years. Normally, you could add a calculated item to calculate growth rate as (2015/2014)-1, but calculated items

are not allowed in grouped pivot tables. So, you went to cell D5, typed an equals sign, clicked on C5, typed a slash, clicked on B5, and pressed enter. The 14.54% is the correct growth rate. So, you then copied the formula down. Somehow, the growth rate for every month is identical.

Figure 915 Sales went down in March 2015 from March 2014, there is no way that 14.54% growth is correct.

Select D5 and look in the formula bar. The formula there is =GETPIVOTDATA(“Revenue”, $A$3,”Date”,1, ”Years”, 2015) / GETPIVOTDATA( “Revenue”, $A$3,”Date”,1, ”Years”,2014)-1. There is no way that you typed any of that. You simply used the mouse when building the formula.

This also happens if you use the arrow keys. Equals Sign, Left, Slash, Left, Left, Minus, One, Enter will normally create a formula of =C5/B5-1, but in this case, you get the formula with two GETPIVOTDATA formulas.

What is GETPIVOTDATA and how did it get in your worksheet? The Excel team is hoping that you would see GETPIVOTDATA, then go find out what it is and learn to love it and use it all the time. But that is never what happens. Instead, people are annoyed by it.

As an aside, I spent eight years hating GETPIVOTDATA, but now I understand it and occasionally even use it. See “Can I Save Formatting in a Template” for an example of when you would want to use it.

The big question is how to enter a formula without getting the GETPIVOTDATA. One quick and easy way is to type the formula without using the mouse or the arrow keys. Just type =C5/B5-1. This creates a formula that will copy.

The other method is to permanently turn off the feature to generate GETPIVOTDATA. To do this, chose

File, Options, Formulas. There is a checkbox for Use GetPivotData Functions For PivotTable References.

Turn this off.

Additional Details: the other common problem with formulas outside of pivot tables is that they don’t deal well with the changing size of pivot tables. In Q1, six reason codes are found, so the % of Total formula points to G$11.