Having created a Pivot Table from a Base_Data sheet, one may want to compute the Account wise and Growth % of 2010 over 2009.
There are two ways one can go about answering the question above:
Solution A – For Excel 2010 and higher versions – This solution is for those using the PowerPivot MS Excel add-in for Excel 2010 and higher versions.
Solution B – For all versions of MS Excel – This solution will work in all versions of MS Excel but for those using Excel 2010 and higher versions, the PowerPivot solution would be far more efficient.
The steps for creating a pivot table under Solution B are:
1. Select cell C4 on the sheet “Pivot Table”
2. Go to Pivot Table Tools > Options > Fields, Items and Sets > Calculate Item. In Excel 2007, this will be Pivot Table Tools > Options > Formulas > Calculated Item
3. In the Name box, type Growth
4. In the Formula bar, enter =(‘2010′ /’2009’ )-1 and press OK
5. Select the Growth column in the pivot table and press Ctrl+Shift+5 to convert the decimals to percentages
The problem with the above process is that the Growth % in the Grand Total column (cell D10) will be incorrect i.e. -31.93%. Unfortunately, this is the simple summation of the Growth % above. Ideally, this should be 1.77%and it should be computed as:
First of all, this problem is happening because cell D10 is at the intersection of two formulas – one is Excel’s default SUM function for the Grand Total column and the other is the calculated field formula created above. We need to prioritise the application of formula on this particular cell i.e. the Growth % formula written above should take precedence over the default SUM function. To accomplish this, try the following steps:
1. Right click on cell A10 (Grand Total) and select Remove Grand Total
2. Select cell A9 and go to Pivot Table Tools > Options > Fields, Items and Sets > Calculate Item. In Excel 2007, this will be Pivot Table Tools > Options > Formulas > Calculated Item
3. In the Name box, type Total
4. In the Formula bar, enter =Ret Sales + Met Sales + Det Sales + Fet Sales + Get Sales. You can do so by double clicking on the individual items available
5. After performing the steps above, the problem would be the same as we had earlier i.e. the Growth % in cell D10 will show -31.93%
6. To prioritise calculations in cell D10, try this:
a. Select any one cell in the pivot table and go to Pivot Table Tools > Options > Fields, Items and Sets > Solve Order. In Excel 2007, this will be Pivot Table Tools > Options > Formulas > Solve Order
b. Select the first item and click on Move Down
c. Click on Close
The Growth % in cell D10 should now appear as 1.77%.
Change in growth from -31.93% to 1.77%!!!!!!