Pivot Table Excel Tutorial - YouTube

Hi everyone, my name is Kevin.

Today I'm going to show you how you can create  pivot tables in Microsoft Excel to do some  

pretty amazing analysis and as full disclosure  before we jump into this, I work at Microsoft.

All right, well what are we waiting for?

Let's jump into it. Here I am on my desktop  I'm going to open up Microsoft Excel.

The version that I'm using this is the latest  version of Excel, but if you have Excel 2019,  

2016, 2013, you name the version, you should  be able to follow along with this tutorial.

So what I wanted to do is I wanted to  come up with a fun example and so today  

I'm going to pretend that I'm a used car dealer  so I have a spreadsheet called car inventory.

I'm going to go ahead and open that,  and one thing I want to do is first  

kind of help orient you to the data  that I have in this spreadsheet.

So what I have here is this is my this  is my inventory of cars that I have.

As you can see, I have 24 vehicles in my lot and  it's a mix of different makes. I have Hondas,  

I have Toyotas, Nissans, Chevrolets, all sorts of,  

and then I have many different models Accords,  Camrys, Altimas, a variety of colors of vehicles.

I think black and silver tend to be  the most popular cars, so hopefully  

I have a bunch of those in my inventory. Then  I have the different mileage amounts, so some  

of the cars have a little bit higher mileage.  Some of them have a little bit lower mileage,  

so kind of a nice little mix here. And then I  have various price points. Now for the price,  

this is what I believe I can charge for the  vehicle so I've listed that. I've been working  

in this car business for a while so I have  a pretty good sense for what these cars will  

sell for. And then lastly this is what I paid for  the vehicle, and so you can see what my margin is  

here. I got $1,000 bucks on the car. If I could  get the $4,000 because I only paid 3000 for it.

Hey everyone, got to make a living so I  need to get a little bit of a margin there.

So this is my data here and now what I want to  do is I want to do a whole bunch of analysis.

I want to figure out, hey, what is my total  profit margin across all of these cars?

You know how many how many red cars do I have?

Or how many Hondas do I have?

I have all these different questions  about my data and I want to analyze it.

Now what I could do is I could write a  whole bunch of formulas like if I want to,  

you know, figure out what my  margin on this vehicle is,  

I can answer a quick formula,  but that takes a lot of time.

Pivot tables, simplify analysis and  really make analysis a lot faster.

In my job at Microsoft, I use pivot tables all the  time and it helps me to analyze data much quicker.

And so today I want to show you how I do that. So  what we're going to do is I have the data here.

We're going to go up to the  top bar and click on insert  

and you'll notice this option called pivot table.

I'm going to go ahead and  I'm going to click on that,  

but before I do that I want  to show one other option,  

there's also an option called recommended  pivot tables, so if I click on recommended  

pivot tables, this is Excel recommending  different pivot tables based on my data.

So here they have a bunch of things like I could  see the sum of the mileage by the different makes,  

and here I could go through, and I could  see some samples that they've setup. So  

kinda neat thing that could help you  really simplify pivot table creation,  

but today I want to show you how you can  create your own pivot table from scratch.

And So what I'll do now is let's  click on this pivot table button.

What it's doing is by default it  selects this table of data that I have.

It's already pre-selected it.

I could also change the selection if I want to,  but Excel has done a good job of picking it,  

and then I could choose do I want a new worksheet  or do I want to put it on an existing worksheet?

I'm just going to create a  new worksheet with this data.

So I don't want to change anything here.

All this looks good.

Typically, when I get this, I just  click on OK right away. All right,  

well here I am, this is the main pivot table view.

It looks a little intimidating at first.

You have a bunch of different  controls on the right-hand side,  

you have this other thing  that says to build a report.

So let's walk through how to do it. So you have  a few different options here. You have filters.

This is if you want to filter your  data by any one of these fields.

You have your rows, which is rows going down  a sheet and then you have your columns which  

are going across the sheet and then you  have the values associated with that.

Well, why don't we do a real example  and then you could see how these work.

I want to know how many cars do I have by make and  

model and then by color. It’d be just  to get a sense of what my inventory is.

So the first thing I could do is  I could click on these different  

items and then I could drag it into  these different boxes down below.  

So I'm going to take make and I'm  going to drop it in the rows column.

So here now I get a quick view of all the makes  that I have on my lot. So I have Chevrolet,  

