Compute Pro rata growth rate within a Pivot Table

Assume a four column input data range (refer sheet named “Input” of this workbook) arranged as follows:

1. First tow columns are Group and Type which have text values
2. Third column is a month column with entries such as Dec_11 and Sep_12.  These denote 12 months ended December 2011 and 9 months ended 2012
3. Fourth column is Revenue which has numbers

The objective is to compute pro rata growth rate of Sep_12 revenue over Dec_11 revenue within a Pivot Table.  In the workbook (see link above), I tried to use a calculated item formula within a Pivot Table to compute this but encountered three difficulties in doing so (refer Notes section of sheet named “Result of Calc item formula “).

There are two ways one can go about answering the questions 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:

The Objective is to change the orientation of the base data such that a calculated field formula can be written within a Pivot table instead of a calculated item formula.  The process to change the orientation of the base data is described below:

1. Select range A2:D14 of Input sheet and press Ctrl+F3 > New.  In the name box, type Dummy
2. Select range A2:D14 of Input sheet and press Ctrl+T to convert to a Table
3. Open a third worksheet in the workbook (downloaded from the link above) and save the workbook, say on any folder on the desktop
4. While the active cell is any cell of the new worksheet, go to Data > From Other Sources > From Microsoft Query
5. Under Databases, select Excel files > OK
6. In the Directories dialog box, navigate to the folder on the desktop where the workbook is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and click on the folder where the workbook is saved
7. In the left hand side window, select the workbook and click on OK
8. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box
9. Click on Next three times
10. Select the option of View Data or Edit Query in Microsoft Query
11. Click on the SQL button and delete whatever you see in the box there
12. Enter the following SQL statement in the white box

Transform sum(rev)
SELECT Dummy.`GROUP`, Dummy.TYPE
FROM Dummy
Group by Dummy.`GROUP`, Dummy.TYPE
Pivot Mth

13. When you click on OK, you will see a four column database (refer sheet named “Result of MS Query”)
14. Under File, select the last option – Return Data to Microsoft Excel
15. At this stage, if you wish to get data as you saw in MS Query then select Table.  If you directly want a pivot table, select the second option button – Pivot Table.  Select any cell where you would like to the result to appear, say cell A1. 16. Click on OK.  A counter will run at the bottom left hand side with the title of Reading Data
17. If you had selected pivot table in step 15 above, then the pivot table grid/layout will appear
18. You may now drag fields to create a pivot table
19. You may now write a calculated field formula within the Pivot Table (refer sheet named “Output”)

As can be observed in the “Output” sheet, all the shortcomings mentioned in the Notes section of the “Result of Calc item formula” sheet have been overcome.