Microsoft Excel Pivot Tables & Excel Dashboards in 1 Hour! | John Michaloudis MyExcelOnline. com | Skillshare

Microsoft Excel Pivot Tables & Excel Dashboards in 1 Hour!

John Michaloudis MyExcelOnline. com, Stand Out From The Crowd

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
12 Lessons (1h)
    • 1. Welcome Introduction

      1:25
    • 2. Why Pivot Tables?

      6:20
    • 3. Excel Tables & Tabular Format

      5:57
    • 4. Creating your first Pivot Table

      6:03
    • 5. Formatting a Pivot Table

      3:57
    • 6. Summarize Values By: Sum, Average, Count

      2:32
    • 7. Show Values As: Year to Date & Variance

      7:16
    • 8. Grouping Your Data

      7:02
    • 9. Inserting Slicers

      3:57
    • 10. Inserting Pivot Charts

      2:19
    • 11. Creating a Dashboard with Slicers & Pivot Charts

      4:12
    • 12. BONUS: Pivot Table Tips!

      9:40
17 students are watching this class

About This Class

Microsoft Excel - Excel with Excel Pivot Tables in 1 Hour!

DOWNLOAD THE MICROSOFT EXCEL WORKBOOKS FROM THIS VIDEO: Downloadable Excel Workbooks

Who is this course aimed at?

No matter if you are a Beginner or an Advanced user of Excel,  you are sure to benefit from this quick course which goes through the most important tools that are available in an Excel Pivot Table.  The course is  designed for Excel 2007, Excel 2010, Excel 2013 and Excel 2016 and Office 365.

In no time you will be able to analyze lots  of data and tell a story in a quick and interactive way, learn how to build stunning Dashboards and get noticed by top management &  prospective employers.

What you will learn?

  • How to set up your data and create an Excel Pivot Table in less than 3 minutes which will increase your efficiency!

  • How to put your key business metrics like Year to Date Sales, Monthly Variations and Top 10 Customers in an interactive Excel Pivot Table, taking your analytical skills to another level!

  • How to create an Interactive Excel Dashboard with Slicers & Pivot Charts that will WOW your boss and get you noticed by top Management!

  • Pivot Table tips & special Bonus material that you can keep and use to become better at Excel straight away which will skyrocket your personal development!

----------

I have been helping thousands of people further their careers by teaching them Excel the easiest and most comfortable way and I want to help you out as well because you deserve it! These tutorials will give you career-building skills that you can use to become better and more efficient at your job.

So  go ahead and learn from the following Microsoft Excel tutorials right NOW... every hour you delay is delaying your professional progress ...

John Michaloudis

Chief Inspirational Officer

www.myexcelonline.com

www.myexcelonline.com/blog - Sign Up to receive FREE weekly Excel lessons

Transcripts

