- Hi, welcome to this
special pivot table webinar. My name is John Michaloudis and I'm the founder of MyExcelOnline.com and I'm also gonna be your
tutor for today's webinar. Now this is my house and my studio and this is where I do
all of my Excel recordings and also my webinars. So welcome to my house. And I got to tell you that I've spent many hours
perfecting this webinar just to make sure that
you're gonna get great value and lots of pivot tables tips so you can walk away from this and become more efficient
just after 45 minutes. And I want to make sure
that I deliver, okay? So once again, I'd love to thank you for
taking your precious time out to come into this webinar and that means that you're
a really special person and you're different from the rest because you're committed to your success and I'm gonna try my best to help you out. So let's get started. Let's go straight into the slides and into the Excel pivot
table course, okay. Okay, we're here in the Excel workbook and what we're gonna talk about today several different parts that
we can use a pivot table for. Now on the left hand side here we have our different subtopics. Now we're gonna firstly
create a pivot table then we're gonna format it. Then we're gonna use the
values by calculation, then we gonna use the
values as calculation. Then wanna group, then we're gonna insert
some slices and charts. Then from that, we're gonna create a cool
interactive dashboard. And finally, I wanna end with just a few time saving and cool tips. Okay. So let's get started
with creating a pivot table. So let's go in here in our data source. So usually normally what
we get from our ERP systems at work when we get different downloads, we get lots of transactional
data as you can see here, okay does all the way down. It could be, you know
many rows of the data. With that pivot table, you've
got up to a million rows of data that you can use, okay? Now, normally we have
at, in this format here which is called a tabula format. So our data has to have
a unique column heading like we have products there and then at the bottom have
our different products. The same thing for salesperson. That's the unique column heading and then have the different salespeople all the way down here, okay? So this is a tabular format. Now, one thing you got to make
sure is when you have a date for example here, make
sure that it's informed that's formatted in a date format, okay? So to do that, you can just click on there and from in here, just go to short date. Now, when you have
values like we have here, also make sure that we
have values in there and there are no text cells, okay. So just to make sure
that each column pertains to the information that
it has to have, okay. So an important thing is we have a blank column down here, okay? Now to do a pivot table,
it's not gonna work. We would've make sure that there are no blank columns or rows. So if you have something like this just highlight it, right
click and delete it. The same thing here, you
can see we have a blank row. Right click and delete, okay? So just make sure that before you start that your data is set up
properly like we have here. Now another thing is, you
should always use Excel tables. And Excel tables are fabulous,
I use them all the time. It doesn't matter if you
have million rows of data or you just have three lines of data. Always use Excel tables because they're gonna
help you when you refresh and update your pivot
tables with more data and also it has some other core functions and that's just another topic but I'll show you just what I mean. Okay. So if you create a Excel table just click in there and you can press insert and table, okay? Another thing is, a shortcut
Control + T, Control + T and what it does it knows that your data is all the way down there. And also it's always good
just to make sure, okay? For example, here we
have a total of there. We don't want that total there, okay? So just make sure that
we go all the way to 577 and then press okay. All right. I'm just gonna clear that. So this is the table. And let me just move that here. We know this is a title is
because when we get out of it we don't get this pop-up tool tab up here. When we click in that,
we have is table tools. As you can see there, we
got different designs, okay? So that's another cool thing. We've got different designs there. Another thing is when you scroll
down, just have a look now. When I scroll down, the
column headings stay there. That doesn't happen
with a non Excel table. So that's another cool feature. The best feature for me is,
lemme press Control + Down or the Control and the Down Arrow key, all the way down here. What we can do now is
when we're in the table we get this table tools, as I said before if we're out of it, we can't do anything. So we're gonna back into it. Okay. Now the total row,
this is the best function. What it does is, it gives you a total. You probably can't see it here,
let me make it a bit bigger. Okay. All right. So it gives you formulas in there. So you can actually sum
the whole row, okay? You just automatically sums
it or you can count it. It gives you how many transactional rows of data we have there and also we can do an average, okay? So that's the best bit about it, okay. Now to add a row, you
just right click in there and go to insert, okay. Well, you can go all
the way to the end here and you just press the Tab key or you can also, if you
can see here on the corner see how it's changed there you can also drag it down, so you can add information there okay? So always use Excel tables, okay? So let me just make this
a little bit smaller. So now we have our Excel table let's insert a pivot table. Let's go to insert and pivot table. Okay. So we have this dialogue box. It says, what is your range? Now because it's a table,
it knows that has a name. So it gives us table one. Let me step out of that. Another thing is when we
go to table tools, design the table name is here. You can change that. You can say table two. It doesn't really matter. Let's go again to insert and pivot table. You see that? Table two. So it sees that as just
one structured reference. We have that table, that's okay. The next question is asked us is, choose where you want the pivot
table report to be placed. We can put it into an existing worksheet which is somewhere in here or we can go to a new worksheet. What it does is it create
a brand new worksheet. If I said existing
worksheet, we can put in here or we can go into any
other worksheet, okay? Now what we want is a new
worksheet and press okay. So what it does is it puts it
into a new worksheet in here. So from in here, you can
create your pivot table. But what I'm gonna do is just
go back in here, go again. Doesn't really matter. Insert pivot table. And I'm gonna go another
example, just to show you and go to an existing
worksheet and go in here and we can just put it all
the way there and press okay. All right. So it's put in the pivot table into this originally
created the worksheet. So if I step out of there, you can see that the film list goes away. To bring it back, you just
got to step into it, okay? Now I can close it from there. And from here on the
options, I can reactivate it. I can also grab it and
move it all the way across. You know, we're can put it over here. To lock it back in, go all the way to the right
as if you're throwing it out of the screen and
it locks back in there. So in here we have our column headings. Okay. So you see them all there. Now in the row labels, the
row labels are here, okay? Going down on the left side you can see here that,
that's a row levels there. So in here, it shows us the unique values from the field down, the rows of that left
side of the pivot table. And normally here, we're gonna put in the
products, names, location and business units. In here, we're gonna put our products. You can just drag and drop just like this. That's the best way. That's the way I do it. And as I've done that and drop it in here you can still get a live preview. And it gives us the
unique values that pertain to the products column. Now I'm gonna grab the
salesperson and drop it in there. So you see we have the
products and the sales person. I can also move up to the top and have the salesperson on
top and the products in there. Okay. That's the row labels. Now the column labels up here
it's what goes across here, okay, on the top. And usually we have the trending there so we have months,
periods and years, okay? So in here, we're gonna grab the year and just drag and drop
into the columns, okay? As you can see there, lemme just move it all the way here. Okay. So you can see it much better, okay? So we get a live preview
of the unique items that are within the year. So we had 2012, 2013 and 2014. Okay. Now the values which is in here, which is in the guts of the pivot table here we pull in our sales normally, okay? So let's get our sales in
here and drop it in there. So as you can see it's put it all in order
for us in the years and in salesperson and products. In the report filter, we put items that we want to drill down for example, regions, employee
names, lines of business. So in here, let's just put in, for example let's put channel partners, okay. You can see the filter
comes up there, okay. So we can drill down from there. So as you can see we've done a quick pivot
table in a matter of minutes. It's as easy as that. And if you have any
questions, just type it in now and then I'll get back to
you at the end of the course. Now, once again, this is being recorded and you're gonna receive this video and then you can have a
look at it at your leisure. So very easy. I just did it in a couple of minutes. I was explaining it, but
it doesn't really matter if you don't wanna have sales personnel you're gonna have a year in the row labels, you just take it, drop it and drag it across just like that. It doesn't really matter, okay? We can put in there,
you can chop and change. It just depends on what you want to show or what your boss is looking for. Within the day, we always get
instructions from our bosses because they get instructions
from their bosses and we always create those
reports based on their needs. And it changes from day to
day or from week to week. If you do a cool report, I'm sure that your boss is gonna come back to you the next day and say, oh can you do me another
report of this and that? So once you, once you get the appetite wet they're gonna come back
to you all the time and they're gonna love you for it. Trust me. Okay. So now let's go to the format. One second I can sip of water. All right. Now, in formatting, okay. So what I do here, lemme just go back to the original way it was. Okay. No, it was years. Okay. Just like that. I can probably take out salesperson there just to make it a little bit smaller. Okay. Now what we can do,
we can actually copy this. Just highlight everything
and you press Control copy or from the options, you can go to select and type pivot table, Control copy. Let's come in here. Let's go to format. Okay. And Control + V. So he puts it in there. Okay. Now what we can do in formatting refreshing a pivot table. So once your data source changes, for example, this is our data source Control + Down to go all the way down. Let's just drag in here. Okay. So for example, your information gets
updated by another person or the next day you it just gets updated. Let's update this. Let's click on that and press Control + D,
shortcut to copy down. So whatever is above. Okay. So we've copied this. Now, for example, let's
put in a high value here. Let's put in 1 million. That's what 10 million, okay? So let's go back to our
pivot table in here. It hasn't got updated. Now to update a pivot
table, you need to refresh. So to refresh, click on your pivot table go to options, refresh. Or what I like to do,
right click and refresh. Here we go in there. Okay. So we have the
change that was made there. Okay, so what we can, you
always gonna make sure just to refresh. So if we go back in here, data
source, right click, delete go back to our pivot table. You're gonna see this
is gonna get changed. Go to options and refresh. You see that? So always make sure to refresh when your pivot table gets updated. Now, another thing we can
do is number formatting. To format these numbers, let's bring up our field
list again and go to values. One way is to go in here and
go to value field settings. And from in here, choose number format and from in their we can number
and then put some separator and then put it like that and press okay. So we have the formatting done like that. It looks much better. The reverse Control + Z,
just to go back, okay? Another way is to just
to right click in there go to number format and then from in there, is a quick way. Always right click, there's
a lot of options there. Pivot table designs. Okay. If we are out of here, you can't enter into a pivot table. You always gotta make sure
to step into the pivot table. So it gives us the pivot
table tools tab here. Now under the design option we have over 80 different styles in here light, medium to dark. Just depends on what you like. You can just scroll
across and you see that you got a live preview,
which is awesome, okay? I like using this here. Now another thing is under design you can go under subtotals and you can switch on
and off the subtotals grand totals, you can switch
on off the grand totals. Just press off. You can see that, okay. And let's put it back in. Okay. Let's right click in here and bring the field list up again. So we have a big sum of sales in here. Now we can drop this as
many times as we want. So let's go again, drag and
drop in here, as you can see and let's do it one more time, okay? Another way in here we can just from the dropdown box just click in there and go
to value field settings. And in here we have the
sum, which is what we have or we can change it to count
average, maximum, minimum the product, account numbers. It's got 11 different options there. Now let's choose count and press okay. And then for in here this is the same thing and
let's choose the average. So as you can see here,
if we just scroll across we have the sales, we have the count which is the number of transactions and we also have the average for the regions and the products. Now, from in here we can choose to select
just one year, 2012. So can see that. And just to make this a little bit better we can get rid of the grand totals. So off for rows and columns. And then once again, right
click in here, number format and let us make this a little bit prettier and press okay. And then we can just highlight that and put it in the middle. Okay. So we have the sum of sales, we have the count of sales
and the average of sales. Now we can change this as well here instead of saying count of sales, USD two we can just put in the
count just like that. Okay. Delete that. And here we can do the
same thing for average. Now, if it doesn't let you,
because several examples there's a field name called average just make sure you put
a space in here, okay and then press okay and that will fix that problem. Now, if we right click
and show field list, you can see that that
has been changed there automatically as well. So pretty neat stuff. Instead of sales we can have different metrics
to show on our pivot table. So you can move this around if you want the count to
the average and so forth. Okay. So let's go to the show
values as, all right. Now, what we're gonna do is let's put in another pivot table, insert
pivot table, existing worksheet. Let's go to our location and put it in there and press okay. Okay. So we've created
another pivot table. So in here, let's drop in our sales month in the row labels and the financial year in the column labels. Make this a little bit bigger. Okay. So the other thing I wanna do now is put in our sales in there. Okay. So let's just make a few cosmetic changes which are like and then we can get going. Okay. Here we are. Let's get rid of the grand total. You can just click in there,
right click remove grand total. All right. So let's bring our field list in here and just move this a bit in here. So, okay. Instead of doing that, let's just, we can just
keep it there. That's fine. I'll make this a little bit smaller. Okay. So you can all see that. All right. So let's go back into our pivot table to activate our field list. So what we gonna do now is we
gonna in there another metric which is called the dollar
hub running total in or year to date. To do this, you got to grab the sales and drop it into your values area, okay. From in here, click the
value field settings. Now instead of summarize values by which is what I show you guys before, let's go to show values as. And from in here, you have an array of
different calculations, okay? So I'm gonna go through
every one of them now. I'll go through, I just show you a couple
of different ones, okay? Now let's go to the running total in. Okay. Running total in. So running total in
means year to date, okay? So to give us the total per month and it'll accumulate the previous month and then added on and will
give us a year to date. Okay. Just a simple terms. Now, the base field we want to make the base
field being the math because that's what we're
going to increment, okay? So we're gonna show values
as the running total in on a monthly basis, okay? That's how you read this,
press okay. All right. So let's get out of this here. Now what I'm gonna do
is just show 2012, okay? So right click number format. All right. Here we are. So what it does is it adds 771,000 January and then it adds 867 in here, okay? So those two there, the sum is 1638. Now, if you can't see if he's here, right click and make sure
that your sum is activated. This is a cool tip to use all the time. You can have the average
count, sum, maximum, minimum. So whenever you choose
a selection of sales it gives you some quick metrics, okay? So it shows us here it's
1,638 which is in there. Okay. Jan to March 2.4
million, which is in there. You can see it increments
all the way to the total. Okay. Let's activate our field list again. What I'm gonna do now is
just drop in the year, okay. I'm gonna drop in the year. First of all, let's clear
the filter for the year. So we're gonna show everything grab the year and drop
into the row labels, okay? So we just have the years and
the months going in there. Okay. So now you can see we've done that. It does the same thing for each year. It starts in the new year
and end at the year to date okay, for that particular year which is, you can move around and still has that metric there. Now I wanna add in another one which is the difference
from the previous month. Okay. So once again, grab the
sales and drop it in here. So once we drop it into the values area we can do as many
metrics as we like, okay? Drop down narrow, value field
settings, show values as. Now, we're gonna show the difference from the previous month. Now, drop down box there
and it goes down to difference from, okay. Now the base item previous
and the base field month just to make sure there's a correct, okay? So what it says here is, it's gonna show us the
values as a difference from the previous month. That's how you're read it for. Difference from previous
month and press okay. All right. So let me just format
this a little bit better to get rid of that. Okay. Number format. And I like to put in a minus
red for the negatives, okay? Just stands out. So what it gives us is just the difference between the previous month. So the different between Jan and Feb you can see it was an increase of 95,000 and then, there was a decrease of 83,000 from February to March,
and then March to April there was an increase of 124
and so on and so on, okay? So you can see the difference
from the previous month. So you've got all these different metrics and you got heaps more. This is just the tip of the iceberg. Now in my extreme pivot table course I'll go through every single item. So every single item that
you see in here, okay. All this, I go through
every single one, okay? All right. So I hope you understand that. And once again, if you have any questions just pop them in there now. Lemme just have a bit
of a sip of the water and we can continue on. Okay. So you can see the power within the pivot table and you don't have to use formulas. You don't have to use formulas. You just got to choose the right buttons and then you have it there. And if your information gets
updated in your data source with another a hundred thousand rows all you got to do is go back
in here, right click, refresh and it gets updated. Imagine that, the power of that. You can show that to your
boss, take a screenshot and say, hey, this is
how business is going. This is how our products are trending and you can make some
insightful business decisions. And I'm telling you you're gonna be the
go-to person in your job once you master pivot tables. We don't really have to master them. You've just learnt that now. So if you can just show you this you'll become a master in your work. Imagine if you learn every
single different item that you can do with the pivot table then you'll be doing courses like myself. All right. Let's move on. Grouping. Okay. So now we can group our pivot table and I'll show you three different
ways how we can do that. One of them is grouping numbers and the other one is grouping the date and also we can also group by text, okay? So what I'm gonna do now is just drop in another pivot table. Click in there, insert pivot table just go to our grouping area here. Let's go into the numbers. All right. And do it in there. So we're gonna drop in here
the sales in the row labels. Okay. Something that's not usually done. So let's drop it in there. And it shows us all the
unique sales amounts, okay? You got heaps there. Okay. And we can just
click in there, right click and choose group, okay. And it automatically chooses
where to start and where to end by the minimum maximum amount and these are the increments, 10,000 and we can put in hundred thousand or we can put in 20,000,
whatever you want. Let's just use 10,000 and
press okay. And look at that. It gives us the sales ranges, okay. So now what we can do is grab the sales and drop it in here, okay? Now it gives us the count of sales. So it just counts how many transactions are between zero and 9,999
and then so on, okay? Now let's jump into sales in here again. And instead of having a
count, let's choose the sum. Okay. So we have the sum in there. So we have the sum and the count. So for example, from the, for the sales range 90,000, and 99,000. So between 90,000 and say 100,000. So the sales, individual sales
valued between that range we had sales of $5.8 million. That's what it's saying there. And we had 62 transaction
between that sales range. Okay. If you wanna ungroup, ungroup it right click and just press ungroup, okay? Now date fields. What I'm gonna do is go in here and just create a quick pivot table again. It takes us a few seconds okay, and drop it in here. Now what I'm gonna doing in
here is dropping the order date in the row labels or the date row labels. So it gives us the
unique order dates, okay? All right. So from in here,
we just right click and group. Okay. Because it's date,
Excel is smart enough and it gives us the
grouping by the dates, okay? So we can group by dates, if it's in blue, it means it's selected and click again to unselect, okay. So we can select in quarters, okay? With the starting and
ending date is automatic and just live like that,
quarters and press okay. And you can see it's done in quarters. Just press Control + Z to go back and then right click and group. Let's do by days, okay? When we choose days, it gives
us the number of days options, scroll bar. So let's choose seven, okay. So one week. Let's group by weeks and press okay. So you can group by weeks. So now we've got that. We can put in our sales and say, okay how much sales did we make for that week? Go like that. Now, we're getting a count of sales. The reason why we're
getting a count of sales a couple of reasons. One is that you may have in
your sales column, a blank cell. If you have at least one blank cell you're gonna get a count of sales. Another way is that,
let's go back in here. Is because once we group values and then you create another
pivot table like we have now it gives you a count, okay? So don't freak out. If you get a count, all you gotta do is just click in here, okay? And then just put in and double click sum. Okay. So we have the
different sales values that or the total sales per week. So you can see that, okay? The different sales per week. All right. Now finally,
we can put in there another pivot table. Okay. You see that? It's just in there. All right. Now let's
move this across in here so you guys can see a little bit better. Now we're gonna put in the
regions in the row labels and the years in column labels, okay? So, and also let's not forget
sales in the values area. Okay. We've got a count because we have a group sales before. Okay. Let's put you in there. Okay. Perfect. Okay. So what we can do now
is, we can actually group text for example, let's just say
that our organization changed and Americas and Europe is one region or one poll or whatever you wanna call it and Asia and Africa is just
a different region or poll we can do is, name this West. So highlight that, right click and group. So it automatically gives
us a group name, group one then it puts those in each
as separate groups as well. So let's just, don't worry about this now. It gives us group one. Let's change that. Let's change that to West. Let's just type in there West, okay? And in here, let's just
highlight all that, right click and group and name that East. So we have our two groups
East and West, okay? And you can see here, region two has been created here, okay? Because we grouped text. What we can do now is we
can actually grab this and move it across, okay? Just like that and we can have East and West and we can see the different values that pertain to the West. You can see it's only
American and the Europe and East only Asia and Africa, okay? Or we can just move it back like that. So you can group text as well. So now we're gonna go on to that fun part which I love, slices and charts, okay. Okay. So now we're gonna
grab our text in here. Okay, let's just a group text Control copy go in here and just put it in there, okay. All right. So we have that in there. Let's just to get rid of the grand total. Okay. Make this like that. Perfect. Okay. So now to insert a slicer,
first of all, slices what they are, are large buttons. I'll show you what has been
selected in your filters. So they are your visual filters. Now, if you click in there you have these different options there. And so you just select
a couple 2013, 2014. Okay. It doesn't show you in here what's selected apart from
in here, what's not selected. Okay. But with a slicer you can actually see the selections made and you could see what
hasn't been selected, okay? So let's just clear that. Now to insert a slicer,
click in a pivot table, go to options and insert slicer. Okay. Here we are. So it gives you the list
of your fields, okay? Or your column headings. You can choose any one of them. It doesn't have to be in your pivot table. Okay. It can be outside
of your pivot table. So in here, what I'm gonna do now is put in the sales person, slicer case or something that's not in there. Let's put in the our salesperson
and press okay. All right. So here we have one of our slices. Okay. And you can actually, from in here just change the style, okay? If you click out of it and click into it you can see your, the
slicer tools option, okay? So you can actually choose
different colors in there. All right. Okay. So if we choose Homer in, right? John Michaloudis, Michael Jackson you can see that information
gets updated accordingly. Okay. So we have one slicer. Go and drop in another pivot table. Let's go back to create one in our data source, insert pivot table and let's go to our
existing worksheet in here. Okay. And press okay. All right. So let's create that. Let's move in here and let's
put in our sales in here, okay? So because it was grouped as you can see it's already grouped for us. Okay. So let's grab the sales and then drop it in here and
just get the, that one there. All right. Perfect. So we have our second pivot table. So what we can do now is drop
in a slicer from in here. So we're in the pivot table
options, insert slicer let's go in to the months. Okay. So we have the different months. You can see there. Okay. And that changes there. All right. So you can choose the month and we can also grab it
and scroll down, okay. You can also press Control key and select and unselect a couple or use a Shift key as well to
select multiple items, okay. So we have our slicer. So we have two slices, okay. One for our first pivot
table, we'll put up there and we have this other one
for the second pivot table. Now just another thing you can do here once you click in this slicer,
under slicer tools column you can actually put into
three different columns, okay? It's just a cool trick. So you can have it into
quarters there, okay. And let's change it around a bit. I can make it a bit more fun. Okay. Now, one thing that we can do now is let's put in a pivot chart, okay? So we have a slicer. We have this slicer
that's connected to that. Just get rid of this. It's annoying, okay. We have the month that's collected in here that's connected, sorry, in there. Now, okay, so now we can
insert a pivot chart. Let's go to options. So once you're in the pivot table go to options and pivot chart, okay? So in here let's choose a
column, okay, and press that. So it gives us our pivot chart. Now right click in the button there and just hide all field buttons on chart just get rid of that
because that's annoying. All right. So let's just put that in here. Or we just, just for now can I just make to it
visible to all of you we'll just put in here just for now, okay. So as that changes,
the pivot table changes which is connected to the pivot chart and that changes as well, okay? And then right, top right
hand corner, just to clear it. So we're gonna have all
the selections there. Okay. So there's a pivot chart,
committed to the pivot table which is connected to this slice site. It's like a big happy family. Okay. Now let's put in another pivot chart for this pivot table here that we created. So options, pivot chart,
and let's choose a a bar chart and press okay. All right. And once again, let's just
get rid of the buttons there. Okay. And you can just name
this, whatever you like but let's just get rid
of it for now, okay? And then what I'm gonna
do is just place it on just leave it here for now. Okay. So with our second slice that we just connected to
the second pivot table. Okay. Up here, let's
just make it like that. Okay. Better still, let's just
move it in here. All right. So we have our second slicer,
our second, okay, in here. You just separate it a bit
'cause it may be confusing. All right. So this side here is a
different pivot table with different slicer and
different pivot chart. Okay. So if we change that you can see that the
amounts changing pivot table and also your pivot chart, okay? So it's pretty cool. All right. But we can take this a step further. We can connect this slicer with this other slicer in here, okay? So we can connect these two slices and then control the pivot tables and the pivot charts, okay? And I'll show you how to do this. All right. Let's put that in there. Now go on to this slicer right click and choose the pivot table connections. In here, okay, we're going to choose, there's a lot of different pivot tables, but what we want to connect is I want to connect this slicer,
okay, to this slicer in here. So to do this, just click
in the second slide, at the second pivot table
they were connecting. 'Cause I wanna get the name. So option pivot table 11, okay? Let's pick pivot table 11
and this is pivot table nine. So pivot table nine and
11 have to be married. So once again, right click in your slicer. Okay. Pivot table nine is chosen. Let's choose people with level 11. Let's connect these two pivot tables. So by connecting these two pivot tables we're connecting the slices
and the pivot charts, okay? The same thing in here
for the second pivot table that we created. Right click pivot table connections and let's connect it to
pivot table number nine which is on that left hand side, okay. See what happens now as I change one slicer both charts change and both pivot tables change. You see that? As I change the other slicer,
both pivot charts change. So now from this we can create a cool dashboard. To do that, select the slices
Control, hold on a Control key select the pivot chart, Control + X. Let's go to our dashboard area, okay, and press Control + V and escape, okay. So now we have our cool
little dashboard in here with our two different you just putting in there, pivot chart. So as we change one, we can
see they both change as well. Okay. So that's pretty cool, isn't it? So we can have another
slicer from in there. We can also go to analyze, okay. So in the pivot chart, we can
go to analyze into slicer. Let's insert the year. Okay. So let's put in the year in here. Okay. Let's make few years another color. So from a pivot chart, you
can actually put in a slicer. We could have done it the other way we could have gone into our pivot tables, but you know I just
showed you another way. All right. So let's right click this new slicer and let's connect it to
pivot table number nine, okay just to make sure it's always connected. So 2011, 2030, sorry, 2012, 2013, 2014. Look at that and the title changes. Now we've created a quick dashboard and I am sure that your boss is gonna fall off his or her chair and
fall in love with you and give you a pay rise. If they don't come to me
and I'll speak to them. All right. So this is how easy it is to
create a slicer, a pivot chart and connect them together
and do a cool dashboard. As your data gets updated, all you gotta do is just right
click in your pivot tables, refresh and the information
in here gets updated as well. Okay. Let's finish up with some cool tips. Okay. Now what I'm gonna do is grad. Okay. Just Control copy there. Just grab this and put it in here. Okay. Might be not let us go and grab maybe this one here, okay. It's a little bit smaller,
Control copy in there. All right. Let's do that. Just to show you, okay,
so a few tips in here. Okay. Sometimes you get into cells. Now in here, we don't have any empty cells but say you do get empty cells. Now, if you want to show
a value for a empty cell you got to click in your pivot table go to options and options. And in here, layout and format you can see for empty cells, show. You can put in there a zero, okay? So instead of being empty,
you put in there a zero, okay? Now another thing is that say, you know you make this a little bit bigger. Okay. And make it pretty. And then you're right
click and you refresh. It moves it back in.
It's annoying, isn't it? Well, I hate that. So again, options and options and the order column width on update. We don't want that. We don't wanna to order width. We want to keep it the way it is. So say you want to make
it bigger and pretty and just keep it like that and right click and say,
you refresh your data. It keeps it like that. That's a cool tip. Another thing is, okay. Say that someone else has been updating your data source, okay you go all the way down here and they just keep on updating it and then you open this workbook and then you don't know that
someone else has gone in there and updated information. And that new information
is not being reflected. Okay, because it hasn't been refreshed because he didn't put in
that someone else did. How are you going to know? So a good tip to know is you can actually everytime you open your workbook the pivot tables can
refresh automatically, okay? So you didn't have to go in
there and refresh all the time. So I actually had to do this again options and options, options and options and data. And the third option here is refresh data when opening the file, click that. So the next time you open your file, okay? You get a little message
here. That's fine. The next time you open your file your pivot table will be refreshed. That's one less thing that you need to do. Okay. In here, what's due next month. Okay. So what we're gonna do now is let's put in there another pivot table. Let's go into our grouping
our date filter here. Let's go to select and type pivot table. Okay. And let's just put it in here just to make it a little bit bigger. Maybe not that big. Okay. All right. Here we are. So we have our dates in here
and let's just right click and just, we can ungroup for now. Okay. Now, I wanna see what's due next month. So from the dropdown box,
you get the date field test. You can actually see here
next month, this month last month, it lists next
quarter or next year. So let's say, go to next month. Okay. So it gives you here the dates that pertain to next
month and say, for example these were invoices, accounts
payable they had to pay. You'll actually know what amount
you need to pay next month. Okay? Now, when you come in next
month and you open this table it automatically re freshens
for the following month. So you'd only have to change anything and you have an array of
different options there, okay? So you can go through that as well. Now another tip here, okay,
is top 10 orders, okay. So what I'm gonna do is, I'm
gonna grab this just quickly let's just put in there, okay. So now click in there,
right click, show field list so instead of order date,
let's put in there L. Okay. Let's put in channel partners. Okay. Can we have our sales there. So channel partners and sales. Let's go instead of top 10 orders we can say a top 10 salespeople. Okay. Or sorry, channel partners. From in here, we can go to value filters and because it knows that
we have values in there okay and now this it'll give us the value filters top 10, okay. So top 10 in here, we
can do top or bottom. We can do five, 10 or a
hundred, let's choose 10. And then here, let's just
choose the item for now, okay? Just leave it as this and press okay. So it gives us our top 10. We can actually right click in there and sort from largest to smallest. Our case we have the top channel partner in the first row and
then the 10th down there. Okay. So it gives us that,
it's fantastic, top 10. Another thing is, you can go
to conditional formatting. So conditional format and let's make a data bar here
which is new in Excel 2010. And let's make a, you
know, let's choose that. So once we choose that we can actually have to
select the third option. So the third option means
it's going to condition and format all of the values
except the totals or subtotals. Okay. So you can see that we
have the green being on top and the red being on the bottom but we can actually go in there
and just change that around. We can actually put the red on top. Okay. And then the green on the bottom. Okay. So you can actually
apply conditional formatting and I've got a whole chapter on my extreme pivot table
course on conditional formatting with pivot tables. Now, finally tip one that I love and one that you're gonna
get lots of benefit from. Okay. Let's put in a new pivot table. Okay. By now, you know this off by far. Okay. So we're gonna put
in out salesperson in here and then we're gonna put in, sorry the salesperson that will
pop filter, one second. Let's just move this. Okay. Once second again, okay. That's fine. The reason why that happened is because the report filter
is gonna go on to my it's gonna go on to my text here. So what it says is, you
wanna replace the contents or the destination itself? That's fine. Let's just say yes. Okay. You see that? It's got rid of it. Okay. Let's put in the month, okay. And let's put in the sales in here. Okay. Okay. Let's put in like that. All right. So, and let's put in the
year in there. All right. So we have our sales per month and also per year for each salesperson and say, we have, look we have four salespeople in here, okay? And you know, you create
this cool looking report and you know, you make, you can even pull different
metrics if you like. Now, say you wanna copy
what you've done here is pivot table here with
your special metrics, your special conditional format. So you wanna copy that for
each different salesperson. Well, you can do that. Okay. Easy go to options and the
options are dropped down and then choose show report filter pages. So the report filter is here,
okay. So the salesperson. So show the report filter
pages, click on that. And it gives you an option. It gives you the option
of whatever field list is in the report filter. At the moment, we just have one. So we're just gonna choose that. When I press okay, have a look down here
at what's gonna happen. All the names are gonna be created as a separate sheet with
the separate values. Boom. Okay. Let's go here.Have a
look there. Look at that. We have Homer Simpson in
right, John Michaloudis and Mark Jackson as the own
sheet with their own metrics. So that's fantastic. So you can take that
and you can copy paste or you can print that and you
can save that page as well and you can give that to your boss instead of going one
by one and doing that. This is a quick and easy way it's a great tip and one that
you should definitely use. Okay. So that has been briefly
the pivot table webinar and now I've showed you
the most important features but there's lots more, there's lots more that you can do with this pivot table and I want to show you now
is just go on to my course and I'll show you the different parts that are within a pivot table. Okay. So in the course,
you've learned a lot of things about pivot tables, but there
are heaps more, heaps more. Now I've got a course called
the extreme pivot tables and I've worked over 12
months to get this course out. I've read many books,
I've searched the web I've looked at all the
different scenarios enhances and I've come up with a course where I have over 200
videos, 217 to be exact. And I go through every single
aspect of pivot tables. I also have a lot of tips and also have a lot of
business finance cases and some accounting cases as well. So I put everything into practice. I also have some bonus workbooks and I'll show you this quickly here. 'Cause this is the course
here is an online course and it's also downloadable as well. So you can view it online and you can also download the videos and you can view them when
you are in transit, in a train or you're waiting at an
airport or you're flying. And you know, you've got some
time to watch some videos without being connected. And the good thing about this is that all these videos are
about three minutes each. So just listen three minutes. So they're quick, they
get straight to the point and you also get each workbook that pertains to each tutorial. Okay. So I have an introduction here. I have a pivot table cheat
sheet, which is awesome. You can hang on your wall. I have some shortcuts, my
top 20, my favorite ones and they're in animated gif format. So you can play those and just sit back and view the tips and also
have all the workbooks, okay? So they're all downloaded there. Okay. Now the first chapter
is arranging your data set. So it just goes a bit about how you should prepare your data, how you refresh your data and how you customize your pivot tables. There's a lot of information there, okay? Now, the second chapter is
about value field settings. And you know how we talked about the sum, count, average, maximum. I'll go through all of those, okay? Now the third chapter is
about value field settings show values as, and we
talked about the year to date or running total in as
well as the difference from the previous month. Now, there was only two, but
I have 14 different scenarios. And I have a couple of accounting and financial case studies there. Now in grouping, we grouped
dates, we grouped values and we also grouped texts and you also grouped by
different scenarios as well. Okay. And I show them in here, all right. Now, sorting, I could've showed you how to how to sort from largest to smallest. And you can also sort
from different scenarios left to right, A to Z, Z to A, okay. Now filter as well. We said, we filtered the
dates for next quarter or next month or next year. And we also filtered the
top 10 values and bottom 10. Yeah. There's a lot of different filters. There's heaps of filters and I go through every
filter that is available. Okay. In chapter seven, my favorite chapter is all about slices, how you customize it, how you connect that and I've got some great
business case studies here and some interactive fun
tutorial for you, okay? We also have calculated fields and items. Now we didn't go through that but it's a little bit
specialized, but in here I'll show you exactly how
you create a calculated field and also a calculated item. Now, pivot charts, I'll go pivot charts and how to format them. There's 21 different tutorials
just on the pivot charts. So there's a lot of information there. Now, also got about
conditional formatting. I showed you how to conditional format just a row or just a pivot table area. But there's different, different
ways to conditional format. That's a whole course in
itself, but I show you, I go through every single
conditional format tool within a pivot table and
I'll show you how to do that. And it just, just brightens
up your pivot table and it just looks at, it
just looks much better and then the result is
just much more appealing and then your your management team is definitely gonna love you for it. Okay. Now the get pivot table formula. A lot of people don't understand
that it's a great formula. I explained to you how
you should apply it on and when you should apply it. And it's a great, it's
a great tool to use. Now, also talk about macros in here. So you can also do pivot tables
with macro second automated with the press of a button you can change your pivot table. So there's a 50% a bit of data management and how you can reduce
the size of your data and also different tips and also I've got in
there how you can interact with Microsoft's OneDrive, okay? And then finally, I have bonus
videos just for you guys. And it goes through sales forecasting. I'll also make a balance sheet,
interactive balance sheet, reconciling customer payments, a breakeven model, heaps
heaps of information there. You get all the videos there, this 217 you get all the
workbooks and I just show you now. Let's just go into one video there. Let's just click there. And then it goes into the video there. Okay. You see how quick that is? And it comes up. Okay. On the left, you have
the 14 different chapters and then we're in here now. Okay. So shows the video. It just make it bigger, okay. So you can see there, and I, you probably I don't know
whether you can hear it enough but I know, it's nice volume there. You can hear everything. You can also download the
video, just press that and then bang, look at that. You have it straight away. You can download it. Okay. You can watch it
whenever you want offline, at work, in the plane. Now attachments in there,
I have the workbooks. You click on that and then press open, the workbook comes up, okay? So you have that, okay. Lemme go back in here and okay. So we can exit out that. You can also see how are you progressing. So the percentage of
videos that you've seen and what's left as well,
you can also discuss so you have any questions you can go and discuss with your fellow
students and also with myself. So if you ever have an issue,
just write something in there and respond back to you straight away. So this is our complete
comprehensive course. And my fellow Excel
bloggers have said that this is currently the
best course in the world. Another person said that this is the most comprehensive course that they've ever come across and it is. I spent a lot of hours a lot of time just to make
this course remarkable. So once you finished that, you
just become an Excel expert and you just get to another level and you can get those
promotions that you want. And once again, thank you
very much for taking time out to take part in this special pivot tables and I have hope that you've learned a lot and I think I'm, I know
that you've learnt a lot and you've taken the first step into becoming a Excel master. Okay. So good luck and I'll
see you inside the course. (upbeat music)
