Home // Video Tutorials // 3 - 6 (Intermediate) // 5 // Pivot Value Settings and Options

Pivot Value Settings and Options

How to use the Pivot Value options to not only sum, count etc but also to create % of and running and cumulative totals.

Pivot Value Settings and Options

Pivot Tables for Excel 2007We are now going to look in a little more detail at the Values Portion– where the data is generally kept. Just a couple of useful bits of information upfront.

As you can see we have pulled through the Bill Column and you’ll see it has told us “Sum of Bill”. If you want you can pull the Bill Column through many times – you’ll see I’ve grabbed it once, grab it twice and as you can see the exact same information comes through and we’ll show you later why this is useful. Also note that the Column Labels now has something called Values- if you watch what happens here on the screen if I grab that and drag it and put it underneath the month and let go – notice that the Value headers now follow after the month. If I don’t like that , grab it and move it above the month, let go – now the Value header sits here with the month underlying it. I am just going to put it back there.

Now let’s see what else we can do with the Values Portion.

If you click on one of the numbers, and you right click, you’ll see you get a number of options but at the bottom here there is something called Value Field Settings. If I click on it, you get the following options – just working through it- you’ll see at the top is Custom Name. If you don’t like the name Excel has given it you can type you own name in. You’ll also notice that at the bottom there is something called Number Format. Let’s just cancel out of here. If you look at this data you may be tempted to format the data here in the normal way perhaps right click Format cells and then go and do some formatting. You need to realize that Pivot Tables stand on their own so if you try to format the cells straight into the Spreadsheet, next time you refresh that formatting may be lost. As a result the only way to format it properly is to go to Value Field Settings and Click on Number Format and you’ll see the exact same dialogue box appears but now when you choose the settings you can see although you highlighted one cell and click on OK you’ll see that entire Value Column gets formatted because the Pivot Table knows that you want all those items to be formatted exactly the same.

Let’s just click on this one here and go back into our Value Field Settings and let’s look now at the Summarised Value Field by. You’ll see when we drag the Value Field into the Values item it immediately assumed that you want to sum the information in that column. However there are a number of other options eg you could count the number of items. If I click Count and I say OK you’ll see it tells me how many items it found and you’ll see there were 58 items which added up to the R184. If I right click again, Value Field Settings – perhaps I want to look at the Average. I click Average and say OK you can see that what it is telling me is on average for the 58 items previously identified at 184, the average was 3.17. Let’s see what the other options are – there is a Max – it brings through the maximum number it finds in those areas, there is a minimum, shows the minimum number. And you will see there are other options – product (counts how many numbers it found), there is a standard deviation and a few other statistical terms.

Just going back here – let’s put it back to sum. Now let’s look at the “Show Values As” Tab .

When I click on that you’ll see it says Show Values As – currently it is at Normal – so what it is doing is just showing the normal values. If I click on this a whole bunch of options appear – you’ll see there is a difference from, percentage of, running total in, and a number of other options.

So let’s just look at these- let’s go to the first one – difference from : when you activate this option you’ll see it asks you what the base field is and what the base item is. Now what does that mean? When we look at this table you’ll see we have got January, February, March. I want to see the difference from, in this case, the previous month so over here I want to see what the difference is between those two and over here the difference between those two. In order to activate that I have to tell Excel what the base field is – I say please go and use the month. And then for base item I can either say look at the previous item (so February will look at January), or next (so January will look at February) or I can specify a particular month so if I say January, not only will this one show the difference from January, but March will also show the difference from January. So I am going to say go to Previous and when I say OK you’ll see what you have got now is a difference from the previous month so this number is 953 more than January and this number is 222 less.

Let’s continue to explore these options. So I right click Value Field Settings; Show Value As; currently we are on difference from. Let’s go to the next one which is percentage of: when I click on it , again you have the option of base field – the base field needs to be in your Pivot Table otherwise it will give you an error message, so we leave it on Month and in this case let’s rather use our base item as February so it seems February is the highest number so we are now going to compare everything to February. When I say OK you’ll see it gives me a percentage of February’s total – so that number is 16% of February, this (February) is 100% of February and that is 80% of February. Going back here – let’s go to Value Field Settings; Show Value As: you’ll see the next item is Percentage difference from: which is pretty much similar to the difference from and the percentage of it just puts the difference from in percentage terms so we’ll ignore that one for now. There is another item here called Running Total In: when I click on it, all it wants to know is where must it run the total from- which means that we want to have a running total in this column so January will be 184, February will add these two together and March will add all three together. So it can be a year to date type situation. When I say OK you’ll see what it has done is January is the starting point, February adds the two numbers together, March adds all three numbers together and you don’t have a grand total because this is a running total and there is the total for those three items.

Let’s look at some of the other options. So we are currently in Running Total In: you’ll see over here we have got a percentage of row, percentage of column or percentage of total. Just moving this down – at the moment you’ll see percentage of row would give me what percent this makes of the row but we don’t have a sub-total here. We have got percentage of column so we want to know what percent does that number make of this entire column – basically of the total. So if I go to percentage of column and I say OK you’ll see it will always add up to 100%. The first item is 8% of the total, the second one is 50% etc. That is similar to this item here, percentage of total – if you have multiple rows and columns. What the percentage will be – it will be a percentage of the grand total of all rows and columns. And the last item is Index which helps you to calculate the relative value of a cell. It can be useful – it is best to look at the Help file to understand how it does that calculation.

The last thing to understand about the Value section – let’s say we look at this number here – we see that it is 915.52. Now it is very difficult to understand what that number is made up of – if we had to come here we would have to look through the entire data base to figure it out. What you can do as well is if I simply double click any particular cell – so I am going to double click this one. What you will see is that it has opened another sheet and it has extracted all the items from the data base that make up that number- so if we had to come to the bill and we add it up you’ll see it comes to the 915 – we can now look at this and understand what makes up that particular component. So again, just to show you, choose a cell you want to work with, double click it and the information will be extracted for you.