Excel Pivot Table: Adding single column in grouped pivot table

Question

Using a pivot table with this setup:

  Name    Amount         Total
        Jan  Feb  ...
  A      1    2            3
  B      2    1            3
  C      4    1            5
  D      3    3            6
Total   10    7            17

The data is coming from a table where each "Amount" has a specific date (ex 1/1/2016, or 2/4/2016), these dates are then grouped by month in the Pivot Table. This pivot table will be changing/months will be added, so its not a fixed size. I would like to end up with something like this:

  Name    Amount         Total   %
        Jan  Feb  ...
  A      1    2            3     17%
  B      2    1            3     17%
  C      4    1            5     29%
  D      3    3            6     35%
Total   10    7            17    100%

But when I try to do this by adding a percent column in the data source, or by using a Calculated Field, a % column appears for each Amount column. ex:

Name          Amount                 Total 
        Jan  %      Feb  %   ...
  A      1   5%      2   11%          17%
  B      2   11%     1   5%           17%
  C      4   23%     1   5%           29%
  D      3   17%     3   17%          35%
Total   10   58%     7   41%          100%

*Note that my Grand Total Field is automatically removed

Is there any way of achieving my desired result with no VBA, and no upkeep of pivot table (hiding/unhiding rows, adding a separate table at end of pivot table that will be deleted when new months are added)

I would also like to keep the automatic Grand Total field, however I can just manually create one in my source data. (Assuming I can figure out how to insert single columns in a pivot table)

hinton888 hinton888 · Accepted Answer · 2016-01-22T16:06:37

In your pivot table you need to turn off subtotals, this is on by default. The percentages after each month are subtotals, whilst the last column of percentages is called the grand total. The grand total is the one you want.

To turn off subtotals you need to go the design tab and click on the "Do not show subtotals" selection of the subtotals.

Excel Pivot Table: Adding single column in grouped pivot table

2 Answers