1. Welcome Introduction: Hi and welcome to this special pivot table weapon A. My name is John Michael Livers and Andi founder off Mike. Sell online dot com and I'm horsing and be your trigger for today's webinar. Now, this is my house and my studio, and it's where I do all of my excel recordings and also my weapon us. So welcome to my house. And I'm gonna tell you that I've spent many hours perfecting this weapon up just to make sure that you're gonna get great value and lots off pivot table 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 love you thinking for taking your precious time out to come into this webinar. And that means that your really special person on your 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 shredding to the slides and into V Excel pivot table course. Okay, 2. Why Pivot Tables?: Okay, here we are, inside the pivot table, cause now I'm gonna go through just a few slides and then we'll stop without Excel Workbook . Now he's gonna show you some stats. Now there are over 500 million people around the world using marks of Excel and only 5% years it to its full potential. Now that's a very small, small amount off users, as you can see here on the bottom, right hand corner. There's a few people that really know how to use Excel, and the rest of the people don't use it to its full potential. And I show you why now the top three reasons as to why most people don't use excel or pivot tables. Number one is a lack off company training. Now I see this day in and day out where companies neglect employees personal development due to cutbacks due to downturn economy. But it doesn't have to be that way. People don't need to go into your office dio expensive training or you don't have to go off site to do the expensive training you can actually learn Excel online, and also you have to do that. Number two raises is the lack of time. Now I understand that because a lot of people are busy commuting their families but learning itself. You don't need hours on end to Lennox, so all you need is a few spin minutes a day and you will learn slowly, Excel pivot tables and formulas. And the reason is that people don't know excels for potential. Now I get that because there's a lot of different tours. Within. Excel has been a lot of different versions, but I'll show you just a few treats that you need to know just to get better. So you don't need to learn everything allow under the Master Off Excel. I don't even know Hattie Do v b a coding or macros. Now I can tell you that I know about 85% off excels capabilities, and you don't need to know 85%. You don't even need to know 50% with 25% you'll be better than the next person. Name your swimming up. You will learn how easy these two number one set up a pivot table. They just set up in less than three minutes and I'll show you that. How it's done. Number two, you will show you some of the vast analysis. So in here, I'll show you how to do some you today analysis with your sales figures and also some analysis on your top 10 customers for each of your region and many more tricks. And number three also, you have slices and had a insert, a pivot up and with the slightest and pivot charts. I'm gonna show you how to create an interactive dashboard where your boss can just click on an item and the valid changed and the people changes and he just gonna fall off his chair and become in love with you. Okay, so now let me tell you a bit about my story. I was born in Australia and raised on, went to university that my parents were Greek migrants, and I also lived in grace when I was small for about five years. But then we returned to Australia because Greece back then was bad as it is now. Now that was a good choice because we had some excellent university and I graduated from there with a bachelor of commerce with the major of accounting and then family jobs right away. So 21 years of age, professor job, which was excellent, and I started using himself back then. No, now wasn't the best in Excel, but slowly and surely, as he has passed on, I got to know Excel pretty well now. It wasn't until five years ago when I decided to really, really become good Excel because I wanted to be different. I wanted to stand out from the crowd, and I knew that Excel was a tool to make me stand out and get those promotions. Okay, Another thing is that with my extreme pivot table course that has taken me 12 months to do , it's a comprehensive cause. It's over. 200 people have tutorials and I'll show you that after how wedding? Uh, okay, now I've trained over 8700 professions and then roll satisfied and the ball taken their excel and people take the skills to another level. Most of my students come from USA Australia, India, Brazil, China, Spain, UK, from all over the world. It's not just one area and just amazing to the different types of people that come in, and not just accountants or financial controllers or analysts may give people that mean quality assurance. People that are auditors, some people that are in medicine, other people that are doctors, they just they just need to know there business. And also when they doing some studies with a lot of different analysis and difference statistical information, they need a pivot table two. Analyze that data and show it into a meaningful report. So all walks of life, it's not just the account of a calm scene. I just get amazed by the different walks of life that coming Teoh my course. Okay, so that's my story. Briefly now live in Spain and I married and I have one cheeky told his name is Michael and mingle, mingle. Ludo's never sounds pretty weird. Doesn't Okay, so now at the moment I'm in Spain and who knows which country I'll be tomorrow? Well, I think I'll be here for quite a while. All right, now, just going through our next life. Now, after this weapon, I'll give here a special bonus just because you've attended this weapon. A case? Ah, special bonus with lots of savings on. Just hang on after be course finishes. And then I'll show you that special 3. Excel Tables & Tabular Format: okay. Where he I am the Excel workbook And what we don't talk about today several different parts that we can use a pivot table for now, on the left hand side. Here we have a different sub topics. 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 use the values as calculation that when a group, then when I insert some slices and charts then from Matt Win create I called Interactive dashboard. And finally I wanna end with just a few time saving and cool peeps. Okay, so let's get started. We creating a pivot table. So let's go in here. You now, dinosaur. So usually normally what we get from our e r P systems at work. When we get different downloads, we get lots off. Transactional dieter, as you can see here. Okay, there's all the way down. It could be many rows of data. We give a table, you got up to a 1,000,000 rose off data that you can use. Okay, Now, normally we have that in this format here, which is called a tabula former. So Dina has to have a unique column hitting like we have proxy and then at the bottom have our different products. The same thing for sales person. That's the unique column hitting and then have the different South people all the way down here. Okay, so this is a table of former now, what you gonna make sure is when you have a date, for example, here, make sure that to form That's formatted in a date format. OK, 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 I just make sure that each column pertains Teoh the information that he has to have. Okay, so on important thing is, we have a blank column down here, Okay? Now, who did? People table. It's not gonna work. Wouldn't make sure that there are no blank columns or rows. So if you have something like this, just highlight, right? Click and delete it. Same thing here. You can see we have a blank broke right fleet and delete Okay. So just make sure that before you start that dialogue is set up properly like we have here now. Another thing is you should always use excel tables. Next. Cell tables are fabulous. Are using all the time. It doesn't matter if you have a 1,000,000 rows of diarrhea, 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 cool functions. And this is another topic. But we'll show you just what I mean. Okay. Said you create it a excel, tell you just clicking there and you can press, insert and table. Okay. Another thing is a short cut control T control tape. What it does he eat? Knows that your data is all the way, the on there. And also, it's always good just to make sure. Okay, for example, here we have a total there wouldn't want that total. Okay, so just make sure that would go all the way through 577 and then press, OK? All right. I'm gonna clear that. So this is the table and let me just move that here. We know if the title is because when we get out of it, we don't get this pop up to Tab up here. When we click in that, we have his table tools. As you see, there were different designs. Okay, so it'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. It doesn't happen with a non excel table. So that's another cool feature. The best fitted for me is let me press control down with the control and the down Harry cape all the way down here. What we can do now is when where in the table we get this table tools. As I said before, if we're out of kind of anything, so we're back into it, OK? Now, the total road, this is the best function. What it does is it gives you a turtle. He probably concept. Here, let me make it bigger. Okay. All right. So it gives you formally is in there, so you can actually some the whole row. Okay? You just automatically of sums it or you get counted gives you harmony. Transitional rows of data. We have the and also we can do an average. Okay, so that's the dispute about it. Okay. Not to add a row. You just right click in that. I'm gonna be insert. Okay? We can get all the way to the end. Yeah, just press the tab key. You can also, you can see here on the corner See how it's changed that you can also drag it down so you can add information. They okay, So always use excel tables. Okay, so let me just make it a little bit smaller. So now we have excel Table. Let's insert a pivot table. 4. Creating your first Pivot Table: So now we have Excel Table. Let's insert, give a table, this guy insert and people paper. Okay, so we have these dollar box. He says, What is your range now? Because it's a table e noser as a name. So it gives us a table one. Let me to step out of that. Another thing is only going to table tools design. The table name is here. You can change her. You can say table to Doesn't matter. Let's go again to insert people table. You see that table two. So he sees that as just one structure reference. We have the table. That's okay. The next question is, are cities cheers where you want the pivot table report to replace. We can put it into an existing worksheet, which is somewhere in here. Or we can go to a new work. See, what it does is create a brand new work. So if I said existing work so we can put in the hours, go into any other work seat, OK? Now, what we want is a new work seat impress. Okay, so what it does is it puts it into a new work shit in here. So from here, you can create your pivot table brought on when they do. You just go back in. Here I go again. Doesn't matter. Insert a pivot table and I'm gonna go another example just to show you can go to an existing worksheet and going here, and we can just put it or the way they and press OK, All right. Say is put in the pivot table into these. Originally created the worksheet. So if I step out of there, you can see that the film these goes away to bring him back. He just couldn't step in tow. Okay, now, okay. Closer from May and from hand options, I can reactivate it. I can also grab it and move it all the way across. We're gonna put over here to lock it back in, go away to the right as if you're throwing it out of the screen and locks back in there. So in here we have our column headings. Okay, so you see them. Hold that now into the burrow labels. The role levels are here. Okay. Going down on the left side. You can see here that that's the right. Was that so? In here. He shows us the unique values from the field down the rows, off the left side, off the people table. And normally here, we're gonna put in their products, names, location and business units in here. We're gonna put our products. You can just drag and drop just like these. That's the best way. That's the way I do it. And as I've done that in trouble in here, you get to get a live preview and he gives us the unique values that pertain to the Products column. Now I'm gonna grab yourself person and drop it in there. So, you see, we have the products and the South person. I can also member to the top and have the Southwest on top and the person that Okay, that's the royal episode. Now the column labels up here, it's what goes across here on the top. And usually we have the training there, so we have a month's period and years. Okay, so in here, we're gonna grab the here and just drag and drop into the columns. Okay? As you can see that, let me just move it along the way here, Okay? So you can see it much better. Okay, so we'll get a live Previ off 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 off the pivot table here, will pull in our sales normally. Okay, so let's get out. Sales in here and dropping next. As you can say eats, put it all in order for us in years and in South Person 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, letters poured in, for example. It's poor channel partners. Okay, You say that filter comes up there, Okay. So I can drill down from there. So, as you can see, we've done a quick pivot table in a matter off minutes. It's as easy as that. Maybe have any questions just happening now and then I'll get back to you at the end off the course Now, once again just being recorded and you're gonna receive this video and then you can have a look at your leisure. So very easy. I just did it in a couple of minutes. I was explaining up. It doesn't really matter if you if you're going to want, have cells person. They're gonna have a year in the rally, But you just take it, drop it and drag it across. Just like that, he doesn't really matter. Okay? We can put a man you can chop in Change just depends on what you want to show or what your boss is looking for within the day. We always get the instructions from our bosses because they get trucks, minibuses and and we always creator those reports based on their needs and change 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 since our can you do me another report of this and that. So once you once you get there, appetite where they're gonna come back to you all the time on bond, they're gonna love you for it. Trust me. 5. Formatting a Pivot Table: informally. Okay, so what I do here, let me just go back to the original way. It waas okay? It was years, okay? Just like that. I could probably take out of cells person. They just make a little bit smaller. Okay, Now what we can do, we can actually copy me. It's just hollowed everything in a press control copy or from the options you can go to select entire pivot table Trop. Copy. Let's come in here. Let's go to format. Okay, control vague. So you put it in there. Okay, Now what we can do in the formatting refreshing a pivot table. So once your data source changes, for example, this is a data source. Control down to go all the way down. This just drag in here. Okay, So for example, your information gets updated by another person, or the next day he just gets updated. Let's update this. Let's click on the press, control the shortcut to copy down. So whatever's about. OK, so we've copied these. Now, for example, let's put in Ah, high value between one 1,000,000. That's what, 10 million. Ok, so let's go back to our pivot table in here. Hasn't been updated to update a pivot table, you need to refresh. So to refresh, clicking your paper table So the options refresh or what? I like you dio rightly and refresh here. We got in there. Okay, so we have the change that was made A Okay, So what? We can you is there? Make sure just refresh. So if we go back in here Data source, right click delete. Go back to your people table. You gonna see these is gonna get change, good options and refresh. You see that? So I always make sure to refresh when your people have gets updated. Now, another thing we can do is number formatting to former these numbers. Let's bring up our feel this again and go to values. One ways to go in here and go to valley feel settings. And from in here choose number former. And from there we get number and then put some separator and then put it like that press. Okay, so we have the formatting done like that. It looks much better. Price controls it just to go back. Okay. Another way. It's just the right clicking their good number format from in there is a quick way. Always right click. There's a lot of options there. Give a double designs. OK, If we're out of here, you can't in. He will give it. You always gonna make sure to step into the table so it gives us the people table tools tap here. Now, under the design option, we have over 80 different stars in here. Light, medium. Too Dark. Just depends when you like. You can just scroll across and you see that you get a live preview, which is awesome. Okay. I like using this here. Now, another thing is under design. You could go under sub totals and you can switch on enough. The subtitles Grand totals. You can switch on and off the grand totals. This Chris off. You can see that. Okay. And let's put it back in 6. Summarize Values By: Sum, Average, Count: Okay, Lets correctly can hear Bring the fill it up again. So we haven't seen some of 1000 here now Weaken Drop these as many times as we want. So let's go again Drag and drop in here as you can see And let's do one more time. Okay, Another way in here within just from the drop down box Just clicking man got value feel settings And in here we have the some which is what we have what we can change it to count average maximum minimum the product account numbers Scott live in different options. Let's choose account on press. Okay. And then for in here you see the same thing. 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 also have the average for the regions and the products. Now, from here, we can choose to select just one here 2012 so you can see that. And just to make this a little bit better, we can get rid off the grand turtles so off for rows and columns and then once again right click in here number format and just make this only be prettier and press OK. And then we can just highlight that and put it in the middle. Okay, so we have the sum of sales of the count of sales and average sales. Now, we can change this as well here, instead of saying count of cells USD to just putting the count just like that. Okay, Delete that. And he asked me the same thing for average. Now, if it doesn't let you because say, for example, with is a field main called average. Just make sure you put a space in here are cake and then press okay. And that will fix the problem now, right? Click. And so feel, Miss. You can see that that has bean changed their 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 county, the average and so forth. Okay? 7. Show Values As: Year to Date & Variance: What we'll do is let's put in a another people table, insert a pivot table, existing work shaped. Let's go to our location and putting that and press. OK, OK, so we credit another pivot table. So in here, let's drop in our cells month in the rare labels and the financial year in big column labels making bigger. Okay, so the other thing that wouldn't do now is putting out sales in there. Okay, so let's just Mexico's medic changes, which I like, and there we can get going. Okay, here we are. Let's get rid up the grand Total B Just clicking there. Rightly removed grand title. All right, so this spring out field list in here and just movies I've been in here, so Okay, so doing that delicious. Would you just keep it there? That's fine. Make use a little bit smaller. Okay? So you can all see that. All right, so this go back into a pivot table to activate our field. This So when India now, they was gonna put in there a another metric, which is called the dollar up. A running total in or a year to date to do this, you gotta grab their sales and drop it into your values area. Okay. From in here, click Vela Fuel savings. Now, instead of summarised the values by which is what I showed you guys before, it's gonna show values as and from in here, you have on a write off different calculations. Okay, so I'm gonna go through every one of them. Now I'll go through. I'd show you a couple off different ones. OK, now let's go to the running total in. Okay, Running total in. So running Colleen means you to date. Okay. So to give us the total per month noodle accumulated, previous mountain then added on and give us a year to date. I can't. Just simple terms now, the base field. We want to make the base field being the month, because that's what we're going to increments. Okay, so within the show, values as the running total mean on a monthly basis. OK, that's how you read this press. OK? All right. So let's get out of these here. Now. What I'm gonna do is just so 2012. Okay, so right. Please. Number format. All right. Here we are. So what? It does is it? Add 771,000 January. Then it adds a 67 in here. Okay, so those two, they're the sum is 1638 Now, if you can't see it is here. Right. Click and make sure that your son is activated. This is a cool tip to to use all the time. You know, the average account, some maximum. So when it reaches a selection off sells and gives you some quick metrics. Okay, So show says here it's 1638 which is in there. Okay. Jan. 2 March 2.4 million. Which is that you can say it increments all the way to the title. But let's activate airfield this again. What I'm gonna do now is just a drop in the Yeah, okay. I'm gonna drop into the year. First of all, let's clear the field for the years will show everything. Grab the here on dropping to the road labels. OK, so we just have the years and months going in there. Okay. So you can see we've done that. It does the same thing for each year. Starts in the media need adds the year to date for that particular year, which is you can move around and still has that metric. They Now I'm gonna add in another one, which is the difference from the previous month. Okay, so once again, Greta 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 Arrow Valley fuel settings. So valleys as now, we're going to show the difference from the previous month. Now drop down box there on gay down t difference from. Okay, now the base I tempt previous and the base field month special. This occlude These are correct. OK, so what? It says he is going to show us the values as the difference from the previous month. That's are you ready? Difference from previous month and pressed. Okay. All right. So let me just format this a little bit better. Get rid of that. Okay, number format. And I love putting ST minus read for the negatives. Okay, just stands out. So what it gives us is just a difference between the previous month. So the division between January Fed you can see it was an increase off 95,000. And then there was a decrease off 83,000 from Feb to March and then march to April. There's an increase of 24 saw one and someone. Okay, so you can see the difference from the previous month. So you got all these different metrics and you got hate small. This is just the tip of the iceberg. Now, in my extreme, people have. Of course, I go through every single item site, every single item that you see in here. Okay? All these are going through every single one. Okay, so you can see the power within a pivot table and the identities formulas you don't ever. These formulas, he just gotta choose the right buttons on. Then you have it there. And if you're information against updated in your diner source, we another 100,000 rows or you gotta do is go back in here, right? Click, refresh, and he gets updated. Imagine that power that you can show that to your boss, take a screen show and say, Hey, this is how business is going. This is how our products trending on. And you can make something Saiful Business decisions on. 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 just let it out. So you just show you this. You become a mass than you would imagine. If you learn every single different items you could do with the pivot table, then you be doing courses like myself. 8. Grouping Your Data: grouping. Okay, so now we can grew our pivot table, and I show you three different ways How we can do that. One of them is grouping numbers to the other one is grouping the date and no. So you weaken also group by text. Okay, so what I'm gonna do now is just drop in, okay? Not that type. Clicking their insert a pivot table. Let's go to our grouping area here. Let's go into the numbers. All right? And when he met. So we're gonna drop in Here. Are the sales in the row labels OK, Something that's not usually die. Silas. Drop it in there. And he shows us all the unique sales amounts. Okay, good. Hips that. Okay. And we could just click in there, right click. And she's group, okay. And ultimately chooses would have stopped would end by the minimum maximum out. And these are the increments. 10,000. And we get putting 100,000. No weekend putting 20,000 whatever you want. It's just use 10,000 and press OK. And with that, he gives us the cells. Rangers. Okay, so now what we can do is grab the sales and drop it in here. Okay, Now it gives us the counter sales. So he just counts How many transactions are between zero and 9099 then so on. Okay, now, this has been sales in here again, and instead, off having a count, let's choose the some. Okay, so we have the some in there, so we have the some anti count. So, for example, from the foot of south range 90,000 99,000. So between nine thousands and say 100,000. So the sales individual cells valued between that range, we had sales off $5.8 million. That's what it's saying that. And we had 60 to transition between that sells rains. Okay, if you're not, go on group it. Right. Click and just press on group. Okay. Now, date fields. What did these going here on? Just create a quick people table again. He takes us a few seconds. OK, drop it in here. Now. What I'm doing here is dropping the order date in the road. Labels ordered eight row labels, so it gives us the unique order dates. Okay. All right. So from in here, you just right click and groups. Okay, Because it's dates. Excel is smart enough and he gives up the grouping by the dates. Okay, so we could group by days. If it's in blue, it means you selected and click again to answer left. Okay, So we can select in court is okay, good the starting and inundate these automatic Just live like that quarters and press, OK, you can see it's done in corners. This price controls it to go back and then right, click and group. Let's to buy days. Okay? When we choose days, it gives us a number of days option scroll about. So let's choose seven. Okay, so one week, this group by weeks and press OK, so you conclude by weeks and now we've got that we can put in their sales. It's OK. How much silence that we might for that week go like that. Now we're getting account of sales to the reason why we get an account of sales. A couple of races. One is that you may have your sales color a blank. So if you have at least one blank cell, you're gonna get a count off sales. Another way is that let's go back in here is because once we group values and then you create another people double what we have now, it gives you a account. Okay? So don't freak out if you get a count. Are you gonna do is just quick in here, okay? And then just put in and double click some. Okay, so we have the different cells, values that the total sales per week, so you can see that. Okay, the different cells per week. All right, now, finally, we can put in there another people table. Okay, You see that? Just the men. All right, Now, let's movies crossing here So you guys can say a little bit better now. We're put in the regions in the labels hand the years, you need common labels. OK, So And also doesn't forget sales into the values area. Okay, we get account because we have a group sales before. Okay, let's put him at are perfect. Okay, So what we can do now is we can actually group text, for example. Let's just say that organization changed on Americas and Europe is one region or one poll whatever you wanna call it and AIDS in Africa is just a different region, Paul. We can do is name these West, so highlight that right? Click and group. So automatically gives us a group name, Group one. Then he puts the values in Each is separate groups as well. So let's just or a bit obvious. Now you get this group on this change. That list changed that too west. Just type in the West. Okay? And in here, it's just Ha little that right click on group on. Name that East. So we have about two groups east and west. Okay, you can see here Region to has been created here. Okay, because we grouped text. What we can do now is looking actually, grab these. Move across. Okay. Just like that, When we can have East west on, we can see the different values that pertain to the West. You can see it. Any American Europe and East oni Asia and Africa. Okay, We could just move it back like that speaking group text as well 9. Inserting Slicers: So now we're gonna go on to the fun part, which I love slices and charts. Okay. Okay. So now we're gonna grab our text in him. OK, so it's just a group Text control copy going here. I just put it back. Okay. All right. So we have that in there, just get rid off the grand title, okay? Make this like that. Okay, So now Teoh, instead of slicer, first of all, slices What? They are a large buttons. I'll show you what has been selected your filters. So they are your visual filters. Now, if you clicking there, you have these different options that Andi So you just select a couple 2013 2014. Okay, It doesn't show you in here. What? Selected apart from in here, what's not selected? OK, but with a slice out, you can actually see the selections made and you see what has been selected. Okay, so let's just clear that now. Tweet. Consider slicer clicking a pivot table, ready options and insert slicer. Okay, here we are. So it gives you the least off 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? You can be outside of your people tape. So in here, what I'm gonna do now is poured in the cells person slicer, catches something that's not even just putting them in ourselves. Person and prince. Okay. All right. So here we have one of our slices. Okay. You can actually from in here just changed the star. Okay, If you click out out of a clicking toe, you could see the slice of tools option. Okay. So you can actually choose different colors in man. All right. Okay. So which is Lima? Ian Wright. Some include as Mark Jackson. You can see that information gets updated accordingly. OK, so we have one slice. Go and drop in a another pivot table. Let's go back to create one. You know, dinosaurs insert a pivot table and lets go where existing work. Shit in here. Okay, Press like I All right. So let's create that. This moving here. Let's putting out outsiders in here, Okay? So because it was group, 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. They all right? Perfect. So we have our second pivot table. So what we can do now is drop in a slice of from in here. So we're in the pivot table options in such slicer. Let's go in to the months. Okay, so we have the different months. You can see there, OK? And that changes that. All right, We choose a month, and we don't say grab it and scroll down, OK? Open or suppress control. Kate and select and I select a couple. We use a shift key as well to select multiple attempts. Okay, so we have our slicer, so we have two slices. Okay, One for first Peter table put up there. And we have this other one for the second people table now. Just nothing you can see here. Once you click in the slicer under slice of tools columns, you can actually put into three different columns. Okay. It's a cool trick so they can have it into quarters. That Okay, unless change it around, be kind. Might be more fun. OK, 10. Inserting Pivot Charts: katzenell again insert a pivot chart. Let's go to options. So once you're in the people table good options and give it a shot. OK? Saying he'll issues a column. Okay. And press that so he gives us our pivot chart now, right? Click in the bud and they just hide or fuel buttons on child. Just get rid of that because that's a nine. All right, so let's just put that in here. Well, we just just one now, okay? Just to make a visible to all of you into premiums just for now. Okay? So as that changes the pivot table changes which has committed the pivot are on that changes as well. Okay. And then right top right hand corner. Just clear it so I can have all the selections there. Okay, so there's a pivot chart connected to pivot Table, which is connected to this life site. It's like a big, happy family. OK, now let's put in I another pivot chart for this pivot table here that we created. So options pivot chart and let's choose, I press. Okay. All right. Once again, let us get rid of the buttons there, OK? You can just make this whatever you like, but you just get rid of it for now, OK? And then what I'm gonna do is just place it on. Just leave it here for now, OK? So with now, sickest life. So we just connected to the second pivot table. OK, here. Let's just make it like that. Okay? Better still, listen, moving in here. All right? So we have our second slice our house again. Okay. In here. Justice separated because it meant you're confusing. All right, so this side here is a different people. Table difference, Liza. And different picture. Okay, so if we change that, you can see that the amounts changing people table when I say your child. Okay, so it's pretty cool, all right? 11. Creating a Dashboard with Slicers & Pivot Charts: weaken. 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 that piva tables and the pivot charts. Okay? Actually have duties already. Is good getting there. Now go on to this slice of right leek and cheese. Pivot table connections in here. Okay, We're going to choose. There's a lot of different people tables, but what we want to connect is I want to connect this slicer okay to this letter in here. So to do these, just click in the second slide. That second pivot table with over connecting because I want to get the name. So options people have 11. Okay, lets people have bolivar. And this is pivotal. Night. So pivot table nine and 11 have to be a married. So once again, right clicking a slicer. Okay, people, table nine is chosen. Let's choose. People have 11 let connect leased to pivot tables. So, by connecting these to pivot tables were connecting the slices and the pivot charts. Okay, The same thing in here for the second people tell when we created rightly people travel connections, let's connect it to pivot table number nine, which is on the left hand side. Okay. See what happens now as a change. One slice at both charts change and both give it was changed. See that? As I change the other slicer, both pivot charts changes. And now from this, we can create a cool dashboard to do that, select the slices control. Hold on. Control key. Select the give a chance. Control X. Let's get about dashboard area. Okay? And Chris, control V and escape. Okay, so now we have our cool little desk. Paul, eat here. We've out to different. It's just putting that pivot charts. 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 you, man. We can also go to analyze. Okay, So in the future, we can go to analyze inserts lifestyle. Let's assert the year. Okay, so this put in the year in here. Okay, let's make these another color. So from a pivot, Chaikin actually putting the slicer, we could have done it the other way. We could've gone into our people tables, You know, I just showed you another way. All right, so let's right. Click these new slice on and let's connect that to pivot table number nine. Okay, Just make sure it's always connected. So 2011 2030. Sorry. 2012 2013 2014. Look at that. And their title changes. Now, we credit a quick dashboard, and I am sure that your boss is gonna fall off his for hurts a and fall in love with you and give you a pair ice if they don't come to me and I'll speak. All right. So this is how easy it is to create a slice of a pivot chart and connect them together and do a call passport because your daddy gets updated for you to do is just right clicking your people tables. Refresh on the information in here. Get up there as well. Okay, let's finish up with some called tips. 12. BONUS: Pivot Table Tips!: Now what? I'm gonna do these Grab. Okay, Just a patrol copy. There. Just grab these and put it in here. Okay? Maybe not let us go and grab. Maybe this one here. It's a little smaller. Control Copy in there. All right, let's do that. Just to show you a case of huge keeps in here. Okay? Sometimes you get into cells. Now, in here, we don't have any of your cells, but say you do get empty cells. Now, if you want to show a value for I in yourself, you got a click in your pivot table. Good options on options. And in here lay out a four, man, you can say for in the cells shot, you can put in their zero. Okay, So instead of being empty putting that you zero Okay, now another thing. Use that. So, you know, you make this a little bit bigger, okay? Make it pretty. And then you're right. Click and you refreshed. It moves back in. That's annoying, isn't Well, I hate that. So again, Options and options and order feet column with an update. We don't want that. We don't want to vote if you want to keep it the way these So? So you want to make it bigger and pretty And just keep it like that and right click and say you're official data. Kids like that. That's a cool T. Another thing is OK, say that someone out has been updating your died of sorts. Okay, Away down here. And they just keep on updating out. And then you open these workbook and they you don't know that someone has gone in there and up there information on bond and that new information is not being reflected, Okay, because it hasn't been refreshed because he didn't put him that someone else. How you need to know. So a good tip to now years you can actually, every time you have in your workbook, the pivot tables can refresh automatically. Okay, so you don't have to go in there and refresh all the time. So I actually have devious again options and options, options and options and data. And the third option. Here's refreshed Dara. When opening the file, click that So the next time you have in your your file Okay, you get a little message here. It's fine. The next time you'll be a file. Your pivotal will be refreshed. That's one less thing that you need todo Okay in here. What's Jude next month? Okay, so what we'll do now is list putting that another people table. It's going to a grouping out date filter here to select and talk every table. OK, Unless just put it e here. Just make a little bigger. Maybe not that big. Okay? All right. Yeah. So we have now dating here, and we just write leak and just Well, get on group for now, okay? Yeah, honestly, was due next month. So I from the drop down box, you get the date field test. You can actually see here next month. This month. Last month, the least next chord on Nexia. So it's so good next month. Okay, so it gives you hear the dates they're 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. OK, Now, when you come in next month and I've been this table automatically refresh it for the following month, so you don't have to change anything you have on a raid off different options that you go through that as well. Now, another tip here, okay, is top 10 orders. Okay, so what? I'm gonna do these. I'm gonna grab these just quickly. They Okay, so now cooking that right leg. So few list. So instead of order Day, let's put in their hour a case with Channel Partners. Okay? We have our sales there, so China ponies ourselves. Let's go. Sort of top 10 orders. We can say at top 10 cells, people okay or sort of channel Panis from in here and get value filters. And because he knows that we have values meant no dates, but I give us the value filters top 10. Okay, So top 10 in here we can t top or bottom, we can do 5 10 or 1/100 issues. 10. And then he admits, Just choose the item for now, OK? You just leave it as he is in prison. OK, so it gives us our top 10. We can actually write taking man. And so, from largest to smallest case, we have the top channel panna on the first row, and then they tend to down man. Okay, so it gives us that. It's fantastic. Top 10. Nothing is. You go to conditional formatting. So conditional. Former And let's make dad about here, which is me in Excel 2010 and let's make it. Let's choose that. So what's the voice we choose? That we can actually have Teoh select the third option. So the third option means it's going to continue format all the values except the totals or subtitles. Okay, so you can see that we have the green being on top on the red being on the bottom. But we can actually going there, just change that around. We can actually put the red on top, okay? And then the green. But okay. So you can actually apply conditional for Matic. And I've got a whole chapter on my extreme with her because un conditional formatting with pivot tables now final did one that I love and one that you're gonna get a lot off benefit from. Okay, let's put in a new pivot table. Banana You notice off by, huh? Okay, So we're putting out sales person in here, and then we're gonna putting sorry itself. Person in the port fuel top one second. Just make this. Okay, one second. Again. Okay, that's fine. The reason why that happens because the report filter is gonna go on to my We're going to my text here. So what it says is gonna replace the contents of this nation sells? That's fine. Let's just say yes. Okay. You see that to get rid of it, Okay, It's pretty. The month, okay? And let's put in the sale skin here, okay? Careless preen like that. All right, so and it's been a year in there. All right, So we have our cells per month and also yeah, for each sales person and say we have Look, we have forced house people in here. Okay? On, You know, you correct these cool looking report on and you know you make you cannot even put different metrics if you like. Now. So you want to copy what you've done here? These people have been here with your special metrics with your special conditional format . So you wanna copy that for each different cells person? Well, you can do that. Okay, easy. Go to options on the options dropped out and then cheese show report filter pages. So the report filter is here because of the South Person. So show the report, feel the pages click on that and it gives you an option. Gives you the option off. Whatever feel least is in the report filter. At the moment, we just have one. So we're just gonna choose that when Chris, like, I have a look down here. What's gonna happen? All the names are gonna be created as a separate sheet with that separate values. Boom. Okay, let's go here. Have a look there. Look at that. We have Homer Simpson in right, John, Michael Lewis and Mark Jackson as the own. See with their own metrics. That's fantastic. So you can take that. You get a copy paste where you can print that on and you can save that page as well. On you could give that to your boss instead, off going one by one and doing that. This is quick and he's away. It's a great tip and one that you should definitely use. Okay, so that has bean briefly the pivot table. Webinar Enough, Sergio. The most important features. But there's lots more. There's lots more than you can do with this pivot table. On what we're seeing now is just going to my course. I'll show you the different pots that with unite people type.