Home // Video Tutorials // 6 - 10 (Advanced) // 6 // Group Pivot Table data

Group Pivot Table data

How to group Pivot table reports e.g. group dates into months, group items together or group numbers into relevant bands.

Group Pivot Table data

Pivot Tables for Excel 2007Pivot 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.