Home // Video Tutorials // 3 - 6 (Intermediate) // 4 // Create Pivot Table in Excel

Create Pivot Table in Excel

How to create a pivot table in Excel for the first time.

Create Pivot Table in Excel

The process to create a Pivot Table in Excel is fairly simple – Just highlight the data base that you want to turn into a Pivot Table, making sure that you have met all the requirements of a Pivot Table- no sub-totals, no columns without headings, no duplicate headings, no merged cells, correctly formatted dates etc.

To get the full Pivot Tables manual with pivot tables exercises follow this link

You go to insert and you see there is something called Pivot Table – I am going to choose Pivot Table for now. The Pivot Chart will be explained later. Click on Pivot Table you will see Excel produces this table – first thing it says it thinks it is going to use the cells in the data base and it is always useful just to check that it has guessed correctly. It looks like it is looking in the right area. You can use external data which will be covered later. You also get asked where do you want the Pivot Table to appear – either in a new worksheet or else in an existing worksheet. If you do ask for existing worksheet it will ask you where to place it. We are going to say new worksheet. When I click OK you’ll see it sets up the Pivot Table for your first time use.

Just to explain some of the aspects about a Pivot Table. The first thing to notice is when the Pivot Table is active you will find you have got a Pivot Table Field List and that In your ribbon you have a new item called Pivot Table Tools consisting of Options and Design. If you click in a cell outside the pivot table, these disappear and in order to get them back just click into the Pivot Table area. You’ll notice with the ribbon there are a whole bunch of options which we will cover at various stages. You can see there are a number of options here – if you click on the Design Tab you will see again a whole variety of options particularly around the Style of your Pivot Table. We will look at those later.

On the right hand side you’ll notice the Pivot Table Field List – if you look here you’ll see that these labels correspond to the column labels in your underlying data base, so here you will see date, time, duration. This is one of the reasons why the labels need to be descriptive because this is all you have to work with. At the bottom here you will see some empty blocks and you’ll see that this section is for the Report Filter, this is for where the Column labels will go, this is for where the Row labels will go and this is where the Values can go. You will also notice there is something here called Defer Layout Update. If you click this on and off what happens is as you build your Pivot Table you can decide whether the screen will update immediately or it will wait until you have finished all your changes before you then push update.

Let’s show how easy it is to set up a Pivot Table by producing a nice simple one. If I go down here, you will see there is an item called Bill and that’s the bill associated with these cell phone accounts. There are a number of ways I can get this information into my Pivot Table. You can click on the little box but then Excel guesses where it thinks you want to put it. My preferred way is that you click on the item, drag it while holding down the button. You can see you can choose where you want to put it- I am going to say please put it into Values. When I let go, you’ll see what it has done is created its first little report and this is saying that the total of that column is that amount there. Now that itself is not particularly useful, it is just the sum of a column which we could do fairly easily. Now let’s introduce another item- for example, I am going to take this Month column, click it and drag it and now I want to do is to see the months down the side and to have these sub-totals. When I let go, notice that in a very easy way you have now created a report that gives you a breakdown of the total, split by the various months. If you don’t particularly like that or where it is placed I can grab that label, maybe put in under the columns, when I let go you can see it now goes across the top. And again if I want maybe to go back, I can click it, drag it and that looks quite nice.

Another thing to realize about Pivot Tables, is that they are not directly linked to your formula. For example if I go and you’ll see this is January and it is 184 and I go to the underlying data base and I take one of the numbers – and let’s put a really big number into the bill- let’s make it a million. When I come back to the Pivot Table you’ll notice nothing has changed. This is because Pivot Tables need to be told if there has been a change in the underlying data base. Some simple ways to do this – you can either go to the ribbon and you’ll see the button called “refresh” you can choose to refresh or refresh all or else you can right click on the Pivot Table and over here you have an item called Refresh- when I click Refresh you’ll notice that the big number – the change I have made comes through. Let’s just undo that and again when I want to change it I go and I click refresh and the old number is back.

So at a very simple level we now have a very powerful report which we can manipulate to generate various type of reports. In the rest of the course, in the video clips we are going to understand what can be done with each of these sections.