Free Lesson: How to Organize a Pivot Table

FREE LECTURE EXCEL COURSE: ORGANIZING A PIVOT TABLE

To enroll in the full course visit 5 Powerful Excel Skills

[Automatic Transcription]

I want to do is start to show you how we can organize our pivot table in such a way that it starts to produce the type of summary and ultimately chart that our boss was requesting and if we quickly go back and review his original request we can see that we already have heard out the first thing he was asking for that is all detail that we have on crude oil imports in 2005 sorted by company name we already produced that using our lessons they were contained in the filter section of the course what we’re gonna move on to next is getting things organized in such a way that we can produce this chart that he’s asking for where he can select any import country and see crude oil import started out by company from low to high okay now pivot table is gonna help us get our data summarized in such a way that we can then use a pivot chart which we’ll show you in the pivot chart section of our course to produce this chart so the pivot table isn’t going to produce this chart itself but it’s going to get the data summarized and organized kind of in a mini report it’s going to get it in a format in which we can quickly use that to create a pivot chart to show our boss exactly what he wants to look at okay if we go back to our file what we want to do is again make sure we have the pivot table selected go over to this field list here and the upper right corner of your screen and what we want to do first is make sure that we limit the data set that we’re working with in our pivot table to only crude oil imports so if you recall we had this column header in our set of data and imports tab entitled product names and what we want to do is because the product names field contained all of the different types of products in our data set what we want to do is use this as a filter you can click and drag this down into report filter what we want to do is filter our data set so that it contains only crude oil imports and how we can do that is by clicking on this grey box which should look very familiar to you because it’s the same type of grey box that appears next to each column header that has a regular filter on it if you click on that gray box right now we have you can see all is selected by default all of the products are selected but we want to limit our data set in our pivot table to only crude oil imports so by clicking on crude oil and hitting ok we now know that within this pivot table that we’re going to create this summary that we’re gonna create we’re only going to be looking at crude oil imports that’s important because our boss was wanting a chart that is limited to crude oil imports now the other thing that he wanted to be able to do if you recall was he wanted to be able to look at crude oil imports or any country okay so you might be guessing by now what we want to do is also pull in country names into our report filter box right here what this will allow us to do is now filter the pivot table that we’re going to be creating and continue to just think of it in terms of a summary that we’re creating we’re going to filter it we’re gonna give our boss the ability to filter by country name so we’re going to leave it at that all for now as it defaults to so what we’re gonna be looking at as is in our pivot table is crude employed crude oil imports from all countries by whatever it is that we end up dropping into our row labels and values and so I’ll show you what I mean by that now he wanted to be able to look at the quantity of imports for any country crude oil only by company sorted low to high so we’re we need to pull in a few extra pieces right before we can get a summary that looks like that it produces that so now this field RS name if you remember if I go back to imports this was the field that contains a company name so what I want to do is take this field RS name drag it into the row labels and now you can see that every company name that appeared in the imports tab now appears on an individual line in the pivot table now if I were to move this up to the column labels just for examples sake you can see that these names are one-by-one placed in each column it’s a little tight here in terms of from the perspective of how we want to put this summary pivot table together to have these in rows but there may be a case where it’s tighter to have this information appearing in columns they put this back in the row so that we just have a single company name listed out on each row now in order to look at quickly create a summary of how much oil was imported for each company crude oil only crossed all countries what we want to do is select quantity now I go back to my imports tab you’re going to recall that quantity quantifies how much oil is imported for each company and if I go back to my pivot table all I need to do is select quantity and pull it into values and now you can see these values appearing for the quantity for each company name so you’re gonna notice that says it’s going to default to count of and then whatever numerical value you have down here which in this case is quantity of oil imports crude oil imports to be specific right because we have our filter set to crude oil products names only what you’re gonna want to do is right click somewhere on one of these numerical values and go down to summarize values by and you can see that it defaults to account we don’t want to count how many times they’d given quantity appeared for each company what we want to actually do is summarize the total quantity of imports for each company and you can do that by switching this check mark it’s used to sum once I do that you can see these numbers change and this is now the total quantity appearing for each company of imports for each company for crude oil only across all countries okay previously when it would have defaulted to count again it was just counting merely the number of times that a given quantity any quantity appeared for any of these countries now we’re summing the total amount of oil imports for Beach so what we want to do next is start manipulating this data in such a way that it is prepared in a way that is it’s very easy to create a chart based of other than off of it that’s going to mimic exactly what our boss is asking for and I’ll show you how we can do that in our next video. 

0
Add to compare
On-demand
lean tools

Lean Tools

Sold by Jeff Adams
$99.00
0
Add to compare
On-demand
sharepoint course
IT

Introduction to Sharepoint

Sold by Sharepoint is your Friend
$199.00
0
Add to compare
New!
tibco-training
IT

Introduction to TIBCO Spotfire

Sold by Shakeel Muhajir
$299.00

GET THE BEST DEALS IN YOUR INBOX

Don't worry we don't spam

Profile Photo
Show full profile

Publisher

Petrolessons is a networking portal for Oil and Gas professionals looking to develop their skills, share knowledge and rise to the top of their careers.

We will be happy to hear your thoughts

Leave a reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Do NOT follow this link or you will be banned from the site!

Forgot password?

Enter your account data and we will send you a link to reset your password.

Your password reset link appears to be invalid or expired.

Log in

Privacy Policy

Petrolessons
Reset Password
Compare items
  • Total (0)
Compare
0