 |
|
|
Online Excel Pivot Tables Tutorial by Microsoft
In the last lesson on Pivot Tables we looked at how to set up a simple pivot table and how to use the grouping and field options of Pivot Tables. In this lesson we will discuss some of the more advanced features of Pivot Tables.
Note: If you are using a version higher than Office 2000 some of the commands that we use here that we are selecting with our right mouse button, you may not see. If you cannot see them, you will have to show your Pivot Table toolbar where you will see these commands.
Setting up the Pivot Table
In the workbook download that goes with this lesson, you will notice on the Pivot worksheet we have added two more columns to our original table. The columns now reading from left to right are; Names, Age, Sex, Date of Birth, Hourly Pay Rate, Hours Per Week. With the two new columns being Hourly Pay Rate and Hours per Week residing in columns E and F. Lets use this data to now create a Pivot Table as follows:
Create Pivot Table based on this data so that the fields Years, Date of Birth, Names and Age are all residing in the Row field of the Pivot Table. Place the field heading Sex into the Page Field area and then in our Mandatory Data Field, place the fields Hourly Pay Rates and Hours per Week. Once you have set your Pivot Table up in this manner, click through the rest of the Wizard and place the Pivot Table on a new worksheet.
Click anywhere within the Date field of the Pivot Table, then right click and select Group and Outline>Group.... Ensure that both the Starting at: and Ending at: boxes are checked under Auto. Then from the By box select Months and Years, then click OK.
Now drag the Date of Birth field which should now only store the month names up to the Page field position directly below Sex. Your Row field headings for your Pivot table should now be Years, Names, Age and the two Page fields should be Sex and Date of Birth. Ensure both Page fields are showing (All) Data. You should also have some fields stemming from your Data area, such as Michael sum of Hourly Pay Rate. Right click on this or any other similar field and select Hide for Excel 2000 and Delete for Excel 97. We should now have a very basic Pivot Table layout that is really only giving us the exact same information as we were seeing on the original data source. The only difference being our employees are now grouped by the years in which they were born, and we can easily show individual months by selecting the appropriate month from the page field and/or sex from the sex Page field.
What we are going to do now is create our own Calculated Field that will tell us how much each employee will be paid based on their hourly rate and their hours worked per week. To do this, come over to the Data field and in any one, right click, go to Formulas>Calculated Field... This will display in front of us the Insert Calculated Field dialog box. Before we go through and use this, let me give you a brief explanation of what each box is used for.
Calculated Fields
Name:
This is where you type a name for the Calculated field or item you want to create, or alternatively you can select a named field or item from the drop down list if you were editing an existing name. At this stage, we have no names in here, so this drop down list will be blank.
Formula:
This is where you can type the formula for the Calculated field or Calculated item. The formula syntax that we use is specific to Pivot Table and Pivot Chart reports. More on this later.
Calculated Fields Formula Operations
It is important to know that when we use a calculated field, the calculated fields operate on the SUM of the underlying data for any field in the formula. This in some cases can be very confusing and if not aware, can also lead to possible erroneous results derived from your calculated fields. We will use an example in this lesson later that will show you exactly what I mean.
Field:
From this box we can select an existing field and use it in our formula. To do this we simply click the name of the field in the Fields box and then click Insert Field.
Going back now the brief explanation of a formula we can use in a Calculated field, there is one very important rule that we have no choice but to adhere to. This is that any formula we use cannot be a built-in function of Excel that requires a range as its argument(s). Unfortunately, this does prohibit greatly the type of formulas that we can use for a Calculated Field. Also, we cannot use defined names, or array functions.
Formulas for Calculated fields operate on the sum of the underlying data for any fields in the formula. Formulas for Calculated items however, operate on the individual records.
Rules when using Pivot Table Names in Formulas
All source fields are allowed, that is formulas for calculated fields can refer to any field in the source data for the Pivot Table. This includes fields that are part of the source that have not been dragged to any of the Pivot area.
Number of Fields and Items Allowed:
Calculated fields can refer to one or more field names. Calculated item formulas can each include only items from the field in which you create the calculated item.
The Order of Fields within Names:
In a name that includes more than one field, the fields can be in any order. For example, we could use '1977 Brenda' or 'Brenda 1977'.
Names that include Spaces, Numbers of Symbols:
If the name of a Field item we wish to use in a Pivot Table contains spaces, numbers or symbols, we must place single quotation marks around the name.
Totals:
Formulas cannot refer to Totals or Grand Totals in the Pivot Table.
Field Names in Item References:
You can include the field name in a reference to an item. However, the item name must be in square brackets to avoid the #NAME error when two items from two different fields have the same name.
Referring to Items by Position
Alternatively, you can also refer to an item by its position in the report as the report is currently sorted and displayed. Hidden items are not counted in this index.
See "Rules for Using Pivot Table or Pivot Chart Names in Formulas" in the Excel Help for further detail.
Calculated Fields
Lets now move into our Pivot Table and create our own calculated field to tell us the pay each person should be receiving.
The first thing we need to do is change the names of "Sum Of..." to something a bit more meaningful. You would probably think the best way to go about this would be to change the name Sum of Hourly Pay Rate simply to Hourly Pay Rate. However, Excel would not allow us to do this as it would tell us that the Pivot Table field name already exists. So, we need to change this to a name that does not as yet exist. Lets call it Pay Rate per Hour. To do this, simply select any cell that has Sum of Hourly Pay Rate in it, then in our formula bar delete what is there and type Pay Rate per Hour and push Enter. This should automatically change all of them throughout the Pivot Table.
Next click in any cell that says Sum of Hours per Week and change this to read Hours Worked.
Now follow these steps to obtain a Calculated field that will tell us the amount of pay each person should be receiving based on their hourly pay and hours worked. However, there will be a serious flaw in the Total result for our Calculated field, see if you can spot this yourself. The clue is under the heading Calculated Fields Formula Operations. Without telling you exactly what it is, we will do some more on calculated fields, and then last of all show you how, if you are not aware, you could give yourself erroneous results.
Select any cell under the data field
Right click and select Formulas>Calculated Field...
By default in the Name box should be Field1. Lets change this to read Total Pay.
Select Hourly Pay Rate from the Fields box, then click Insert Field.
Type the multiplication sign (*), come back down into the fields box and select Hours per Week
Again, click Insert Field
Click OK
You should now see that in the Data field there is now a new field called Sum of Total Pay with the corresponding figure being the pay they would received based on the hours worked and their pay rate.
Again, the word Sum of Total Pay is not relevant in this case, so lets change this to read Weekly Pay.
Let's now assume that the Government has bought in new Legislation that states that all workers over the age of 30 must receive an additional payment of 5% on top of their weekly wages. (Yes, I know it is wishful thinking!!!!). We now need to change our Pivot Table to take this into account.
To do this, again in the Data field area right click and select Formulas>Calculated Field... From the Name box, click the drop arrow and select the name Total pay.
Once you have selected this, the formula that we wrote previously should now be showing in the Formula box. Follow these instructions:
Left click in the Formula box, immediately after the last single apostrophe type +if(
Now select the heading Age from the Fields box and click Insert Field
Ensure your mouse insertion point is flashing immediately after the word Age in the formula box and type >30,(
Come down again and select Hourly Pay Rate and click Insert Field
Type *
Select Hours per Week from the Fields box and select Insert Field
Now close off the opening parenthesis with a closing one and type *0.05,0)
So, all this should look like this:
='Hourly Pay Rate'*'Hours Per Week'+IF(Age >30, ('Hourly Pay Rate'*'Hours Per Week' )*0.05,0)
in your Formula box. If it does, simply click OK and you should notice now that anybody greater than the age of 30 should have 5% of their total wage added on.
In summary now, using the two methods that we have shown above, you can see how we can write simple formulae for our Pivot Table and also how we can go in and edit an existing formula.
Imagine now that we have this information, the pay rates, hours worked and total weekly pay for each employee. We now wish to find out what percentage of the total weekly pay is for each employee, but before we do, we need to first create a new Calculated field that we can use to show the weekly pay as a percentage of the total as opposed to a dollar figure.
To insert the new Calculated field, follow these steps:
In any cell under the Data area, eg; Hours worked
Right click and go to Formulas>Calculated Field...
Give it the name Percent of Total Wages
Clear out what is currently being shown in the formula box,
Ensure your mouse insertion point is still flashing in there
Select Total Pay and click Insert Field
Click OK.
The first thing we should do now is change the wording of our new field from Sum of .... to simply Percent of Total Wage. Again the easiest way to do this is to select any one of these headings and make the adjustment in the formula bar. As you will see at the moment all we have is an exact duplication of the weekly pay for each employee. We now need to change this field setting so it will actually show us the percentage for each employee compared to the total wages. To do this, follow these steps:
Select any one of the Percent of Total Wage field headings
Right click and select Field Settings....
Click the Options button
From the Show Data as: box, select % of Total
Click OK
This is where you should now be able to spot the serious flaw in our calculated results for our percentages. Just looking at them, and knowing how many employees we have would probably lead you to examine it further and spot the mistake. However, as you can imagine, if the mistake is not so obvious, it would be quite likely that you would expect the result as being correct and possibly go ahead and use these results for whatever purposes.
The Not So Obvious Flaw
The percentages that we are looking at are actually being based (as we have told it to do) on our Total for our Weekly Pay (which is where the error resides). If you scroll down to the very bottom of our Pivot Table, you will see that our Total weekly Pay is giving us a result of over $200,000. When in reality, the result should be only just over $10,000. Yet, looking back through our Pivot Table, our weekly pay for each individual person has been calculated out correctly. The problem lies in the fact that to give the Total for our Calculated field, Excel has taken the Total Pay Rate per Hour and multiplied it by the Total Hours Worked, which has obviously inflated the figure dramatically. Now, this is fine, provided you are aware of it, because we could simply hide the Total Weekly Pay row at the bottom of the Pivot Table and simply not use it for any results. Then we would simply just use the figures being generated for each individual employee.
The bottom line here is that to do such calculations as we have just tried it is often better to do these calculations in the underlying data and have that result extracted into our Pivot Table. So lets do that now.
Go back to our Calculated field. Any one will do. Select both the Total Pay and Percent of Total Wage and delete them. Now go back to our underlying data and add another column at the end of our table and call this one Weekly Pay. In the first cell directly under this heading (G2), put in the formula =E2*F2+IF(B2>30,(e2*f2)*0.05,0) and double click the fill handle to have it copied all the way down your table.
As you can see, this now is giving us the weekly pay, exactly as was being given to us in the Pivot Table, however when we now include this new column in our Pivot Table, we can rest assured that the Total for this column will be the correct figure. It does, however, pose a new problem in that as we have now added a new column we would need to go back into the Wizard of the Pivot Table and redefine our Pivot Data source. If, however, we had used a dynamic range which expanded both down rows and across columns, we wouldn't need to worry about doing this.
Lets now create a dynamic range based off this data.
Go to Insert>Name>Define
Under Names in Workbook: type MyPTData
In the Refers to: box type =OFFSET($A$1,0,0,COUNTA($A$1:$A$500),COUNTA($1:$1))
Click Add then OK
This has now given us a dynamic range for our Pivot Table that will automatically incorporate any new data added down to row 500 and across to column IV.
Now lets go back to our Pivot Table sheet, right click anywhere within our Pivot Table, select Wizard and use the back button to get back to Step 2 and change what was $A$1:$F$20 to MyPTData. Then click Next (and if using Excel 2000 or higher, click Layout) and drag our new column heading Weekly Pay into the Data area of our Pivot Table and it should read Sum of Weekly Pay. Again, no need to have the name Sum of Weekly Pay, so lets change it in the Layout Step of the Wizard. To do this, double click on the Field button Sum of Weekly Pay and in the Name box type Weekly Pay Total and click OK. Then click through the rest of the Wizard until you get to the final step and click Finish.
Now you will see looking at our Weekly Pay Total for each individual it is exactly the same as it was previously in the Pivot Table, however, the all important difference is our Total Weekly Pay Total at the bottom of our Pivot Table is now giving us the correct figure of just of $10,000 and not the erroneous inflated figure of over $200,000. The bottom line here is really that if you are unsure when you have created yourself a calculated field, check the results yourself manually and especially the totals.
Now we can go back and safely add a second calculated field that will show us the percent of Total Wages. To do this, follow these steps:
Right click in any item under the Data field
Select Formlas>Calculated Field
Give it the name Percent of Total Wage
Delete what is currently showing in the formula box and with your mouse insertion point still flashing in here, click on Weekly Pay, then Insert Field
Click OK
Again at the moment this is giving us an exact duplication of the total wage for each person. Lets go in to the Sum of Percent of Total Wage and in the formula bar edit it to read Percent of Total Wages. Note we must add the S otherwise Excel will tell us that the field name already exists.
Right click and Select Field Settings>Options
From the Show Data as: box select % of Total
Click OK
Now we should be getting correct percentage figures.
Basically, in summary hopefully this lesson has shown you how you can create Calculated field, but more importantly be fully aware of the pitfalls associated with them. If and when you create a Pivot Table, you find that this problem reoccurs, you may need to reassess the situation and add the figures to the Data table itself as we have done.
Online Excel Pivot Tables Tutorial by Microsoft