hi welcome to teachers Tech my name is Jamie and it's great to have you
here today I want to show you how to create a pivot table and pivot
charts along the way I'm going to give you some tips and tricks as
well if you would like to follow along with today's tutorial I have the data for you just
check Below in the description you'll see the link that you can download the data that I'm working
with then you can go through it with me step by step so we're starting with how to create a pivot
table and this is the data that we're using here now to do an insert uh it's very simple you just
go up to the menu and hit insert and below in the ribbon you're going to see pivot tables right away
as I go through these pivot tables I'm going to give you some tips and tricks also and I'm going
to start with a tip right away and the first tip is to select inside anywhere in the range or the
table if you're using a table uh before you go and click on pivot table because what it does it
automatically finds the r range for you so here's an example if I go up to Pivot tables notice that
they have some recommended pivot tables here I'm going to cancel and click out and I click
on recommend it again and it doesn't know now I could still select my my table or range from
here and it'll be fine but just as a quick tip if you select inside of it and pick pivot table or
recommended pivot tables it's going to know where the data is coming from now I just want to mention
a little bit about the recommended pivot tables uh this is a quick way to uh to create them uh
usually they're pretty accurate with probably the main ones that you would want but you can
see where it goes some of unit cost by region some of unit cost by item it does some recommendations
based on the data and the titles that they think that you would want so uh you can always start
with recommended pivot tables and choose one of them uh but we're going to be going from
pivot table on it so uh and after we go through pivot table you'll understand after you insert
the recommended pivot tables you can still make the same changes so I'm just going to go ahead
click pivot table and you can see that right away since I'm clicked inside of the uh range here that
it has the sheet and the range from A1 to g44 here now uh where do you want this pivot table
to go so this is important do you want a new worksheet or do you want it to be on the same
worksheet uh so I'm going to go new worksheet uh and so it's going to open up another tab you can
choose what you want to do on that or you can set a different location here so you can also choose
whether you want to analyze multiple tables and I'm not doing that in this case but I'm going to
go ahead and hit okay and what you get if I look down at the tab at the bottom you can see I have a
new sheet right here uh and now they're asking me to start building my uh my report by selecting uh
my different fields to to put onto it so now we have to be selecting our pivot table fields and
if we look over on the right and it can be done very quickly so if I go ahead and I'll just click
on sales rep and what you notice is it gets put into rows and now the names are going to go down
this column and then the data as I select it would be put into rows you can quickly change this so I
could D drag it into columns and now the names are going to go across the top and uh we are going to
have our information below it in the columns now we can uh quickly turn this off and on the sales
rep so if I click off of it it goes away we can drag these down too so if I drag this into a spot
I could dragged around into columns you can see how easy it is to manipulate so I have another tip
here that I want to give you uh so this is kind of the default setup here with the fields down
below where you're drag into if you go up to the gear here and tools and drop down you you can pick
a different way to view it so I prefer this one right here uh I'm just going to select it and we
get these uh on the side here with our different fields that we can just drag across now that's
up to you what you prefer and the other thing I want to point out with if we go back to it you
can sort your Fields A to Z uh so if you go ahead and select it you can see how it changes it uh now
this if you had a lot of different fields this would just make it easier to find them so just
remember those couple tips right there okay let's add some more things to this now so we're going to
have our sales rep here and I want this in columns and I'm going to place it here so you can see uh
some of the ones are hiding right now and the other thing I want to add so let's go ahead and
add our uh totals and you can see as I click on different ones how quickly it starts to change so
I can click on and off uh I'm going to go ahead and click on units it shows all my sums of units
here but what I want want is to have items so when I click my items now I do want this in rows
it places them down here so now if I pick my units it fills in so I can see each person so if I look
at Dwight I can look at per binder per desk and all the different items and it gives me the grand
total there so these are just some different ways you can get some quick uh your report being built
by selecting the fields so did you notice if you click out of the data that the pivot table Fields
go away so we C click back in and they reopen here now I want to point out just if you're adding more
Fields let's try uh I'm going to add a region here so if I click on region you can see it added it
to the rows at first and then it's going to be breaking down this way it breaks down the binder
and based on the Central north and south where where it was selling being sold in different
places now if I bring this over to columns you can see now that it's going to go across the top
it's kind of breaking uh it between the different uh salese so in the case of Someone Like eron You
can see that she sold in the Central and North while Dwight only sold in the central here so
go ahead try different things try adding uh you know by selecting different ones uh selecting uh
seeing how it works to get the different reports on it and then moving from rows to column to see
the ways you can adjust it now another way you can adjust it is to sort and if I look over here you
can see that we have a drop down and this is going to help me sort so in this case this is going to
sort my column labels which are the uh sales reps here and if I drop down I can go ahead and select
individual ones so if I said okay let's deselect and I'm going to go and let's compare Dwight and
I'm going to compare Jim and hit okay and you can see that we have the two right here and I can look
side by side to see their totals of their items being sold if I wanted to see total of amounts
in different ones I could pick a different uh thing to add from over here to add it and you
can see how quickly it can adjust so if you drop down again I could select everything hit okay and
everything goes back on I can select this way too so if I wanted to sort a little bit more uh if I
didn't want to see all the different items maybe I just wanted to see paper I could hit okay and
now I'm just seeing paper and I see the sales Pur so it makes it easy uh to look at that way
so go and try a few different ways to sort now I want to show you uh how you can actually build
a individual report and it will create a new tab down below for each sales rep or depending on what
you're creating and it's done only with only few clicks so before I show you how to create a tab
for each salesperson I wanted to point out the use of filters so let's say in this uh data with the
units I just want to see uh by region also and I like to filter through there so if I go and take
region and put it in filters you can see up top I can simply now drop down and I could be looking
for a certain region or zone so if I wanted to see in central I hit okay and I just get those results
back you can also see that you can select multiple so if you want it more than one or switch it back
uh it's easy to add more than one filter so or even move from different ones I could uh take
this sales rep here and move it up and now I can sort by region and sales rep so if I was looking
for uh let's say in the north and I'm going to also choose a sales rep I'll choose Pam hit okay
I can see in the North she sold this many if I was going to go back and check out a different
region uh at this point you can see now she hasn't had got anything in the South so I just wanted to
point out uh the use of filters but now I want to show you how you can use the filters to create a
tab for each salesman person uh in this example so I'm going to go ahead and deselect the items
that I have here and I'm going to build another report and remember I want a new tab for each
one for each salesperson so I do need a total and this is going to go into the values here I do want
the item there so I can see uh the item beside the total here and we're going to need the sales wrap
and I'm going to actually add this to the filter so I'm just going to drag it into the filter like
this now uh something else I want to point out out is to format your numbers here so if I go and
just select in here I can rightclick and number format so maybe you want it in accounting uh or
currency you can see the different examples that goes through so if I wanted two decimal places
and it to look like this I'm going to go ahead and hit okay uh and you can see how it formats
it you can choose the format that uh you would like uh the other thing I want to show you is if
I rightclick on this you can sort this so maybe I wanted from smallest to largest or largest or
smallest or some more I'll just go largest to smallest just like this so some things that you
can do uh to it now what I want to do is generate the individual reports on each tab if I go ahead
and drop down and go to options right here and you can see show report filter pages so when I
select this we're going to get show report uh filter Pages if I go ahead and hit okay now uh
it's going to go ahead and if I look at the bottom so if I look there's Andy there's Dwight's there's
Aaron I'm going to get an individual report for each uh for each salesperson and you could adjust
it depending on what you want so I wanted to make sure you understood how you could do that uh very
simply inside the pivot table Fields so I'm just going to go back uh you can see as I go through
these different ones I'm just going to go back to the uh original P table that I was working on
here and I just want to show you a couple other things and some more tips now let's say I was
curious uh where this 23514 number uh came from if you double click on a number so I'm just going
to double click on this number it opens this up and shows uh how that number was created by all
of this information so that was by a double click it opened up a new sheet when I did it you can see
there's a sheet 16 uh beside it now so just try double clicking on a number and it's going to show
you how it's even broken down more now some other things I want to show you uh with formatting and
usually things can be done in a few different ways I'm going to just show you if I go down to values
here right here and just drop down uh you can see that there's value field settings you can also get
to Value uh field settings if you right click on them too you can see value field setting settings
right here so if I go ahead and choose uh value uh this settings here value field settings let's say
I could you know I could say an average account uh if I was doing a count hit okay well the
dollars don't make any sense anymore but it's just counting up uh here would be1 15 rather than $15
so depending on what you uh were uh trying to show in your pivot tables remember that we have value
settings and you can change uh what you want right through here so I'm going to leave this as sum now
another thing I want to show you when you're right clicking uh is if you right click you can show
values as so I find this is a cool way to show percentage so do we want H you know you try a few
different one so if I say percentage of row total column total let say column total I can see that
the binders make up 48% uh the pens will make up 21% uh and it just shows you an a different way so
all these different reports can be generated and remember you're right clicking uh to get to more
of these here so I just go back to no calculation and it's gone so now I want to show you how you
can apply some conditional formatting to your pivot table numbers here before we get into the
graphing and charting uh so let's say if we want to this to be like a a data bar through here so
what I need to do is go back up to the home tab up top here and look under conditional formatting
if I drop down uh you can see we can apply all these different things but we're looking at the
datab bars datab bars and if I hover over notice it's just doing the cell that I'm selected in and
I want it to be applying to all of them uh in this case just go to more rules right here and you can
see apply rule to only that cell no we wanted to apply to that uh a cell showing the sum of total
values here so if I select this one here uh that will do the trick now the other thing do you want
the numbers to be shown so right now it show both or do you want to be just the bar only so I can
go and pick a color now and it will preview down below so maybe I pick an orange just like that
uh and I'm going to go ahead and hit okay and now I have a data bar applying to uh these different
ones so just different way to show uh you know show your reports that you can add conditional
formatting to them okay let's move over to graphing or charting our pivot tables now so let's
say you want to chart your pivot table report just to make it look a little bit better a little bit
easier for people to just take that quick glance and know what's happening uh so I'm just going
to select inside of this here and I'm going to go up to insert and take a look we have pivot chart
right here we do have the drop down I'm just clicking on it here and right away so we're
I'm it defaulted to the call I can pick I just get an idea maybe of different ones how it looks
here just by going through I'm going to keep this simple I'm going to create a pie chart here uh you
can see up top if I wanted to see a different view I can hover over to get the view uh to try any of
these different ones I'm going to just keep it to the pie right now okay so now just hit okay on it
and I have this in here I can move it around I can size it uh if I want to differently if you wanted
it larger here now I want to point out this is dynamic so I can take a look at different things
so if I wanted to take the look at the sales rep here uh maybe I wanted to compare different ones I
could I select multiple maybe I just wanted to pick one so if I pick Michael Scott I can see
just the one person's here so it's going through finding the information and breaking it down for
me uh you can see the items are over here uh so if I go back I can go and select all and it puts it
back again so you can sort using the chart to give those quick looks I can dropped out here also and
if I wanted to uh maybe deselect if I just wanted two things here so if I had paper and desk H okay
and it just compares the two different ones so you can do all these different things within the
chart still just like we were doing uh just with the data before so but you can apply this uh to
the Chart so now now what I wanted to show you too you can uh make this customize this look if
you just double click in it if this wasn't open this format chart area uh just double click on
it and it will open it up uh you can see I can quickly start to change things whether I want
it to be maybe a gradient fill uh you can see how the background changes I can adjust my colors uh
down here if I want to add different ones if I wanted a pattern pattern fill you can play with
all these different options we have our Tech options uh also uh here so if you wanted to
change different fonts and colors you can adjust all those things so if I go ahead and select uh
maybe I wanted this to be differently I can even drop down uh so that's the chart title or if I
wanted maybe the legend here you can see how it selects different things so I could go ahead maybe
I'll add a I can change the fonts like this if I wanted the legend to be positioned differently I
can start to change things all around so all these different things that you can play with uh to get
the chart looking how you want uh you can move these inside too so if I wanted to place these
in different places again you can size your chart differently to uh get it the way you want I hope
this pivot table and pivot chart tutorial has helped you out to make you feel more confident
when using it let me know what else you'd like to learn Down Below in the comments thanks
for watching this time on teacher Tech I'll see you next time with more Tech tips and tutorials