Transcripts for the above video clip:

Pivot Tables 2007- Calculated Fields; Calculated Items

Excel gives you the ability to actually create your own formulas within the Pivot Table. There are two ways to do this - something called calculated fields and another called calculated Items.

Just to explain the difference between fields and items, because it is very important, you need to look at the underlying data. In the underlying data a field is defined as a column in this case, so for example the bill is a field, internet cost is a field, charged to MTN is a field. So if we are going to do a calculated field – what we are asking is to do some sort of calculation in two fields – for example  we are going to work out what the net profit is  and all we are going to do is take the bill, subtract the charge, subtract other costs to get an answer. This compared to calculated items – here let’s say we have got days of the week, Wednesday, Thursday, Friday etc. We may want to actually add up items within a field – so for example  I want all the Saturdays and  the Sundays to be added up to create a weekend. So the difference between the two is that in calculated fields there are distinct columns that we are doing something to. In calculated items we are actually working within the field and adding, subtracting or some sort of calculation to the items in the field.

Focusing on  the  Calculated Field first.  Here we have a Pivot Table – there are dates, we have got the total bill, the costs for the charge and some other costs. We would like to know what the net profit is. In order to do a calculated field, click anywhere within the Pivot Table. You have the Pivot Table ribbon,  you’ll see there is something called Formulas- when I click on it you’ll see there is something called Calculated Fields. And there is the Calculated Items. At the moment it is greyed out but again we are focusing on Calculated Fields. When I click Calculated Fields the following appears and it asks – give this field a name – I am going to call it Net Profit for example and then you just have to create the formula and the Row Labels or the fields are shown here so we know that the formula is going to be – you want it to take the bill and either click insert field or double click and when I say, in this case,  add  the charge (because it is a negative number) add the other costs. I am happy with that. I could say Add and do another one. But for now I’m going to say OK. You’ll see what it has done,  it has created a new column and if we look at our fields you’ll see there is another item called Net Profit. Now in the underlying data base we already had a Net Profit calculation. I have just shown you how to do it in a calculated field.  If you want you can do some quick checks – you’ll see those add up to .09, .09 and if we actually pull through the proper net profit field, we format  it, you’ll see it gives you the same number.  So that is the way to create a calculated field. The benefit of a calculated field is if I now go and I am going to do some sort of a grouping here – the months is OK, the calculated field will continue to do the calculation based on the information it has. I am just going to ungroup this.

Now let’s look at a calculated item.  I have created another Pivot table. We have taken the day of the week which is a field, we have put it into the Row labels- we can see that the one represents a Monday, Tuesday, Wednesday etc and we have got the sum of a bill. What we would like to do is  to add up the Saturday and a Sunday to tell us what we do on a weekend. I know that logically it should come to 267 by adding those up. In order to do a calculated item, what we need to do is click in the field where the item exists.  So if I click here in a Pivot Table and I try do a calculated item you’ll see it is greyed out. You need to click in the field in which the item exists. When I go here you’ll see now you can see something called Calculated Item. When I click on it a very similar dialogue box appears to Calculated Formulas. I will just give it a name – I will call it “Weekend Total”. You’ll see the field is already identified as day of the week – all you need to do is now give it a formula – we get it out of the items. So by clicking on the day of the week it has  identified  all the unique items. We are going to say please add up all the Saturdays and the Sundays. Before I press OK, just notice this – because this is one of the problems with calculated items. We have already said that that must be about  267 or so.  Notice that the grand total is 2237 – when I click OK we now have a new item in the field and it comes to the 267 which is correct, you’ll see it adds it up. But you have to be very careful because what you’ll notice is this new total has been included (double counted)  in  the  items which means it has affected the grand total- the grand total is now 2505. So this number is now added in, in which case it means there is some double counting. If you do need to use this, you probably need to get rid of these grand totals and the way you do it is right click on a Pivot Table, go to the Pivot Tables Options and work through some of these items to remove it.

To Edit or to remove the calculated field or item- again you click in the field and go to Calculated Items. To find one that has already been built, click on the drop down box, you’ll find it, click on it, you have got the formula if you need to change it. Or if you want to delete it, click delete, say OK and you will see you will be back to the original Pivot Table.