PIVOTTABLES - PART 2

Online Excel PivotTables Tutorial by Microsoft

LESSON WORKBOOK:  Level 3 Lesson 4 2007.xlsx

More Complicated PivotTables

Hopefully since you have completed the first part of our PivotTable lessons, you have had time to construct and use a few simple pivottables.  No doubt you would have encountered a few problems, which is inevitable, but this will also enable you to thoroughly explore this very useful function. 

In this next lesson on PivotTables, we will look at constructing some more complicated PivotTables and extracting meaningful data from them.  Remember, that some of the button names and descriptions I am using may differ very slightly between different versions of Excel.

Open up the attached workbook Level 3 Lesson 4 2007.xlsx and on the worksheet Pivot1 we will use the data to complete another relatively simple PivotTable.  This PivotTable however, consists of four columns.

  1. The first thing we need to do is to select any single cell within our table

  2. Go to the Insert tab and select PivotTable under Tables options and accept the defaults.

  3. Click OK

  4. Drag the Names field button to Row Labels

  5. Drag the Age field button to Row Labels, so it is sitting directly below Names

  6. Drag the Sex field up to the Report Filter area

  7. Drag the Date of Birth field to the Row Labels area

  8. Drag the Ages field to the Values area

You should notice that this will default to Sum of Age.  What we would like is a count of ages.

  1. Click the Sum of Age field

  2. Select Value Field Settings from the list, then select Count

  3. There is no need to make any other selections now, so click OK

At this stage we should have a very basic PivotTable which has grouped together the duplicate names, which is only the name Bill in this case.  Click on the switch in cell B1 and you will see the words All, Female and Male.  All is highlighted.  This is simply Excel telling us that our PivotTable is displaying ALL sexes, which in this case is obviously male and female.  You can also see this list by clicking on the word Sex in the left hand side of the PivotTable Field List dialog, (not the word Sex in the Report Filter).  You will notice it has an arrow to the right of it.  

  1. Click the drop arrow you will see that we have two other choices, Female and Male.

  2. Toggle between these two choices and you will see your PivotTable change accordingly.

  3. Once finished, set back to All

One thing you will often notice when you create PivotTables is that they generally tend to give you by default, more information than is actually required.  This can make PivotTables look very cluttered and hard to read, which in turn means they are not giving us the information that we want as it becomes too confusing.  Bear this in mind - don't overdo it. 

In the PivotTable that we have created, most of the information that we have is relevant.  The exception to this, arguably, would be the totals that we have in the Age field.  What we can do is very easily remove these.  To do this, all we need to do is select the Design  tab on the ribbon and under Layout select Subtotals>Do Not Show Subtotals

Grouping Fields

Lets now assume that we want to actually group these people by the years in which they were born.  In other words, we want all people born in the same year to be grouped together.  Grouping data in a PivotTable can at times be very frustrating as when you try to group, Excel can tell you that it "Cannot Group that Selection", or is "Unable to Group" the particular field you are trying to group, or the result of the grouping is not what you expect. 

This may be a good time to actually address these common problems and tell you the reasons as to why they occur.  The first problem "Cannot Group that Selection" is normally caused by one of two things.  The first, and most likely cause, is due to the fact that your PivotTable data range includes blank cells within the field in which you are trying to group.  For example, if you had (as we do) a column for the date of birth and within that column somewhere there was only one blank cell, Excel would not longer allow us to group by Date of Birth.  This is because Excel does not know what it should do with the one blank cell in regards to grouping with date cells.  Basically, what this means is your column must:

  1. Have no blank fields

  2. Your data range for your PivotTable should not exceed past the last row of data.

Obviously, it will be quite common once you have created a PivotTable of data that you would be adding new information to that table on an ongoing basis.  The temptation is always there to make your data range for your PivotTable extend well past the last row of information in your table and thus include many blank cells.  Obviously you would do this so when you add more information to your table it would automatically be included in your PivotTable the moment you refresh it.

Extending the data range of your PivotTable well beyond the last row in your table will not only cause grouping problems, but also force Excel to reserve excess memory for all of the blank cells you have included.  This, depending on how many extra rows you include, can end up increasing your file size by 100% or greater.

The solution to this common problem is quite simple and comes in the form of using a dynamic named range (discussed later).

The other common reason for not being able to group data is that you have both text and numeric data in the same field.  This one can be a little bit hard to spot at times, but just remember by default numbers are right aligned and text is left aligned.  Normally, simply going back to your data table, selecting the column which contains what you think is numeric data, changing the alignment to General under Format Cells>Alignment (right click) will force Excel to return the numbers to their original state, although this will not change their format.  You may have to widen your column a little, to actually see whether your data is left aligned (text) or right aligned (numbers).

