 |
|
|
Online Excel Pivot Tables Tutorial by Microsoft
Hopefully since you have completed the last lesson, you have had time to construct and use a few simple pivot tables. 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 Pivot Tables, we will look at constructing some more complicated pivot tables 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 and on the worksheet Pivot1 we will use the data to complete another relatively simple pivot table. This pivot table however, consists of four columns.
The first thing we need to do is to select any single cell within our table
Go to Data>Pivot Table and Pivot Chart Report and accept the defaults in Step 1 (that is Microsoft List or Database).
Click on the Next button
Ensure the range address in the range box is A1:D20
Click Next
Again at this point, rather than finishing our Pivot Table now, we will jump into the layout options and set it up in the Wizard.
Click the Layout Button
Drag the Names field button to the Row field
Drag the Age field button to the Row field, so it is sitting directly below Names
Drag the Sex field up to the Page field area
Drag the Date of Birth field to the Row field area
Drag the Ages field to the Data area
You should notice that this will default to Sum of Age. What we would like is a count of ages.
Double click the Sum of Age field
In the Summarise by: box, select Count
There is no need to make any other selections now, so click OK
Click OK again to move back to the final step.
Ensure the New Worksheet option is selected
Click Finish.
At this stage we should have a very basic Pivot Table which has grouped together the duplicate names, which is only the name Bill in this case. You will notice that in the Page field named Sex that the word (All) is showing. This is simply Excel telling us that our Pivot Table is displaying ALL sexes, which in this case is obviously male and female.
Click the drop arrow to the right of this and you will see that we have two other choices, Female and Male.
Toggle between these two choices and you will see your pivot table change accordingly.
Once finished, set back to All
One thing you will often notice when you create Pivot Tables is that they generally tend to give you by default, more information than is actually required. This can make Pivot Tables 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. As you become more comfortable with Pivot Tables you will know during your set up process via the Wizard what should and should not be showing. But as with all things, this will come in time and it is not to be expected that you will know this without constant practice. In a nutshell this means you will probably end up dragging on to your Pivot Table fields more of your field headings than is really needed.
In the Pivot Table 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 simply need to do is select any one of the totals in the Age field, right click and select Hide. (This is named Delete in Excel 97). By doing this we should now have only a total for each of the names in the list.
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 Pivot Table can at times be very frustrating as when you try to group, Excel can tell you that it "Cannot Group that Selection", 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 Pivot Table 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:
Have no blank fields
Your data range for your Pivot Table should not exceed past the last row of data.
Obviously, it will be quite common once you have created a Pivot Table 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 Pivot Table 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 Pivot Table the moment you refresh it.
Extending the data range of your pivot table 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.
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 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 Pivot Table data by years:
Select any date cell within your Date of Birth field.
Right click, select Group and Outline then 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 Pivot Table, 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 and dragging it to the left and dropping it so that it becomes the inner most row field (left).
Now that we have done this, our Date of Birth field should be grouped by years. To ungroup any data, simply right click on any of the grouped fields, go to Group and Outline and select Ungroup.
Let's now group our data again, but this time by age. At present our Age field should be the right outer-most field, so lets leave it here for now and group our data by age while it is in the outer most field. When you select the Grouping dialog box this time, you will notice that it is slightly difference 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 (in Excel 2000) 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 Pivot Table to be grouped in a way we did not expect. So, what we can do is again drag the Age field to the inner most field (left) and our Pivot Table will be grouped by ages.
Lets now ungroup our Age field so our Pivot Table has no grouped items at all. At this stage the order of our row fields should be:
Age
Date of Birth
Names
Sex (as the Page field)
With all displaying.
Field Options
Lets now look at some options that we can use to change our Pivot Table fields.
Before we do so though, lets group the Age field (innermost) so that all our ages are grouped. This simply means right-clicking in the Age field, selecting Group and Outline then Group ensure Starting at: and Ending at: are checked, and in the By: box type the number 5 and click OK. This should mean our Pivot Table 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 PivotTable Field dialog box. You will notice that in here, the very first box 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, Custom and None. 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 Pivot Table. Lets apply a SubTotal so that we can get an average of our ages in each group. To do this, check the word Average in the SubTotals list box. By doing this Excel will check the Custom option under SubTotals.
Lets back out at this point so we can see the effect our SubTotals have on our Pivot Tables, so simply click OK. If you now look at your Pivot Table, 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 Pivot Table field dialog box. If you wish at this stage (and I would advise doing so) select one or more SubTotals and you will see how they are applied to the Pivot Table. Once you have finished doing this come back to the Pivot Table field dialog box and uncheck all SubTotals except the Average. Now click the Layout button and you will notice that your Pivot Table Layout dialog displayed. Not an awful lot this feature can do, but can be handy depending on your situation. 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, clicking OK in both the Pivot Table Field Layout Box and OK again in the Pivot Table Field box. View your changes to see the difference it has made to your Pivot Table. 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.
Once you have finished this, again come back so that our Pivot Table Field dialog box is displayed. The Pivot Table Field Layout options are the default with Show items in tabular form the only one being checked. From the Pivot Table field box click the button Advanced. This will display the Pivot Table Field Advanced Options From within this dialog box that we can nominate our AutoSort options. The default is Manual but you can see by simply selecting Ascending or Descending we have the choice of sorting our Pivot Table using the desired field by selecting it from the Using Field drop down box. This is very self explanatory as all we are doing is changing the sort order for the Pivot Table.
The one that we are interested in at this stage is the AutoShow option on the right hand side. Here we have two options, Manual or Automatic with Manual being the default. This option is very similar to the Show Top Ten option in Excel's AutoFilters. There may be occasions when you only want to display the top or bottom X items using the field chosen. Again, the best way to see this happening is to make a choice and back out to your Pivot Table to see how the changes affect it. One thing you should notice is that if you have the AutoShow option in automatic, the field in which the AutoShow option has been applied should appear with a bright blue font as its heading. This is Excel's way of letting you know that you are possibly not viewing the Pivot Table in its entirety.
What I would like you to do for yourself using the Pivot Table 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, if you feel comfortable to do so, but please only do this once you are sure that you understand all that has been discussed here.
In the next and final lesson on Pivot Tables we will be looking at some of the more advanced features and going through Pivot Charts.
Online Excel Pivot Tables Tutorial by Microsoft