Dodge, Ford, Honda, Nissan and Toyota. Now what  I could do is I could also click on the model,  

and I could drop it down there and now I could  see the make and each of the models under  

that make and what's kind of nice is I could  expand and collapse these different sections.

I could also collapse all the fields,  

or I could expand all of the  fields using these buttons up here.

I'm going to drag the color over to the  columns and so now here you see I have a  

table of the makes, the models, and the  colors and now I want to get a count.

So what I'm going to do is I'm going to take  makes and I'm going to pull that into values  

and so now what this has done is it's set it  to count and now I can see count by vehicles.

So here, once again I'm going to collapse it, so  here you can see for Chevrolet I have 1 green,  

1 silver and 2 white vehicles,  for a total of 4 vehicles.

And what I'll do is I'll go  ahead and expand this again,  

and now I could see that, you know, by  the different models, what colors I have.

So kind of a very quick way to  visualize my data without having  

to go through the spreadsheet and  filter or enter different formulas  

to get this data. With a pivot table  I can just just do that automatically.

OK, let's say that I want to know what my  profit margin is on my different vehicles.

So what I'm going to do is I'm  going to move the model out.

I'm going to move the make out.

I'm going to move the color out and I want to know  my margin by make. And so what profit margin is,  

it's the price minus the cost, so I'm  going to pull the price in. Here you see,  

this is basically what I expect to get if I  were to sell all of these models. So once again,  

I'm going to pull the let's pull the make  in again. So here you can see that I have  

my 4 Chevrolets and I believe that I could  collect $14,500 and what I'm also going to do  

is I'm going to pull in the cost and so  here you can see you know I expect to be  

able to pull in about $14,500, and to buy  all these vehicles, I had to pay $13,000.

So what I could do now is if I want to  figure out the margin I'm going to click  

on this thing that says fields, items and  sets and I'll insert a calculated field.

All a calculated field is, it's basically  doing a formula on different columns in the  

pivot table. So I'm going to click on calculated  field and then I'm going to call this my profit.

Profit margin and for the formula what  I want to do is I want to do the price  

I'm going to insert the price and  then we're going to subtract the  

cost and I'm going to insert the  cost and I'm going to add this.

What you'll see is it just added  this as a field to my pivot table,  

so I'm going to click on OK and now it's  automatically added this to my table.

So now you can see that I have 4 Chevrolets.

I believe I could collect this.

This is my cost.

And then here's my margin.

What's nice too is I could see well,  what make has the biggest margin?

What you know, I could look through  this and figure out which ones the  

largest or I can right click here, go  to sort and I could go from largest to  

smallest. So here I can see of my 5 Honda  vehicles I expect to make about $3,468.

So that's my biggest margin that I have. So once  again, pivot tables make analysis super easy.

Now let's say that I wanted to know  the average cost of my vehicles.

So let me, I'm going to pull  some of these values out.

I'm going to get rid of the make and to get rid of  

the profit margin. So let's say for  these different cars like Chevrolet,  

Dodge, what was the average price that  I paid when I bought these vehicles?

So here you'll see that this is  currently set to the sum of the cost.

What I could do is if I click on value fields  settings, I have all these different options,  

so instead of just looking at the sum of all that  I paid for these vehicles, what I could do instead  

is I could say the average. I'm going to click  on OK and now this changes it to the average.

Now, one thing I'm going to do just make it  easier to read is let's add dollar signs in. So  

here what this is saying is for my Chevrolet's,  I paid an average of $3,250 for each vehicle  

and for my Hondas I paid $2,500 and you can  see on average how much I paid per vehicle.

So once again, what I do there, is I go to  value field settings, and I could change it.

You know sum, count, many different  options in this list here.

Now the next thing that I want to do is I  have my 24 cars in my inventory. I want to  

know what percentage I have of each color,  so I want to know what percentage of my cars  

are red versus black or silver. So what  I'm going to do is let's go back and I'm  

going to drop color in the row field, so  we're going to drop that in and then I'm  

going to pull color here and then I'm  going to get rid of the dollar sign.

So what you can see is I have 6 black cars. I  

have 6 silver cars. I'm going to sort  this from largest to smallest again.

So here you can see I have 6 silver, 6 black  and then 3white, 3 blue, 3 red, and 3 green.

But let's say I want to know the  percent of silver cars that I have.

