Tip:
Highlight text to annotate it
X
Pivot Tables also allow you to group certain items together. There are three main ways
of grouping but letís first look at the manual grouping method. In this pivot table youíll
notice that weíve got 5 separate items here. And letís say weíd like these 3 to be grouped.
The process is very simple. In the pivot you highlight the areas you want grouped, you
right click and youíll see down here there is something called ìGroupî. When I push
Group ñ what it does it creates a new row label- calls it Group 1 and that item is now
grouped together. Just looking at your field list here, if you go right to the bottom,
youíll also notice it has created another row label here, which you can now drag into
where ever you want to go. If you need to, you can change this name. So, for example,
these might be Cell Companies and the Pivot Table will remember this name, so, for example,
if I come here and remove this row label, youíll see we are back where we started.
Come back in here, pull it down, put it back in and youíll see it has remembered the grouping
name. You can group many items. To ungroup, itís as simple as you right click, go ungroup
and youíll see the grouping is lost and the row label is gone.
Letís now change the pivot table, so that the destination networks are over here, get
rid of months and now put the date in over here. So now what we have is all the dates
in the row labels and we now want to do some grouping. Excel is very clever in the sense
that if it identifies in a row label that there are dates, when you attempt to group
it, it brings up its own specialized little dialogue box.
Before we get into that I just want to show you a common problem with pivot tables. I
am just going to cancel it, go into Phone Bill and now I am just going to delete this
one item ñ one single date out of a whole list of dates. Go back here ñ I am just going
to refresh this. If I try now right click and say Group ñ notice is says it cannot
group the selection. For whatever reason, because of this one single cell which doesnít
have a similar date, Excel has decided that it cannot understand what it must do with
this cell and therefore the rest of the cells cannot be grouped. So you have to be careful
if you ever do this ñ you try to do a grouping and it doesnít work, you need to go look
in the data and look for cells that are blank or have a number in or word in. This commonly
happens when right at the bottom, instead of doing the pivot table up to that row, perhaps
you add a few more rows in and as a result Excel sees the blanks.
Just to get it back ñ weíve just got to fill that in and just refresh. Going back
to the dates, we can right click and say Group. Now letís investigate what we have got here.
It allows you to decide where you want to start and end, so similar to the Filter. But
then you can say I want to group it by: and you can see we have got the options of by
seconds, minutes, hours, days, months, quarters or years. So letís start out with months
ñ so I have clicked on months. When I say OK, youíll see what it has done,it has summarized
the dates to the 3 months that it sees. To ungroup, it is very simple, you click and
you say ungroup. Letís go back in there, Group. Letís say we want to do it by quarters
ñ when I click on Quarters, notice both are highlighted- so if I only want quarters, I
need to switch off the months. I can click on that and say OK. In this case we only have
one quarter. Letís just go back to grouping. Letís do quarters and months ñ when I click
OK, youíll see it creates multiple row labels and basically works its way down through the
groupings. I am going to right click again and say Group.
Letís now switch off the quarters and the months and put it onto dates and notice, if
you activate the days function, you can actually specify the number of days ñ so if we want
to group these dates by perhaps weeks. So letís make it group these in days of 7 and
I say OK youíll see it groups it into days of 7 ñ so in this case from the 25th to the
31st and from the 1st to the 7th etc. I am going to remove the grouping ñ just go ìungroupî.
Another interesting option is Grouping Numbers. So letís first get the pivot table correct
ñ get rid of the dates, get rid of the destination network and in the Values weíve got the Sum
of Bill. What Iím going to do is I am going to pull the bill through again, but this time
I am going to take it and put it into the row labels. When I let go, youíll notice
we have got all the numbers here. Now this will also have the same issue explained with
the dates, in the sense that if one of the cells in here is a blank or a word, it wonít
allow you to group. Letís just understand what we have got here. What Excel has done
is taken all the Bill data, found the unique numbers and put them as row labels. So, for
example, it looks for the row label of 2 and it has only found one of them so it adds up
to 2. But perhaps over here for that 6 , it actually must have found two of them that
can add it up to 12. So all this is is a grouping or a unique listing of all the items in here
and this adds them up. This of itself is not a particularly useful report, you can see
it just goes on and on and on. But by using the Grouping Feature, you can make it useful.
So when I right click on it and I say ìGroupî, because Excel sees that these are numbers,
it gives us a different type of dialogue box. And in this case it is saying, OK, I am going
to start at zero because thatís what its found as the lowest, it is going to end at
36.35 which is probably the maximum and at the moment it is grouping it by groups of
10. Letís just change this and say letís go up to say 30 and letís group it in groups
of 5. When I say OK, youíll see what it produces, is a very nice report which tells me that
from R0 to R5 spent on the bill, those type of calls add up to 943. Calls that range from
R5 to R10, add up to 574. So in this case you can actually analyze, where the majority
of your calls are happening ñ what length of time. So youíll notice that the R15-R20
calls are 115 but once you go over a certain point, once you get to the R20 to R25 calls,
you seem to spend a little more here. We are going to get rid of the grouping or change
the grouping ñ to change the grouping, you click on Group, you can make the changes.
Or to get rid of it, you right click and you say ìungroupî and it is gone.