Transcripts for the above video clip:

Pivot Tables 2007- Pivot Ribbon Options

When you create a Pivot Table and you click on it, not only do you get this Tool Bar here but you’ll notice that in the ribbon there is a new   ribbon  item  called Pivot Table Tools. I am just going to work through this just to explain what each of them does.

Starting at the far left you’ll see that the Pivot Table has got a name and if I click on Options there are a couple of options. The first one is called Options, if I click on it you’ll see it gives you a number of possible things you can do with the Pivot Table. So you will see the layout you can change to merge the centre cells with the  label and how you can do with the indent, how you want the fields to report, how many columns you want per filter, how must it show the  error values and empty cells, must it autofit information. So that is the layout and format. Totals and Filters- you can decide whether you want to see these grand totals or not. Display – do you want to see the various buttons. Most of these items we can access anyway through the ribbon and we’ll show you how to do it. There are some printing options, there are some  basic data options. The data options are quite important, you’ll see Pivot Table data. It says Save Source Data with File. By default this is ticked which means that when you have a Pivot Table, behind it is all the data. That can cause your spreadsheet to be quite big, so what you may want to do is switch that off. Switching if off, however, means that you cannot play with the Pivot Table as much as you used to be able to. Similar with Enable Show Details, if you switch that off, the data behind the Pivot Table is not kept which means the file size is smaller, but you don’t have that flexibility. You can also ask the Pivot Table to refresh itself when the file is opened.

Going back to the ribbon, to Options, you’ll see there is a Show Report Filter page which we cover elsewhere, and Generate Get Pivot Data  which we cover elsewhere. You’ll see as you click on various parts of the Pivot Table, the active field will change, so at the moment it is saying we are in the Sum of Bill. When I click there we are in the  Day of the Week. If I want to make some changes to it, I can either click on Field Settings  and your options pop up or else you could have gone anywhere and right clicked and said Field Settings. You can also decide whether to expand or collapse a particular section. From the ribbon you could also group or ungroup a selection or field, you can do some sorting. You can refresh from here and its worthwhile looking at the drop down, because not only can you refresh this one Pivot Table but if you click “Refresh All”, all pivot tables within this spreadsheet will be updated.

The next one is the Change Data Source. If you click on it, the previously chosen data source will appear and if you want you can change it, add to it, remove from it, etc. So that’s the Change Data Source. You can see if you click on Clear and you  can decide to Clear All or Clear the Filters,  click Select you can  select the entire Pivot Table you’ll see it has highlighted or maybe only the labels and values. Values just  inside this, or labels  just outside this,  etc.

If you want to move the Pivot Table, you can click here. At the moment it is sitting in this sheet at A4, you can move it to another sheet or somewhere else and the entire Pivot Table will move.

Elsewhere we talk about the Pivot Chart and how to handle the formulas. What you can see here is that you can actually choose what you are going to see in the Pivot Table. With the Field List if I can switch it off, you’ll see the Field List over here disappears. We can switch it back on. The buttons, you can see here by January, February there is a little button which allows us to open and close items. If I click this button, those little options disappear. And at the Field Headers, the description of what these are disappears.

Let’s now look at the Design Tab under the Pivot Table Tools Ribbon. You’ll see here at the far left we have got something called the Sub-Total. If I click on it- Sub-Totals, at the moment we have none. If I click on it I can say “Show all Sub-Totals” at the bottom and you’ll see the sub-totals appear. So that gives you a couple of options there. For the grand totals, again we can specify. At the moment we seem to have a grand total for the Columns and the rows. I can switch them all off and you’ll see they have disappeared- or all on, or on for the rows only and for the columns only.

You can also play with the Report layout. So, for example, I can show it in something called Compact view- when I click – notice it is no longer sitting in the cells individually – it is a little more compacted. I can show it in Outline Form or in Tabular Form, which is what we have been working with. I can also tell it how to treat blank rows, so if I click on Insert black line after each item- in this case you will see there is a blank row after each item and I can remove black lines after each item. So you’ll see it is a little bit more compact.

You also have the option here to change the look and feel of your Pivot Table to make it look a little bit more professional.  Here you will see under Pivot Table Style if I expand it, you’ll see the whole selection of options of different colours and combinations. So let’s just choose one and we’ll  be a little bit extreme,  we’ll choose  this one and when I click on it, notice that my Pivot Table assumes  all those colour options.  Fairly easily I can change backwards and forwards till I find one I like. So let’s try maybe that one. This looks quite nice. You can also play with how you are going to treat some of these items, you’ll see if I click the row headers, notice that they change from bold to unbold. Notice that some of these options change as well. You can do the same thing with column- you’ll see that the column headers lose some of their emphasis. You can have banded rows, if I click, you’ll see the rows are a  little bit more banded and banded columns – you’ll see the lines are  showing. You can work through your spreadsheet and create a number of different designs- and use the  one that  is most suitable for you.

Another feature which is not covered here but it is still worthwhile knowing about is that you can create your own Pivot Table style. If I click on this, you can now work through every aspect of a Pivot Table and decide how you want it to look. So for example, if you click on Whole Table and I say Format, you can then specify what type of font, what the borders must look like, what the full must look like. When we have finished with that, we can move to how the page field  labels  will be treated- Field Column, First Column etc. So all the possible  components of a Pivot Table can be addressed and customized – you can change the colours, the borders, the fonts, whatever you want to. But that is not  really covered in this particular course.