So, to group all our PivotTable data by years:

  1. Select any date cell within your Date of Birth field.

  2. Select Group Selection under Group options on the Options tab (or right click and select Group)

This will pull up the Grouping dialog box which will display the appropriate grouping for data formatted as dates.  You may notice that the Starting at: and Ending at: dates both have check boxes to the left of them which are checked.  The reason for this is that Excel will automatically determine the starting date (earliest date) in your source data.  It will also determine your ending date (or latest date).  Normally these boxes would be left checked so that we are always starting at our earliest date and finishing at our latest.  You will notice that if you try to type in the Starting at or Ending at boxes, the Auto: checkboxes will automatically uncheck themselves as Excel will make the assumption that you want to override the automatic feature.  This is not the case for our example, so ensure both the boxes are still checked.

As I mentioned earlier, we are going to group by years, so simply scroll down and highlight the word Year.  Notice when you do this that the default "Months" remains highlighted.  Simply click this to unhighlight it.  Now click OK.  If you now look at your PivotTable, you should be looking at another common problem that happens when people try to group data.  Rather than our data being grouped in years, all Excel has done is convert our dates to display the year only.  Not exactly what we are after.  This same type of problem can occur with all types of grouping, the reason for this is because the field which we are grouping is not the inner most row field.  At present, our Names heading is the inner most row field.  Lets change that now by left clicking on our Date of Birth field (column heading in C4) and dragging it to the left and dropping it in A4 so that it becomes the inner most row field (left).  Now select Group Selection under Group options on the Options tab, scroll down and highlight the word Year ensuring the you unhighlight Months.

Now that we have done this, our Date of Birth field should be grouped by years.  Now ungroup the data, by simply select UnGroup on the ribbon.

Let's now group our data again, but this time by age.  At present our Age field should be in colulmn C.  This time when you select the Grouping dialog box this time, you will notice that it is slightly different in that Excel knows that you are working with numbers and not dates and so displays the appropriate Grouping Dialog box.  The difference you will notice is the By: box at the bottom.  In here you should have the number 1.  This is Excel automatically determining the number of items in each group when you group items in a field.  We can easily change this by typing a number in the By: box to change the number of items in each group.  Not really applicable in this situation.  Leave the Starting at: box checked, and uncheck the Ending at: box.  In the Ending at: box, type the age 25.  Click OK and you should see that having the field which is being grouped not in the inner most field (right) causes our PivotTable to be grouped in a way to show results we did not expect.  Hit the Undo or Ungroup command and drag the Age field to the inner most field (left) and our PivotTable will be grouped by ages. 

  1. Age

  2. Date of Birth

  3. Names

  4. Sex (as the Page field)

With all displaying.

Field Options

Lets now look at some options that we can use to change our PivotTable fields. 

Before we do so though, lets group our Age field (innermost) in groups of 5.  This simply means clicking in the Age field, selecting Group Selection under Group options on the Options tab.  Ensure Starting at: and Ending at: are checked, and in the By: box type the number 5 and click OK.  This should mean our PivotTable is grouped by ages in groups of 5 (where possible).

Double click on any of our existing row field headings.  This will bring up the Field Settings dialog box.  You will notice that in here, the very first box Custom Name is where we can change the name of this field if we so wish.  Also below this, notice that we have options for SubTotals.  The three choices are Automatics, None and Custom.  Depending on the data in the field chosen, will depend on whether you have by default Automatic or None checked.  A point to note here is that if you apply a SubTotal to a page field you would not see the result of your SubTotal until it was moved to the row or column area of the PivotTable.  Lets apply a SubTotal so that we can get an average of our ages in each group.  To do this, check the word Custom in the SubTotals list box, then click Average.

Lets back out at this point so we can see the effect our SubTotals have on our PivotTables, so simply click OK.  If you now look at your PivotTable, in the Total column you should have the average age for each group.  Double click on the Age row field heading to get back to the Field Settings dialog box.  Now click the Layout & Print tab and you will notice various options.  By default the display options will be Show items in tabular form.  The other options can be best explained by you yourself selecting one option at a time, and clicking OK.  View your changes to see the difference it has made to your PivotTable.  Try not to fall into the trap of making more than one change at a time or you will not know which option has made the change. 

What I would like you to do for yourself using the PivotTable you have just created (or if you wish, use your own data) and go through playing about with the different options we have discussed in this lesson so that you get a good feel for what each of them do.  By all means do try other options which have not been discussed here, such as the Sort option on the ribbon and any others you like.

In the next and final lesson on PivotTables we will be looking at some of the more advanced features and going through PivotCharts.

Online Excel PivotTables Tutorial by Microsoft

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.