What I can do here is I'll right click on this and  

I'll do show values as and I have all  these different options in this list.

What I want to know is I want to know  this silver. So in this column this six,  

what percentage is that of the total?

So once again, I'm going to go back to show  values as and I'm going to say percent of  

column total and so now what I could see is  my silver cars make up 25% of my inventory.

My black cars make up 25% of my inventory and  so on and so forth. So there you know I could,  

I could show the values as percentages.

They have lots of different options that you  could do so you can play around with that. So  

what I want to do now is when you look at your  data, seeing it in a table format. That's one  

way to read the data, but sometimes it's nicer to  be able to see a graphical visualization of it.

So what I'm going to do now is I'm  going to click on analyze again,  

and then there's an option that is a pivot chart.

I'm going to go ahead and insert a pivot chart.

I'm going to go with columns.

I can choose many different chart  types, but I'm going to go with columns.

And here what you can see is this is  a column chart of my car inventory.

So here I could see you know 6 silver, 6  black, and then I could see the rest of it.

So just a visual way of my table.

And the really interesting thing is, you  know here what I could do is I could,  

I could drop in different values into my pivot  table and then I could see you know the chart will  

just automatically update based on what fields  I drop into my pivot table. So it's a very nice  

and easy way to pull together kind of a visual  view of your data as well with pivot tables.

One other thing that I want to  call out is you have this option  

that says defer layout update on the bottom.

What does that mean?

Well, with defer layout update, as I  pull items in and out, you'll see that  

it doesn't update my table and it doesn't  update the chart until I click on update.

So here if I pull the color out and then click  

update now it'll update only  after I click that button.

Why would you ever use that?

Well, especially when you're  working with very large data sets.

So let's say you're working with a  million or two million rows of data.

Every time you drag and drop  something into a pivot table,  

it's going to take your computer time to process,  

because it's doing a lot of calculations  based on how you set up your pivot table.

And so this way you can make sure  that you have your set up good  

before you make that update. So it just  helps speed up the process of editing.

Now I created this pivot table  and what I also want to show now  

is not only is this available in  Excel that I have on my desktop,  

but also in Excel online and one of the cool  things is Excel online is actually free to use.

I am in my Excel online. I'm in my office.com  so I've gone to office.com. I've logged in,  

by the way, it's free to log in. If you're a  paid user or a free user, you have access to  

this. And so here I see my recent document list  and my most recent spreadsheet is car inventory.

So I'm going to go ahead and click on that.

Now what it's doing is it's opening it in  Excel online and what's nice is I have this  

file stored in OneDrive, so I edit it on  my desktop and it automatically saves to  

OneDrive and now all my changes that I  just made are available in Excel online.

So I'm going to click on sheet one  because that's where the pivot table is.

But here you can see my car inventory. I have  that exact same data available here online.

I'm going to click in the sheet one and then  here you'll see I have my pivot table and I  

have the chart that I inserted on desktop Excel  and you know here in the online version what I  

could do is I could go to insert. I could just  as easily insert a pivot table and then here  

I could just as easily manipulate my data  just like I could in Excel on the desktop.

So whether you want to use it on the web.  Whether you want to use it on the desktop.  

Pivot tables are very accessible and  you could use them in either place.

That was a quick overview of pivot tables.

They're not nearly as intimidating  as you think they are, and really,  

the best way to start playing around with  the pivot table is just to start dragging  

and dropping things into columns, into rows,  into values, and just to play around with it.

It's a very powerful way to visualize  your data and to understand your data.

In fact, in Excel I very infrequently  now enter formulas, I very infrequently  

enter functions because with pivot tables I  could already do most of my analysis there,  

so there's less of a need to do all these  other formulas. And in terms of analyzing  

complex data or even simple data, pivot  tables do a great job at doing that.

In my job at Microsoft, it's helped  me tremendously in terms of analyzing  

data and understanding data and making  business decisions based on your data.

Anyway, I hope this tutorial is helpful  using pivot tables. Hopefully this helps  

you creating your own pivot tables. If you  enjoy this content, please give it a thumbs up,  

that always helps reaffirm that the content  that I'm creating is valuable. And if you want  

to see future content like this, please  hit that subscribe button and that way  

you'll get a notification anytime  new content like this comes out.

That's all I have. Thanks for tuning  in and I'll see you next time, bye.