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:

=(323,332-317,718)/317,718

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%!!!!!!

Two minor aesthetic comments:

1. In the second Step 3, I would retain the (user's) original title, namely Grand Total.

2. After following the second set of Steps 1 through 6, the color of the Grand Total line is not the same as before (blue background).

I remedied that by simply selecting the A10:D10 in the pivot table and selecting the appropriate blue shade from the Fill Color icon in the Home > Font tab.

As a pivot table neophite, I don't know if that additional step is correct, necessary and worthy of mention. But I thought I'd bring it to your attention for consideration.

(Perhaps there is a way to preserve the original backgroun color by doing something other than Remove Grand Total in Step 1.)

Thank you for sharing your thoughts.

Ashish,

I truly believe this is remarkable. I just learned something new. Thank you for sharing your knowledge.

Thank you. You are most welcome.

Thank You. Excellent Post!

Would your process work if the table was as follows: (where one column only has 2009 Amount and one column only has 2010 Amount).

Account 2009 Amount 2010 Amount

Ret Sales 130,000 140000

Ret Sales 200,000 210000

Met Sales 53,000 63000

Met Sales 65,000 75000

Det Sales 77,000 87000

Det Sales 25,999 35999

Fet Sales 48,793 58793

Fet Sales 22,333 32333

Get Sales 8,925 18925

Get Sales 10,000 20000

How would the equations change?

------------Revenues:=sum(Dummy[Amount])

------------Growth (%):=(CALCULATE([Revenues],Dummy[Year]=2010)-CALCULATE([Revenues],Dummy[Year]=2009))/CALCULATE([Revenues],Dummy[Year]=2009)

The table from your example is below:

Account Amount Year

Ret Sales 130,000 2009

Ret Sales 200,000 2010

Met Sales 53,000 2009

Met Sales 65,000 2010

Det Sales 77,000 2009

Det Sales 25,999 2010

Fet Sales 48,793 2009

Fet Sales 22,333 2010

Get Sales 8,925 2009

Get Sales 10,000 2010

Hi,

Thank you. You may refer to my solution in this workbook.

Hope this helps.

Excellent idea. Thank you for one of the best websites for useful Powerpivot application ideas.

You are welcome. I am glad that you liked the content on my website.

Thank you so much for this extremely useful post. This is something we've been trying to find a solution to for ages in our office!

You are most welcome. Thank you for the feedback.

Hi,

How would this work if the years are going to be dynamic. i.e If I have page filter for country. Country A has data for year 2009 to 2012 and another country B has 2009 to 2014.

Hi,

This should be possible to resolve. Please upload your workbook to OneDrive and share the link of the workbook here.

[…] Computing growth % inside a pivot table 2. Compute Pro rata growth rate within a Pivot […]

What if your sales data was -60k for 2009 and -20k for 2010. The growth would show negative when in fact it is positive. I'm dealing with Gross Profit data that has this and I can't figure out how to take that into consideration (two negatives). Any help would be greatly appreciated.

Hi,

This should be possible to do in a Pivot Table. Upload your workbook to OneDrive and share the download link here.

The download link is:

https://onedrive.live.com/redir?resid=A7075E230B7720B7%21113

Hi,

Please refer to my solution in the attached workbook. I have made a correction for the Calculated Field titled as "% Change GP".

Hope this helps.

If you'll notice on row 7 (upon opening), 2014 GP has actually improved by $40k. It is still showing a -5.35% change however. I've used calculated fields for all columns except for the Sales $ figures and GP $ figures.

Wow, thank you so much Sir!!! I can't thank you enough! This was such a thorn in my side.

You are most welcome.