Home // Video Tutorials // 0 – 3 (Beginner) // 3 // Pivot Table Row Labels

Pivot Table Row Labels

How to work with Pivot Table row labels in Excel 2007 and up.

Pivot Table Row Labels

Now let’s look at the Pivot Table Row Labels.

With the rows you are not limited to only one label, you can have more than one, so for example I have got something here called Day of  Month – if I click it and drag it down you will see the  little blue line there – I am going to put it after the month, when I let go, you’ll see what it does,  I have now got two row labels- one being the month and the second part of that is the day of the month so you’ll see this is January 25th, 26th  etc  and February 1st , 2nd and 3rd. You can move this around, so, for example, if I take the day of the month and I rather put it above the month and let go, now what this is saying is the first days of the month for February- those  were  the answers, for March those were the answers.  For the second day of the month, February March etc.  Let’s just pull it back here. Another thing to notice about the row labels is that above the row there is a little drop down box and if I click on it,  I get a number of options.  So you can see there are some sorting options ( I will deal with these a little bit later), some filter options – so you’ll see if there were value filters we could do that. And then you’ll see at the moment we have selected ALL and it is showing  January, February and March. But for example we can easily go and  say I don’t want January and I don’t want March and when I say OK you’ll see those disappear-  so we can be quite specific on what we want to show. I will just switch those back on and say OK. You’ll see each row label will have a  drop down and depending what’s in them certain other options will become available.

As with all things Excel if you right click on one of these row labels you will see you have a number of options. If I click on “Field Settings” you’ll see we get this – you can see at the moment Excel has automatically chosen a label for the row and called it Month but you can change it. Another useful thing to see is you have got something here called Sub-Total and you can  see it is  set it at Automatic. What that means is that it has automatically decided it will take these numbers and in this case sum them. You can change or decide what you want to see here. So if I click “None” and I say OK watch what happens – you’ll see  the sub-total  disappears. If you right click here and go to  Field Settings – there is also something called “Custom” so if I click on it I can now say please sum the items or count them. If I click on Count and say OK you’ll see now it is not summing these items but counting how many items exist here. If I went to this row label and I right clicked and I said Field Settings you will have a different set of Field settings because  each row label has its own field settings  so I’m going to go back here and right click.  If I am on Count and I switch Sum on, they both stay highlighted and you put Average on as well as well as Max and Min.   When I say ok you get all these details relating to those items – the sum of the items, the count of them, the average, the maximum number and the minimum number.

Go back to Automatic option. Right click on the Row Labels again  – go to Field Settings. Look at Layout and Print. At the moment it is ticked as “show item labels in tabular form” – if I said please show the items labels in  “outline form”  and say OK you will see how the Pivot Table looks changes. Go back to Layout and Print and say  “please display labels from the next field in the same column” – something called Compact Form – OK – you’ll see basically the columns now are a lot closer together.  Right click and Field settings – “please display the sub-totals at the top of each group” – at the moment the sub-totals are at the bottom – OK – you’ll see they switch to the top. Back to Field Settings, Layout and Print –  Let’s go back to show items in tabular form. You’ll see you can also set it up so it inserts a blank line after  each item label. When I switch it on and say OK you’ll see it creates a blank line between each individual item.  Right click Field Settings – you can also specify to show items with no data. By default, in the Pivot Table, if there is no data for a particular item it will not show that item. You can actually insist that it does show the item. You can ask it to insert a Page break after each item – so if you did this and printed it out, this section would be printed, there would be a page break here and this section would be printed on a new page.

Let’s see what else you can do with the Row Labels. If I right click you’ll see one of the options is SORT. You will see there are a couple of options – sort A-Z, Z-A or more sort options. As mentioned earlier, pivot tables are their own animal and they basically live outside of the spreadsheet – they sit on top of the spreadsheet. What that means is that if I come for eg to February – if I go and change the cursor to that little four directional arrow – if I click, hold and I move it up – notice that little dashed line – what this means is that I am going to manually sort this so I want February to appear above January – when I take it to the top and I let go every item that belongs to February moves up with it. So that is manual sorting.  And I can do the same thing, click, change the cursor move it to the top. Let’s say I decide, for whatever reason,  the  Day 5 should not exist  before or after Day 1, click, move it to the top  and now you’ll see my sort order is  5 1 2 3 4  and notice that for all the months that sort order has been applied. That is manual sorting.

However when I right click and I say sort -and I say sort A-Z and click on it , you’ll see it sorts it in ascending order- Jan, Feb March. If I right click again and say sort Z-A, you’ll see it goes backwards.  Probably more interesting is if I go to Sort and then choose More Sort Options-  what you’ll see is we get this : we can switch back to go manual (drag and drop), we can go ascending or descending but very important you will see at the moment it is showing ascending by month- what that means that it will sort it in ascending  but will use the month’s row label to decide what the order is. We don’t have to use that. If I click here I can say “please  use  the Sum of Bill rather”. Go from the lowest to the highest  based on the Sum of Bill.  If I look here and say OK – you’ll see January is in the front because it has the lowest sum of bill (184) next is  March (915) and last is February(1138).  Perhaps you want to do it at this level, I come here, I right click, sort –  because it has recognized it is a number  it now says sort from  smallest to largest or  largest to smallest – I  can say  more sort options – in this case let’s go descending – not by day of month now – I don’t want to see 31, 30, 28 – I want to base it on the count.  So show me the highest count here. Click OK.  It has now  sorted these items, based on the count.

You may also notice that in the row labels you’ll see next to these items there is a little minus sign. If in this case I do not want to see the detail , I can click and it is all closed and I have got my  totals. If I now want to see the  detail, click and it is all shown. If you right click on one of these you’ll see something  called  Expand and Collapse – I can either expand or collapse individually  or let us collapse the entire field.  You’ll see all of those close and you’ll now just see January and March and February. If I want to expand say “Expand entire field”  and you’ll see that is brought through