Transcripts for the above video clip:

PIVOT TABLES

 PIVOT TABLES allow you to analyze and report on vast quantities of information quickly and easily.

In this segment you will learn how to activate the feature, set up your database to be used in a PIVOT TABLE report and how to manipulate the PIVOT TABLE to meet your needs.

In this example we have a database with a number of columns, we’ve got Dates, Supplier number a Currency, Document Numbers, the Amount, Balances, the Supplier Name etc.

What we’d like to do is to be able to summarize and report on this information quickly and easily. PIVOT TABLES are the ideal tool to handle this type of situation. But before we get into the PIVOT TABLE, it is important to know what needs to be done with the database to make sure that it is acceptable to the PIVOT TABLE reports.

• First and foremost all columns must have a heading and they must be unique, so you’ll see here this column that is blank, has been labeled blank
• And there are no headings that are duplicate; this just makes it easier for Excel to understand what information is being inserted into the PIVOT TABLE.
• Furthermore there should be no subtotals, so you’ll see right at the bottom here we have a total, we must be very careful to exclude it from the PIVOT TABLE and all subtotals have been removed.

To activate the PIVOT TABLE,

• click anywhere in the list of data
• and go to Data,
• PIVOT TABLE and Pivot Chart Report
• and click on it
• and what you’ll see is you’ll be asked a couple of questions. so firstly you’d generally be working in a list in Microsoft Excel,
• and you want to work on PIVOT TABLES  for now,
• so you’d say next,
• the next aspect is it highlights what it thinks the list is, and I highly recommend you have a good look at it to make sure that you agree
• so in this case you can see the dotted lines have included that,
• they go to the end
• and when we come down here, you’ll see that they’ve included subtotals which we do not want in there,
• so we just need to put it to row 529, so we’ll go to 529
• and we say next,
• at this stage you now have the option, of where you want the report to go, so it can be in new worksheet
• or you can click on existing worksheet and put it in one of the cells,
• but before you do any of this I recommend you click on the Layout button to set out the layout of your first Pivot Report so if we click on that now

This may look confusing but as you work more and more with PIVOT TABLES you’ll find this very easy to understand. What excel is asking for is what the report must look like and in any report you can

-          have rows of information where you categories information,

-          you can have columns

-          and you can even have pages,

-          and then you have the data itself

-          And what I recommend is the first one you do just make it as simple as possible, so in this case we’ve

• got this item called Supplier number,
• and what I want is to list in the row by Supplier number
• and we’ve got an amount so all I want to do is

• put in the data section
• and you’ll see it comes up with sum of amount and that for now is good enough
• and we can click ok,
• We are now in a position to generate our first PIVOT TABLE Report.
• we’ve maintained that we need it in a new worksheet
• and if we click finish
• a report like this will be generated.

Just to explain this report,

• what it tells you up here is that the data in the Total field is the Sum of the amount column,
• down the column its using the Supplier number column,
• and as you can see there Supplier numbers and this will be the sum of all amounts referring to that Supplier number,
• Whenever you click in this area
• this PIVOT TABLE field list will appear which enables you to play around with the report,
• when you see these little arrows, if you click on them
• it’ll show you all the components of that column and it gives you capability to lets say you decide don’t actually want to see supplier number 1201
• and you click Ok
• it will redo the report without that number
• and you can go back and switch them all on.

You can now move on to the real power of PIVOT TABLE Reports. As you can see our report is set up with the Supplier number and the Sum of the amount, but let’s say looking at these field lists that we actually want to include the month that these amounts occur, what we can do is

• you grab the item, click on it
• drag it across,
• now that indicates that you’ll put it into the data field, you don’t want to do that,
• perhaps you want to put it here in the column field,
• and when you let go
• what it does is quickly splits the column
• so that now you’ve got a Supplier number
• you’ve then got the Months
• and then the Totals making up each month,
• if you’re unhappy with that and you’d rather have the Month being the primary form of categorization on the row,
• you just grab it
• and you move it to the other side
• and now what you have is all the Months and all the Supplier numbers that make up that month.
• and perhaps if you want to maybe rather have the End of Months coming along the columns,
• if you drag it there,
• now what you’ve got is the Supplier number
• split up by the month ends
• with the amount in it all coming to a grand total.

The number of reports you can generate is almost limitless, so for example we’ve got our Supplier number, we now have the Months across the columns, what say we

• take this description
• and pull it across by the Supplier number
• and we let go
• and now suddenly you’ve got a more detailed report

Another useful feature of PIVOT TABLES is the ability to drill down into the summarized information.

• So if we remove this item
• You’ll see that here in March there’s and amount of 2978 for Supplier number 1201,
• if you double click on that cell
• what it generates is a new sheet with the component items that make up that amount so you can actually check back to see what has made up that total.

Getting back to the PIVOT TABLE Report you also have a number of other options. So or example

• you can double click on these headings
• and a little box will pop up
• and as you can see at the moment it’s saying you must summarize by sum and its called Sum of amount
• but lets say you don’t want it to be summed you actually want to count how may items are for each supplier per months,
• then you click on Count,
• and you say Ok
• and what’ll be listed is the actual count of how many items it found in each area,
• and again if you double click
• it will list those items,

Although this might look complex for now, you’ll find that as you work with them you’ll get to understand what is happening here a lot better. So for example

• Let’s remove this End of Month
• and go back to the Sum situation
• and that is a report that is useful anyway,
• you can now feel free to go maybe include the Supplier name
• and this provides a report and its subtotals per Supplier number.

If you right click while you’re in a PIVOT TABLE,

• a number of options will pop up
• and you’ll see that here you have an option to go back to the Wizard that helped you set up the PIVOT TABLE
• and you can also Refresh the data
• so if changes have been made to the underlying data you need to refresh it so that the PIVOT TABLE updates,
• if you go back to the Wizard
• you’ll see that you’re Back at this stage
• if you click the back button
• you can go lets say you’ve added more data you just need to make sure that it still covers the list
• and perhaps if you prefer to work in the Layout view you can click there
• and start to work on the layout of your report,

And again although it looks complex all you have to think about is what Dates are you trying to summarize, are you summing, counting, averaging. And how you going to categories it, and once you’ve understood that you’ll be a long way down understanding how PIVOT TABLES report.