Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

enter image description here

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.

share|improve this answer
    
I'm facing exactly the same problem. Could you please describe how to build Pivot Table for this case. –  madhead May 29 at 8:07

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%

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.