Can I group all rows with a percentage of column total less than 1% in an Excel 2010 pivot table?

Question

This is how my data looks right now, based on hours of work logged per project in 2011:

Proj. Hrs %
A     15.6%
C     7.3&
...
X     6.1%
D     5.3%
Q     1.8%
F     1.6%
H     0.7%
Total 100%

I would like to group smaller projects into a single entry, where a smaller project is one that I have booked less than 2% of my hours on.

Proj. Hrs %
A     15.6%
C     7.3&
...
X     6.1%
D     5.3%
Rest  4.1% <<< Group of all proj < 2% with total % for all combined
Total 100%

How can I do that? Do I have to change the data before I make the pivot table, or can I do it with the pivot table I have already?

Answer 1

1) First, you can do it manually by creating a group on your Project row label. Just select projects under 2%, and right-click Group By.

2) Second, if you want something more dynamic, one way to do with would be to create a Group Field on a % row label on your pivot table. Unfortunately this requires that the % column comes from your source data since a Pivot Table cannot group its own summarized values.

So adding a formula to calculate the % in your source data would allow this method to work, as shown below. One group will be called >2% and be left expanded, while the <2% group will be collapsed.

If calculating % in your source data is too cumbersome, another option is to create a pivot table on an intermediary pivot table, but that can bring a new set of issues.

Answer 2

You are going to have to do the summation group BEFORE you go to the pivot table.

Probably the simplest way to make this work would be to use a sumif formula - like this:
=SUMIF(C5:C15,"<=0.02",C5:C15)

That will give you a single cell with anything = to or less than 2%. Use an IF function to filter your percentages in a new column. Using your examples, like this:
=IF(C5>0.02,C5,0)

So now your source range looks like:

Proj.    Hrs %        Hrs2%
A        15.60%       15.6%     (these are IF statements.)
C        7.30%        7.3%
X        6.10%        6.1%
D        5.30%        5.3%
Q        1.80%        0.0%
F        1.60%        0.0%
H        0.70%        0.0%
B        24.20%       24.2%
M        15.00%       15.0%
J        20.40%       20.4%
p        2.00%        0.0%

Rest                  6.1%  (this is the sumif statement)

Set the pivot table to include the "Rest" row in the source data range, and the filter not to show 0 values in the pivot table.

You may have to play with it some - the natural sort will show your "rest" where the value is. In the data above, "Rest" after everything except project X, which also has a 6.1% value. You could jigger your naming convention to work around that.