How To Use A Pivot Table In Excel
VideoJug quickly teaches you how to utilize pivot tables within Microsoft Excel. Now you can learn to impress your boss in less than five minutes
Hi, my name is Ghamza Jacobs. I'm an IT trainer with New Horizons in London. I'm here today just to show you a couple of cool little tips and tricks on Windows7 and Office 2010.
How to use pivot tables in Microsoft Excel? A pivot table is a fantastic tool to create summaries and analyze data within Microsoft Excel. Sometimes, when all presented with mass amounts of data, if you look at the sheet I have up here for the moment, I've got some order details for various companies, various countries, sales people. But if I go down, I've got 2,156 records going across columns from A to K.
So, let's say your boss comes to you and says, “Oh, how much quantity have we pushed to each country over the last two quarters?” Looking at the data like this, you'll go, “Ah, yes. I think I'm going to have to get back to you in about an hour or so.” So, the pivot table really allows you to transform the data, easily analyze it and really pivot or rotate your view on the data.
That's why it's called a pivot table. So, one of the nice things about Excel 2010, as well as 2007, they make pivot tables really easy. Let's have a quick look.
All you need to do is position your active cell some way inside your data set. You go to Insert, Pivot Table (on the far left), you then get the table range selected over there. So you can see it's got A1 to K2156.
Now, you really don't need to select the data. If it does not select your data correctly, you would obviously have to go in and manually select it but 2010 and 2007 Excel is really good at selecting data. So, A1 to K2156, I'm going to hit OK on that.
It then takes me to a new sheet, which was one of the options on the screen just before, new sheet with a blank table. Now, the way the pivot table works is it actually works with row, column and values. So, if you have a look on the right hand side here, I've got a pivot table field list.
It's taken all my headings from the top here. So, it's taken all my columns and converted into what we call fields. These fields can now be populated down at this area at the bottom over here.
So let's say I wanted a total quantity. I select quantity. I can click and drag with my left mouse button down to values over here.
So I take quantity, drag it into values. That gives me a total quantity. That's the sum of all the quantity that has been sold out.
I then wanted, what did the boss say, we want the total quantity per country. So what do I do? I take the country off the top here, that field; I drag it to the row. And look what happens.
It now breaks down that total to give me a total quantity per country. So looking at that versus that, you can already see, it's much easier to understand. Boss then comes in and says, “Oh, yeah.
What was the salesperson who worked with that?” I can actually take the sales person and put the sales person at the bottom too and that groups it now. You see? So now I've got country, sales person and how they've been selling per country. That is a very, very easy way to create a pivot table and work with it.
You've just learned how to create a simple pivot table in Microsoft Excel 2010.