The Complete Excel Course Pivot Tables, Charts & Dashboards | Chris Benjamin | Skillshare

The Complete Excel Course Pivot Tables, Charts & Dashboards

Chris Benjamin, Instructor, MBA and CFO

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
12 Lessons (1h 23m)
    • 1. Course and Instructor Introduction

    • 2. Excel Basics

    • 3. Introduction To The Data Set

    • 4. Basic First Pivot Table

    • 5. Formatting Sorting and Fixing Data

    • 6. Value Field Settings

    • 7. Pie Chart Graph of Commissions

    • 8. Buyer Type Avg List Price

    • 9. Offer Status Type Pivot

    • 10. Dashboard Creation

    • 11. Dashboard Slicer

    • 12. Course Conclusion

28 students are watching this class

About This Class

Are You A Beginner to Intermediate Excel User?

Have You Heard Of Pivot Tables But Never Fully Understood How To Create Them?

Are You Not Even Sure What You Can Do With A Pivot Table?

Do You Want To Create Sleek Dashboards Using Big Data Sets Condensed Into Useful Reports?

If You Answered "Yes" To Any Of The Above, Look No Further.  This Is The Course For You!

Enroll today and join the 100,000+ successful students I have taught as a Top Rated instructor!

Three reasons to TAKE THIS COURSE right now:

  1. You get lifetime access to lectures, including all new lectures, assignments, quizzes and downloads

  2. You can ask me questions and see me respond to every single one of them thoroughly! 

  3. You will are being taught by a professional with a proven track record of success!

  4. Bonus reason: Udemy has a 30 day 100% money back guarantee if for some reason you don't enjoy the course!

Recent Review:

Matthew F. says: "Terrific course, pivot tables always seemed so confusing to me.  After this course I realized it's really not that hard at all to put one together.  So powerful, such a useful tool.  Creating charts and dashboards was pure bonus material for me.  Really recommend the course, Chris made learning fun and easy."

Why You Should Take This Course With Me:

Microsoft Excel is a very powerful tool when used to its fullest extent.  Pivot tables are one tool that is not one of the first things you learn in Excel, but should be in your backpack of tools you have available.  They are an amazing tool to quickly make sense of big data sets and analyze information from multiple different views, all very quickly.  While they may seem confusing at first, once you learn the in's and out's of pivot tables, you'll be a pivot table wizard. 

In this course we will start with a blank Excel workbook, open a sample large data set, and then demonstrate how to create multiple pivot tables as well as pivot charts.  From there we will explore all of the options you have with pivot tables by adding to them.  After that, we then create a dashboard that any manager would love!

What We Do In The Course:  

  • Learn Excel basics to start

  • Review a large data set

  • Build a basic pivot table to begin

  • Build multiple additional pivot tables to give us a selection of reports

  • Create charts using our pivot table data

  • Create a fully functional Dashboard with multiple graphs and selection abilities

  • And much more!

At any point if you have a question, please feel free to ask through the course forum, I'd be happy to answer any and all questions.  


About The Instructor

Chris Benjamin, MBA & CFO is a seasoned professional with over 20 years experience in accounting, finance, financial reporting, small business and business consulting.  Having spent the first 10 years of my career in corporate settings with both large and small companies, I learned a lot about the accounting process, managing accounting departments, financial reporting, external reporting to board of directors and the Securities and Exchange Commission, and working with external auditors.  

The following 10+ years I decided to go into CFO Consulting, working with growing companies and bringing CFO level experience to companies.  I help implement proper best business practices in accounting and finance, consult on implementation of accounting systems, implementing accounting procedures, while also still fulfilling the CFO roll for many of my clients which includes financial reporting, auditing, working with investors, financial analysis and much more.  

Thank you for signing up for this course. I look forward to being your instructor for this course and many more!

Chris Benjamin, Instructor, CFO & MBA


1. Course and Instructor Introduction: Hi, everyone. Welcome to the course. My name is Chris Benjamin. I'll be your instructor on this course. Thank you so much for signing up s when this course we're gonna be learning all about pivot tables and beyond were also going to be learning about pivot charts and then how to create a nice looking dashboard like the one you're looking at. So essentially, what we're going to do is start with a raw data set. Just this is the exact one used in the course. We're gonna take this raw data set. It's over 800 lines of data. It's multiple columns of two column are, um you know, I'm basically we're gonna take the role of you started a new job. You're giving this huge amount of data and you're kind of told make sense of this, create some useful reports, picked kind of the main areas you think would be useful for management and, uh, make something for us in excel. Ah, a lot of people, unless you're familiar, pivot tables would have a tough time without, like, where do you start with a data set like this? Well, with pivot tables, it becomes so much easier and so much more clear. So in the course, what we're gonna do is we're gonna create three different pivot tables and then also subsequently create nice charts that go with those now, beyond sort of the basic pivot tables that you're seeing here as I click through, we actually explore multiple different columns and weighs a slice in the data multiple different ways. We kind of just settled and and ah, finalized all of our reports based on sort of the top performers, kind of the best averages it set trap. But during the process, we're gonna learn, you know, the way of sort of exploring the data, trying different things, seeing what works, what doesn't work, You know what makes sense. There's a bit of trial and error involved. So you get to see how that works, will create these charts. And then at the end, we're gonna create a nice, slick looking dashboard. Ah, and it, you know, at most I mean, this took an hour just to get through, create all of this, so you can imagine what? What's possible with this? Uh oh. We got nice charts we have that are dynamic as we changed the filters over here, you'll be able to edit your graphs. Uh, let's see. Let's just to demonstrate no one didn't do too much. But you saw how that graph changed just by adding in different factors, etcetera. So we're gonna create nice, dynamic dashboard by the end of the course. Uh, that's for myself. My name is Chris. Benjamin being the instructor have been using cell for over 20 years. I'm a accounting and finance professional. I use excel pretty much every single day. And working with large data sets is something that I'm very much involved in. So, ah, wanted to take my knowledge and information all about you know how to do this and put it together in course format for you guys. Some, uh, so glad again, you signed up looking forward to being your instructor. Now, let's go ahead and get started on taking this big data set and making sense of it 2. Excel Basics: All right, everybody. So before we get started on the course, I want to give you an introduction to excel. If you're familiar with Microsoft Excel, you understand the different cells have input? Um, maybe had do formulas and, most important, here, familiar with this home ribbon, You know, the different formatting, etcetera. You can probably go ahead and skip this video, and then anything beyond what we cover in this video. If we do something during the course, I'll actually call it out and explain it at that point. So this is really geared towards people who, you know, maybe, you know, open an Excel workbook before but really don't understand, You know, all sort of the functionality in here. So if you are still with me, let's go ahead and it started. Welcome to Microsoft Excel. So first of all excels. A spreadsheet program is by Microsoft, operates very much similar to Google sheets. Eso If you use Google sheets, most of this will apply as well. You might just have to go find, uh, things in different locations. Uh, so each of these is considered a cell in the space of where we're gonna be entering data whether it's numbers, letters or formulas. Each cell has a cross reference, so you'll notice all the columns have a letter, and all the rows have a number. So any given cell has a cross reference. For example, this one is I 10 and you can tell that because it's slightly shaded and slightly shaded. Or you can also just look right here and see what is eso. As we click anywhere, you'll see that changes, uh, Microsoft Excel. So So these air considered workbooks, and they very much are like a book and that there's different sheets. So I have my default set up to when I create a new workbook. It just opens up with one sheet. Yours might be different. It might have had multiple sheets already, regardless of whether it did or not. You can add sheets by clicking the plus sign. So you know, if we're using multiple sheets in our course, you know we will need a few different sheets. Eso, anything we do on one sheet won't impact the other. So these really are like pages in a book. So this is like paid one page two, page three. It's a trap so most of functionally we're gonna be using again. It's on this home tab excels very powerful tool. You can do quite a bit with it, but for the purpose of this course, we really don't need to get to two involved. I just want to show you how to do basic sort of data entry, um, and some formatting and moving things around, etcetera. So the way we're going to start this is just going to create a short little sort of table, if you will. Let's assume it's a table of test scores. So I'm gonna write out some names. So, Chris Bob Oops. Keep typing around. Um, Julie and Mark. And then maybe we have test one Test two and test three. Now, the way moving around is on my keyboard. I'm just using the arrow keys. And simple is that whenever you're in a cell to start typing and it will enter, you'll see that whatever we type here is also up here. This is held the formula bar. So later on, when we actually create formulas, what you'll see here is you'll to see the result of the formula. You'll be able to actually see the formula itself up here. So, for example, if we typed, you know, equals four plus seven, it would show 11 in here, but it would actually show the plus four plus seven up here. All right, so for our test scores, let's go and put in some just dummy data. I'm just trying toe populate some information. Ah, just so we have something to work with a little bit unrealistic. So, um Well, God. So we have a nice little table here, so this would be one good use of excel. You know, maybe your teacher, and you want to keep track of test scores? Well, you know, trying to then apply what we're doing right now to what we're gonna be doing in the course . No, we typically want to form at this. So just presents a bit better. It's a little bit easier to look at it a little bit easier to understand. Um, so first things I want to do is kind of take care of this hetero, um, so we're gonna go across and use all the things here, but not necessarily in order, because something that makes sense to do later on. So, first things first to highlight, so anything we do. So if we're highlighting a cell and then we go and do something up here like we apply a bold or color, it will only do it to the cell That's highlighted. Now we can highlight multiple cells, and the way we do that is if you click on the first cell and then hold down your left click and now move your mouth, you can highlight and I'm just scrolling around. I saw my left click press down so it could scroll around Someone's gonna scroll to hear Let go And then those are all highlights now, anything we do up here will apply to all of these cells. Conversely, another way to do it is on your keyboard. If you hold down the shift key and then move your arrows around, that will also highlight. So this whole shift and when you're done, you just let go. So let's go up here, gonna hold down shift and there we go. Okay, guys. So I like the typically bold my header. So let's go whole head and play some bold. So this very first part here I should say, second part because we'll get to this. Um, is you can do bolding. Basically, it's the font itself so we could change the font style. Any time you see a drop down box like a little arrow, that means you can drop it down. You have multiple choices. Typically, I don't change the funds. The one thing we might do is change the font size. So maybe we make headers a little bit bigger, so they will make that a 12 just to make it stand out. You could use these as well, which is increased font size and decrease font size. And the other thing you'll notice, too, is as I hover over any of these items that tells you what it is. Just leave your mouth there for a second. It'll pop up and tell you what that actually does. So, um, so here we have. If you want to read, have bold italics and underlined, Um, one other thing I want to point out is you'll see it says bold. And then in brackets that says control be so if there's ever a keyboard shortcut, it'll tell you what that is as well. So, um, so we could click on the bold and you'll notice that bold everything We can click it again , un bold it or we can hold down our control. So I'm holding out control on my keyboard and pressing B. Oops. Gotta get out of that. There we go. So, control, be bold everything. Um, typically, I don't use to much italics or underlining will come back to this in a second. One thing I do like to do, though. So just kind of popping over here to alignment that we're gonna move back here. I like the center. So for headers, I want a center. My header. So Ah, here you have your alignments, the left center and right, So I'm gonna hit center. So you noticed those air all center now within their cells. You could, in dense of these air in dense and removing dense and these air vertical alignments, we will get too much an emergency center rap tax, most likely. And if we do, I'll just show you during the course again. This is meant to be very introductory for you guys. Just we conform at what it is that we work on during the course. So the other thing I like to do to make this stand out a few other things. When I like to put a line under these cell, it's not quite the same as underlined. So underlying underlines just the tax that's in any given cell. Uh, right here we have Borders and Borders will put a underline under the entire box. So that's what I want to do, something to go here. And if I click often now, you'll see. There's that. My solid line. Just a note again. There's an arrow, Siegen. Drop it down and there's lots of different borders. You could do the right, the left, the top boxes, etcetera. Everything I like to do is maybe shade it, Um, you know, so we could change our font color. I'm typically not a fan of that. I usually leave everything. This is black. If I want something to stand out, I will, though, shade the cell so again dropped that box down and you can highlight and you see it changing already. Over here is I scroll around so it's not permanently changed, like click on it. But it's kind of showing what it would look like. So maybe we want like a light green. So there we go. We got something nicely formatted. Okay, guys. So, um next let's say that this. Ah, So let's go over here to copy Paste. This is copy paste are cut and paste, etcetera in the format. Peter. So say we. This was semester one. And now we want to duplicate this first semester to you know, we have the same students. We have the same tests. We don't want to have to recreate this whole thing over here, since we have it right here. Well, just use our highlights. So hold down your left mouse and and ah, scroll till you cover it. You go here to copy and you'll notice. Copy is control. See? So we compress controls. Hold down. Control, press, see, Or just click on this. You'll see the jagged line outlines it. That means it's been copied. Something's happening with it. So it's just paste it right here. So go to where you want to put that copy and you can just hit the big paste button. And there we go. Now we have a duplicate of it. Easy. Is that much easier than having to recreate it? Okay, guys, So let's say I wanted to, um I wanted to copy this kind of the format of it, but I didn't really want to, um, copy of the data itself just because, you know, we're gonna have different students next semester. Obviously, the scores will be different. Um, you know, and maybe instead of test ABC, these were dates or something. So I like the format. I like all the time I spent, you know, making a green making and centered, etcetera. Um, one way we can just copy the format is if you get new highlight and take. Technically, all we really did was this. These four cells was all we did. Um, this little paintbrush here, it's called the format painter. So you highlight something you have formatted. So this click the format painter and then go where you want to pace that format. So there you go. So we have a green. We have our line. If I type something test six, it's gonna be centered. It's gonna be bold, its executive formatting. So it's a way of copying formatting without copying data. Um, cut works the exact same. So if we highlight something and go, the scissors. So instead of creating copy now, it will. If we had pays, just it'll cut it and move it where we went. All right, guys. Last. Ah, a few little things. So instead of test scores say these were actual dollar amounts. Say these were sales amounts. These were salespeople. We were keeping track of how much they sold of each product. Um, so we want to format these toe look more like dollars. You notice these? They're just straight numbers. So the way we can do that is highlight all these again. Then we have her numbers box. So we conform at in different ways. So the defaults air source that we should say the shortcut ones air the dollar sign of percentage, the comma. There's several other options. You can drop this box down, um, dates, fractions, etcetera. And then you can go to Mawr and have even more options. But for our purposes, you know what? We just want a dollar signs. So there we go. Perfect. Now let's say that we didn't really want the sense we don't need the two decimal points. Um, there's a quick way to get rid of that. If you leave it highlighted. Don't bother. On Highlight. Right here is the decimal increase and decrease. So if we click decrease once and twice, it gets rid of that decimal point and it will round off. It'll so those decimals air technically still there? Um, just they were all zeros because we put in whole numbers. But if there was no 0.56 and there, it would have rounded this number up. All right, guys, I believe that. Is it anything else that we cover in the course? Like I said, um, at that point in the course, I'm just going to show you how to do it there. So we might. So we'll do things like write formulas. We might do some sorting. We might do a little bit more formatting, but I encourage you at this point, if you if you're feeling with thick salary, feel comfortable this point, let's move on. If not, maybe create your own blank worksheet and type some data. Just test out. Just try some of these different formats, see what they look like. Increased the fonts, cut and paste it around. Change things the dollar signs make a list of dates. Um lots of cool stuff you can do in Excel. So that's it for this introduction. Let's get started on the course. 3. Introduction To The Data Set: All right, everyone. So now that we've gone over some basics and excel, you know, some formatting and sexual which we actually won't be doing too much of that we really just gonna focus on the pivot tables themselves. But I want to give you some tools beforehand. Just see not on navigating sell a little bit if you're not familiar with nonetheless. So ah, in this video, I want to introduce you to sort of the data set. We're gonna be using this Israel data. It's from over 10 years ago. It's kind of data, but that's not the point of it. I mean, it still works, obviously, for our purposes, Um, it's from a real estate companies. So they were basically tracking in Excel all their deal flow. Basically, you'll see things like, uh, offer status. So this is it. Each individual offer and what the status was. Ah, the date. The last name of the person the data when pending S. T I etcetera is there's quite a bit information here. Probably type neighborhood agent, the list price, the offer price, the final price, which would be the sale price if a sale to go through and then there's some additional information commissions. A big one will be doing some reporting on that and dates a mutual acceptance and buyer type . But such, right? So essentially, what we're going to be doing is pretending that we're and I'm just going to go down to the bottom here. Some parents, um, cells over there. I want to see where the bottom of this data set is. There we go. So 827. So so imagine. You know, you work for this company and you're given years your first day on the job. When you're given this data set, you know, your boss comes in say, Hey, now, here's all of our transactions for the last year, it looks like the spans quite a bit of time or at least a year. Saul seems to be 2006. So here's the data set for 2006. You know, we want to do some analysis, you know, create a sudden, nice looking report, an excel that we can update, and we can, you know, manipulate and look at our data and lots of different ways. How are we going to go about that? So, uh, you know you're given two days to do it. Well, what are you gonna dio? So first of all, uh, pivot tables, the way to go and pivot charts. So that's what we'll be focusing on in this course. So we're essentially going to play the role of yourself at your job. Here's your data set. What can we do with it? To make some pretty cool reports? Uh, so just some other notes on pivot tables in order to make nice pivot tables. And obviously, we're going to get into the specifics and kind of step by step. So first thing, you need a big data set which we have here, so no, it wouldn't make sense to make a pivot table on three lines of data. Ah, you know, we have 800 plus lines, So this works. You definitely need it to be in sort of, um, an organized fashion like this is with different column headings. Um, you know most. I mean, there's a lot of blank fields in here just because of the nature of what they're doing. Like they're not gonna have a pending date. When it was just an offer submitted, etcetera. So, um that's okay, but you want to make sure it's a complete data set in as much as it can be. Um, you don't want a lot of blank rows at the bottom, so just going to the bottom again. So when we select our day to create the pivot table, we're gonna make sure it just selects from here upwards. And aside from that guys, that's it. I mean, you need some uniformity as well. If you can't have offer status and have, you know, lots of different things are even more important and say it a list price, you couldn't have some of them be dollar amounts. And some of them be, you know, words and comments on things that's not going to come out the best for our pivot tables. You want some uniformity? And, um, what is included in them in the dates you want? Clean data, I guess, is the is the best way of saying that aside from that where you're good, So what we do when we get to creating our pivot table will start in the next lecture actually doing the pivot table. It's want to introduce you to the data here. Ah, we're gonna create multiple sheets for our different pivot tables. Each one will sort of be focused on one report Where, um you know, basically, we're just gonna to a degree freestyle and figure out what? What would be important? So our boss hasn't give us any direction? They said, give us, you know, some cool reports. Um, you know what would be most useful for us? You know, So they're mainly gonna be interested in things obviously related $2. So we're probably going to create a commission report by agent. I think that's one that seems to make sense. Ah, we also might look at, um, final prices or maybe even list. Let's look at list. Prices may be average list price by neighborhood. That might be a interesting report and already sees some data. That's, you know, like this is lower case. This is upper case, so I would be curious and some don't have it. So I'll be curious to see Ah yeah, there's a few with blank. So I'll be curious to see like how it comes out and we can deal with fixing issues as we go . Um, we're not going to too many reports based on date like we're not gonna There's not too much we can calculate, you know? You know, ultimately, when we're doing the pivot table, we're trying to calculate something, so sums averages counts. I mean, we could do counts, you know, on different dates, like how many houses closed on in December and January, etcetera. Um, we could do types of buyer types, and we could do a count on those. So, like, how many were first time? How many were experience looks in then Investors so might do account on that, etcetera. So we'll come up with some ideas as we go see how they come out And if it's actually useful , like we'll know once we create the report, if it seems like a useful information ah, that we would we want to present to management once we create all of our different pivot tables will then create a dashboard sort of a one stop location for them, where we will combine all those charts into one master dashboard, and then we're going to give them some functionality where they can choose different things then. So maybe they want to filter out by agent so they want to see all the different reports filtered by a specific agent or a specific neighborhood, etcetera, some. It should be pretty cool. You're gonna like what we come up with. That said, Let's end this lecture and the next one let's get started on creating our first pivot table . 4. Basic First Pivot Table: Okay, everyone. So let's get started. Let's create our first pivot table and we'll kind of take the 1st 1 slow. And then I'll maybe introduce you to somewhere. Function the features as we move on. So first of all I think we need to do is select our data. So, um, you can pre selected. Or you can have excel kind of guess what data you want to use now, if you have it in a nice, tabular format like we do, it should do a pretty good job. So let's see what Excel does with us. So first of all, where you go to create a pivot table, it's under your insert. Now, if you're using a different version, Excel. I know if you're using the Mac version that's under different menu, so you're gonna have to look. But it should be a pretty prominent item once you find it. So it's in one of the ribbons and these air called ribbons. Each of these your home insert page layout. It's in one of the ribbons on the Windows version. It's under insert some. We're gonna start with pivot table, and it's gonna ask us a few questions So first of all, choose the data that you want to analyze. So it it already guessed it's you know, we were already we had our mouths clicked within the data set. It recognizes we have a nice kind of, um, squared off, if you will. Data set. So it guess, But let's double check. So let's see here. It went from a one, which would be the roof for cell down there are 8 27 which is all the way over here. If we want a double double check, we can click this up arrow. It'll sort of reduce this, and it allows us to select the data. So basically, we get start now. It looks like it did a perfect job. It went to the bottom and let's just move this over bid if I couldn't grow, might not let me right now because we're in the middle of this. But basically, it started right here and went all the way across, which is the bottom of her data and up to a one. So it did a good job. I'm just gonna re selected here since we're almost there. Perfect. Now, if we drop the arrow down will bring back all our selections. Just a za note. You could link it to an external data source. So maybe you want to create a pivot table in one worksheet that's linked to a different worksheet. Um, obviously, it's a little bit cleaner, if you will, if your data and your pivot tables are all in the same workbook, but they're certainly circumstances where you might need to. Maybe it's somebody else's data set, and they just want to give you read only access to it. And, um, they don't want to be able to download it. Nothing. So you just have to kind of linked to it. For that reason just to create this, that would be one reason so, second question here, choose where you want the pivot table to be report or hot where the pivot table report to be placed. Your best bet will almost always be a new work sheep. So if you put it in the existing worksheet, uh, it's gonna trust, So it's basically gonna add to this data table a pivot table as well. Now, this data set is obviously fairly large. The last thing we want to be doing is navigating around trying to find a pivot table. So, um, I would I would Let's put it this way. I've never had a reason to not put it in a new worksheet. Um, where you might want to put. Um, actually, we won't even get into a firm nine times out of 10. You're gonna want a new work. She def. Not 99 out of 100. Um, and then there's additional, um, information. Or choose whether you want to analyze. Multiple tables were not going to get into that, um, for this lesson. So basically the defaults and pick the right, you know, data, new worksheet we can hit. Okay, so you're going to see what it did. It created a So here's our original data. It's called transaction detail. It created a new sheet for us. So looking at this, this is the start of our pivot table. So, you know, again, the purpose of have a table is to take data inert in our master data set and start consolidating it and making meaningful reports out of it. So, you know, imagine somebody had told you. Hey, I want you to give me a total list price amounts by offer status. Oh, and you didn't know how to do pivot tables? Well, you'd have to mainly. So you'd have to sort this, you'd have to break it out. You'd have to do the totals for each type, and then you'd have to kind of then probably just go and type that in on a different page. Or you could link a formula, but it would be a little bit of work. Let's put it that way. So that's probably not your best airport. Don't think too many people care about that, but maybe what people? So we talked about a few examples, Um, you know, commissions by agents. Let's take a look at that. How would we do that without, you know, sort of manly doing something. So with our pivot table, So here's where our data is gonna show up is over here and here. The pivot table fields is where we get to select what we want to do. So, um, the best way to do this is to start building one, and I'll show you along the way. And then I'll introduce you and kind of tell you a little bit more about how to do it. So first things first click in the pivot table, which worry are if you click out, you'll notice the pivot fields disappear. So just click back in this box and even says to build a report, choose fields from pivot table lists. Perfect. So here you'll notice Because our data was in a very nice, you know, table format with headers, which is basically what you needed to be. The first thing is what fields you want include. So each of these is a column in our ah, in our original data. Then basically, we're gonna tell Excel. Okay, we're gonna pick and choose some of these, and we're gonna basically tell it where to put them. So you know what am I visualized? I'm visualizing a report that has each agents name and has the commission amount right next to them, right? Fairly simple. So in our rose, we're gonna want our agents name, and the value that we want to add up will be the commission's. Now, we're not too worried about columns at or filters. And I will introduce you to those probably in the next video. Um, I do try to keep the videos to 5 to 7 minutes. So, um, let's go ahead and just do this one really quick. So let's see, where's our agents? So if you you can do two things, you could just click the box and excel. Well, guess what you want to do. And it guessed, right. I put it in Rose. If you want to as well, you can actually drag it and put it where you want. So I'm just gonna drag it here. Now we want commission, so commission amount again, we can click it. And hey, it figured out Exactly. We want the values. We want a sum of commission. You'll see that here. So, congratulations. That's your very first pivot table. Now, um, one thing when you do pivot tables, there's kind of way around this. It just doesn't format. It just puts it in as, like, Rod Date, if you will are you know, just general format. So you have to format thes. So the quick way to do that, you know well, is highlight. This row were you hit the dollar sign and there we go. Now I'm gonna hit save. It's a fidelity issues, so I've already saved my data set out. Um, so I obviously encourage you whatever data set your working and save it somewhere where you're gonna remember it, Give a meaningful name, etcetera. So, guys, I'm gonna end this lecture. Now. We come back, we're going to do some more work on this pivot table. You know, it's a great for a start. Hey, we have each are agents what their commissions were. Let's make a bit more sense of this and make it a little bit more useful. 5. Formatting Sorting and Fixing Data: Okay, so we have the starts of pivot table now again. I mean, this alone is Did what would have taken us a little bit of time to manually do what had to manually probably sort this original data list by agent, then break them out, create a sum for each one. We could edit filters. There's a few ways around it. Ah, but it would take a bit of manipulation, whereas this was quickly to dragon drops, not even two clicks on the button. And we have the information now. This is useful, but let's do somewhere things with this. So first of all, let's sort this, um, you know, it makes you know, right now it's sorted alphabetically, and that's fine, but let's sorted from highest to lowest. So if you right click in here like anywhere in Excel, you're gonna be giving options what you could do with where you're at and excel. And one thing we want to do is sorted, and I'm gonna go largest to smallest. I want the top seller on top. We have to reformat again. That's just one sort of nuisance with pivot tables, unfortunately, but it's obviously a quick fix just, uh, give it the dollar sign. Gonna go? Didn't save it. Had unchecked that just were not bugged with that each time. So we say a grand total of commissions for that 2006 period as 1.1 million. And now we have all of her performers. And then we have Ah, so these. It's pulling every category, if you will. So everywhere where there's an agent, let's see right here it's pulling these data fields, and now it's consolidating them. So a few things about this guy's If, um see if you think so. We have a few people, have zeros and let's find they might just ah, actually have zeros. Um, I see one that says blank and it has a value. So blank means literally blanket doesn't mean somebody typed in the word blanket means, um, there literally is no value. So for those and now, thinking again, going back to our example, you know, we're assuming we're given this data set, and we have to create a cool report for our boss inmates for the CEO. You know, he's probably gonna ask a question. What blank? What's that? You know, why is that they're so we want to do something with that. So first thing I want to do is I'm gonna go back to my data set, and I am gonna actually filter this by agents. So and then we'll pick just the blank. So if you're not familiar, just a reminder. So if you want to apply a filter to something so we could do this few ways, we could just sort this and then just, like, go to the section, that's all blanks. I mean, that would work, right? I mean, let's just do that really quick. I'll show you what I would do. Highlight everything by clicking on the top corner. Um sort, sort, custom sort. We're going to do it by our data's headers or Dubai agent. And there we go. So we could scroll down. It's probably gonna be at the bottom. I'm going to guess. Okay, so these are all wearing Agent is blank. So what happened here? So we see a lot of the offer. Status is often reject it, um, you know, by refused to sign what else? How far up does that go? There's uncanny arised, so that might be meaningful to this company. Maybe they have a type of agent where it's just done online or something. They don't use it, so I'm not gonna mess with that. But what I am concerned about is all these blanks. Now remember, we did commission. So in theory, anything that doesn't have an agent is gonna have a commission. But we do have some. So that's where that total over here that says Blank. It means the agent is blank, but they're still dollar signs that it's a quite amount, you know, that's a decent amount of 117,000. So we need to do something with these. I feel like guys and I'll all I feel like we should do and see. It was quite a few of them is Let's some put in, Ah, a placeholder, if you will. And let's call it no agent listed. That doesn't really corrupt the date at all. It's very true. It's just gonna look a little bit nice on our presentation when you know, when we give this to management. Now again, I'm not gonna monkey with an categorized if anything. If this was was really life, I'd probably go to my boss and just say, um if agent is on categorized, what did that mean? Should I just leave that? Should I write? I should like, should that also be no agent listed? Um, for our intents, purposes just gonna leave. It is on categorized. Doesn't look like there's too many of them. And I'm not even sure if Yeah, they all seem to have commission. So we're just gonna leave those someone hit Save. Now, if we come back over here, we see this hasn't changed yet. So if we click on here and go back to our ah, our pivot table here, there should be a refresh button. I'm not sure. Let's see where it is. Let me right click. There's refresh under the right click. It's also somewhere in the menu, but I'm not seeing it on on here. Nonetheless, if we do the right click and hit refresh, you'll see those change to know. Agent, list it again. Guys, we're gonna have to do the formatting. Unfortunately, no problem. I mean, you could certainly leave the four main toe last if that kind of bugs you. Ah, that would work as well. So we straightened that out. Let's see So we have name, name names. We have that on categorized category. We're leaving that no agent listed. That's fine. Then we have some names. Looks like there was some double. You know where maybe two agents worked on it again. If that's how this company wants to categorize these, so be it. And then you have a few people that, um, have none. That's fine. Alright, guys. So this video is up to five insulation. We have cut this one off, and then when we come back, I'm going to show you, um, additional field. We can add to this that will actually be fairly relevant and useful as well. 6. Value Field Settings: All right, guys. So, um, what would also be useful? I'm thinking would be, um you know, if I was looking at this data, I want to see kind of the total contribution. Like I know the 1.1 is the total. Obviously, Howard here, you know, Or that's what these air last things obviously, um, was 233 That's about 25%. But I really want to see the actual stats. Like, how much did each person contribute to the total, and this will be something new. So, um, so actually, before, before we do that, I'm going to say one other thing. Guys, So going back. So once we click in our paper table, we get our fields up here. So we did a sum of commissions. Let's say we just want it. Account of commissions. How many, you know, deals. Did each person do that where they earned a commission. So you'll see here where it says values and says some Now, if you drop that down, we can remove it, we can move it around, but we want value field settings. So what value field settings is going to tell us or what it gives us the option to do, and it's very powerful. Is, um we had to choose what we're doing. That the data. So we're saying, Hey, go find all the commissions. You know, the defaults kind of some. That makes sense, right? Add up all the commission's well, we could also discount them, like give us a count. How many commissions did each person have? Ah, just to go over some of the other ones here. They could do an average that bees. So what's the average commission these people are bringing in Just because Howard, you know, has the biggest total. Maybe her average, though, is way less than say, came s. And actually, Kim s is doing better. Delicious didn't do enough, but her average overall is better. How we could look at the max like, what's the maximum out? Each person did the minimum byproduct. Then there's some other Sterritt said. Then we get into some serious statistical stuff, so we're not gonna get that deep. But I think count would be interesting. So let's give that a try. So here's how many deals each one said. And now, um, so actually, we do see Howard did a few deals less than Kim. But Howard had a bigger total, if you remember. So, um, remember, did it re sorted this list? Howard was on top dollar wise. So that means Kim does more deals, but obviously lower value, actually, because, um, her total was lower. So more deals, but lower total means lower overall average. Ah. Then we have all these people, that one. These people did nothing. So this might be something useful as well. Um, I think let's go back to our are some? No, um, like I said actual. Let's take a quick look at average just for fun, just to see what happens again. Let's give this the format. So the average commission, the earning Soma Gardi who, if you remember, was way down. Now I'm assuming rob Majority is also majority. So again, that's a data thing they probably want to clean up. I would ask my boss, there might be a reason it's broken out. Probably not, but nonetheless just just inside, um, and now probably would fact the average as well. You see, there's one big average up here, and then a much smaller almost won the lower average lowest average is down here. So something considered. But unless we can see what the average commission each person is earning, so that's kind of reflects on how big of deals people are doing. Right? So we see Howard is only earning 4000 which isn't necessarily bad commission, but relative to some of the other people that were with Kim. See, now, Kim also are in sort of even lower. So, um, just something to keep in mind. Now, let's go back to our some though, because I want to show you something else. We're gonna add some. We're gonna perform at this, guys. Okay, so I want to see how much each person contributed. So what we're going to do so you can add the same you know, Roe or sorry, column information fueled multiple times if you want. So So we want to do something else with commission, except for some. So we're gonna click here now. This time we have to drag it. Since we already have it clicked. We have to drag it down here now again. It did the some. If you see, these are about the same. But we're gonna make this one a little bit different. So if we go down here and we choose that 2nd 1 and go value field settings again now, um so we have all the same things to count. So if we wanted to, we could actually put count right next to it. We could format this by dollars. So all those things we did where we kept choosing different ones, we could put the sum, the count, the average right next to each other. That might be useful, but something else you could do. And this is what I actually want to get to showing you bring up that value field settings again. Now, if you see here summarised values by some count, etcetera show values that if you click on this and can't really quite clear, see it. But it's zone tab of information. So show values AL out as, and it's currently has no calculation. But one thing I like is you could do percentage a grand Total percentage column total present a row, etcetera. So I want to see a percentage of column total. So whatever the column total is, it's going to show each of these as a percentage percentage column total. Don't worry about these. You see, They're great out we had. Okay, we're gonna format this again real quick, because that's what we dio and you'll see. Now, this is kind of wide, but I'm just gonna bring this in and showing us a percentage of the column total. Now something. Let's do this. Let's refresh it. I believe I'm trying to figure out how this person has a higher commission, but the percentage is lower. Let me see. If so, you can get also get the value field settings here, guys, it might be the rum. Maybe we want a percentage. A grand total. Let's see if that makes any sense. Um, field settings didn't seem like grand. Total changed anything. Percentage of column total. Refresh this. We're gonna highlight this. That's actually been perplexing. Guys. I'm not sure why if we have a total here by this person, has a bigger number, but this percentage isn't quite on the ball. So let's actually do some manual math just to see what's going on there. Guys. So, um, this divided by this would be the same percentage, right? And see yet should be ah, different numbers. Let's try Let's do that again as percentage of the grand total. Nothing not changing us. I'm not quite sure what's going on there. That's actually kind of perplexing. Well, I will check on that in the next video. I hate to throw away what we've done, so let me work on that and I'll report back in the next video. And then also, what we'll do is we'll create cool graph for this. So we get started on night making a nice graph, and then after that, we'll start making some additional sheets with reporting on different factors, if you will. 7. Pie Chart Graph of Commissions: all right, everyone. So, of course, the Suzanne did the video. I figured out what's happening. So Ah, my I caught this. So in this column, we actually have a count of commissions. So this percentage is based on the count total, not the sum total. Um, you know, I was mistakenly thinking it was coming off a here, but you have to realize it's just taking whatever this column is that happen to be account ? And if you remember, Kim had a few more sales or commissions, I should say than Howard, and that's why this is higher. So first thing. So let's click in here. Let's change this value field setting. And remember, there's two ways to do this. He could've right clicked over there. You can come here and do it. So let's change this to some. Ah, this will still be our percentage of column Total is the right actual designation. And now this looks much more right. And we can double check this if we divide this by the total 20.74 So that's what it was. Guys. So sorry about that Mistake is confused myself, obviously. So this is pretty useful information and you can see by being able to include the same column information multiple times. So we could actually so we can do We could actually included 1/3 time if we really wanted to. And then change one of these two ah value field settings. Change this one to the count. So there we go. Would have the some would have the count and then would have the percentage contribution of revenue to the total. Now, I mean, this is very useful information, but what I really want to get to is I just want a simple graph, Honestly. So this might be useful if you're really digging into commissions, but for our purposes, I just want a graph of commissions and we're gonna put that on our dashboard ultimately. So the grafts we're gonna create will be for a dashboards up. First thing I'm gonna do is I'm gonna get rid of these two columns because if we make a graph, it will automatically include everything that's included are pivot table Now The way to get rid of stuff is you can just come over here, click and drag, click and drag. All right, so with still on a pivot table if we go back to our insert ribbon and go to pivot chart is what you're looking for. So if you're gonna pivot chart, it's gonna come up with some pre defined one. So I mean, the column chart line chart doesn't really make sense. Columns. Not bad pie chart, which I'm actually leaning towards. So this is probably what will end up using, and I'm just coming over here, it kind of highlights. That makes it a bit bigger. Bar charts, area charts like this isn't bad, but I really want the piper. I think that visually represents because if you think about it, that represents, um the percentages, Wells. Because really, you know, however big this is, And if you remember, Howard was 20.74 and that's so that's what this pie slice represents. And then what we'll do is we'll actually show the dollar amounts on here as well. Let's choose pie chart and hit, OK, All right. So let's just move it up here. So we have Oliver agents, so I'm gonna make this just a bit bigger, first of all, and I'm gonna click. So if you've ever done General charts in Excel. You might be friendly to this, but you can click on pretty much any sort of item within the chart and do do things too. It's not like you can move it around. You can edit it. So I'm gonna call us that total commissions, uh, experience. And there Ah, let's move this. Let's say, like, just put this right up on top here. Ah, this. You know, we could make it try to make it a bit bigger. Action. That sure is gonna let me do that. Let me re position that, though. I know that much. Um, let's just move it back down here a little bit. Ah, I want to move this up because I noticed this isn't every single agent, so we'd have to expand this down here. All right, so quite a few. Now, um, let's see. So one thing it doesn't have is don't have the dollar amount. Select. Let's right. Click on this and go add data labels. So if you click on anything within the chart, you can. Then you know this drop down menu would be different depending on what it is to chose. So I'm gonna add data labels, uh, data labels. So I like this, guys, What I don't like is this. It's a little bit messy, right? Like we have every single little, um, you know, each of these people accept a fewer zeros if you recall. And even that it has, ah, zeros in there. So do we really want that some thinking? Well, you know, think. Think about our bosses What they really want? No, They probably care about all these little ones that I learned anything. Maybe they care about the top 10. So maybe let's change that. So instead of this being everybody, maybe Let's do so let's click on our chart If we've right. Right click again Ah, let's change this to Let's see, actually, no. Where would do it? My mistake. Over here on our data if we go again the value field settings. Ah, if you remember, let's see. No, I might be incorrect. Honestly, get out of here somewhere. Here, guys selected a format data. Siri's. There is a way to just choose our top 10% basically. So I'm gonna get rid of this. I'll get rid of this. It might be in here. Former, uh type. I am not sure where that sack guys. All right, well, for now, let's leave this. Um, this obviously is a mess. So what we'll do when we come back? Let's see. Yeah, we're up to six minutes. When we come back, Let me figure out that mystery seems like we have another little mystery on our hands. I'm just blanking on where it is, but there's a way where we could just choose to display the top 10. So what we'll do is we'll choose the top 10 in the next video. This will be cleaned up a bit and then we can go on. Let's create a second pivot table, come up with some ah, useful information for that. 8. Buyer Type Avg List Price: Okay, guys, I finally found it. And sorry about that. It's just did not remember being here. So it isn't our pivot tables right now, we have the full list of everybody, but we just want the top 10 for this graph. Um, if you come over here, you'll notice this little drop down. So we go to value filters and if you could. So if you do the drop down, come to value filters, we have several things we could filter on, but one of them, the one the defaults is top 10. Simple is That s a little branch. This box. Hey, Top 10 agents. You could make it. Eight. You could make it five. Now I'm noticing. So I was gonna default the top 10. But there's quite a few here, you know, little ones. Really? There's 123456 maybe seven. Maybe eight at best. Like good ones. And then it definitely gets slim. So maybe let's do the top 77 Seems like a nice ah, a number. Then there's definitely a drop off, or maybe even five. But I mean, five would be 12345 And be these five guys. Ah, I mean, whoever is green here? No, while greens, No agent list. I feel like we should definitely have that. So, uh, let's just do seven and see how it looks. LA cleaner Guys like the numbers air contained, for the most part, within their categories. It's very representative of almost all the commissions. If remember the totals only 1.1 something. Here we have one point. Oh, wait. Five. So we have the majority of the actual dollars of commissions represented here. Now, I would, um, changed my title. Here. Top total. Let's make this top seven commission commission earners. So there we go. So that way, somebody looking at this chart now knows these are the top seven and how much they earned in commission. Let's clean up the look here a little bit. So one. Now, this is a little bit spread out. We don't need this. Quite us. Ah, as long anymore. Since we only have seven people on here, A swell. Let's center this a little bit more. I want to center it more over the actual chart itself. All right? And now people can come here. They can reference this so perfect. So we have our first charts. Let's go ahead. Save this. And now I also want to rename this tab. I mean, we've kind of left it. Ah, she too for a good while. Um, the way you re named Tab, if you just double click on it. Honestly, guys, or you can right click. Um, just to show you that rename I can do things like move, etcetera. But for rename or just double click it. This is gonna be, um, top seven commission earners. All right, All right. So we have our first charts, let's make a few and then, ah, a few different ones using different pivot tables and then we'll create a dashboard. And I think I mean, we only need a few for you to get the idea whether we create, you know, three years or 20 it's not gonna matter there. And it's gonna take a lot of time, so we'll probably just create maybe, um, you know, two, maybe three more. Just so we have something to put on the dashboard some Let's in this video. Let's try toe. Try to see if we can, Just just to show you you know we spent a lot of time kind of cooking or not quickly started taking our time building this, figuring out a few of those issues Now that we have the tools in our tool belt, Uh, let's go through and let's make a quicker pivot table. So this is commissioners. What's something else I want to see? Um, the average list price by neighborhood. I'm curious what that's gonna come out like, So that's let's create a new ones. Let's start here. Let's do what we did before Kick pivot table. You see, it's selected that same data range. I'm good with that in a new worksheet. Perfect. I'm gonna go ahead and rename this tab right away. Um, average ah, list price by neighborhood. Artigas. Some kind of going fast on purpose. I just want to kind of show you once you get going. Once you kind of master this, it's really pretty quick to put together eso we're gonna right. We're gonna put our neighborhoods down the left hand side and see there's quite a few. So this company worked in multiple states. Um, actually, they work around the U. S. At the time that I think they were only in several states. So we have 159 different areas, so this one might be a little bit out of control. But let's let's just see what happens. First of all, we said list price. I want to see what the average list price. Now that's our some of our list price. We want toe one format. This. We might actually lose the formatting when we, um, do this average. And we did. So let's also sort this from highest to lowest and just see what happens. All right, so there's some big areas, obviously of $1,000,000 homes, the average list price. And I've actually these are all in Washington state in the United States, I can tell you that some of these are definitely nice area. So I mean, while those air big I mean, there's there's quite a few. So this report didn't really work out for us, did I mean there's just too many. I mean, there's 159 total areas listed. 157 have values. It's too much data, guys. For somebody to go through sleds, rethink what we might want to dio and I did actually kind of know that in advance. I just want to show you what what you can do. I mean, it was quickly put it together ago. Okay, Well, this doesn't really This doesn't really help us. I mean, it might help somebody doing some market research doesn't really help us for creating a kind of a nice summary report. Uh, no. What would work, though, is property type. It looks like there's only a few different types. Um, it looks like, you know, there's condos, residential, and there's resale condos and houses. Let's give that a try. And the nice thing is, we already have a report. Here s all we have to do instead of let's write, put property type down here. Let's take neighborhood out. And hey, here we go. All right, let's let's see what's happening here. Um, let's sort this do our formatting once again. All right? I mean, 29 items. Ah, still a little bit long. Looks like there's a bunch blanks would have to fix those if we want to go with this report . What I also don't like is it seems like there's pretty inconsistent naming within the field , so I'm kind of torn on this one. I think if it was a small subset, I'd be OK with it. But because there's so many and I feel like there's probably a lot of overlap and there's so many here that air, you know, if he like, is a condo the same as a resale condo and what's the risk? And, you know, there's resale Condo with Lower case. Upper case, um would have to do a bit of work. So, uh, in the real world, obviously, if that's something we really want it, you know, we could go and try to fix those would have to do some work with somebody. Ah, there's neighborhood. That's the one we already looked at. So we don't want that last name is probably not going to give us much information because that's the last name of the buyer or seller, and I mean, every single person's probably gonna be different. There might be a few of the same. One thing I do notice is offer status, you know, there it looks like they have nice, consistent use of naming conventions. There's only about 10 of, um so, um, what could we look out, we could look at average price by offer status. Would that be useful? Maybe final price. Let's see what else we have. Guys. Um, I also noticed there was type of buyer we could do. You know what might be interesting is type of buyer and the average. Um, let's say list. Let's go with a list price just because So let's try type of buyer. Where is this? Here we go. Fire type. We're gonna ditch property type. All right? This is looking promising, guys, I'm not gonna re sort it at this point. There's a lot of blanks. Um, one hot bogus. So bogus is interesting, cause I know bogus means those air offers to you put in that they weren't legitimate. Obviously, they were bogus offers. Um, what's funny, though? Is that such a large amount? And the reason for that is because people put in bogus amounts. So one thing we might want to do here is a couple things, actually, one we're gonna filter out bogus, um, averages. We don't, You know, they're they're useless. Anyways, they don't add any value for us. Ah, The other thing is, we need to do something with these blanks. So, um, so before we went to the data and we kind of sorted and filtered another way just so you know, if you double click on any value in a pivot table and I should have shown you this a long time ago, it'll actually show you what makes up that value. So if we double click on first time buyers now, it's gonna be quite a few. It pulls, and it creates a new sheet. It pulls every single data point that adds up to that. So there's quite a few 106. Um, Now you have to remember to this made a new sheet of data so you can't actually like do that and then edit thes. It's not going to reflect in your original data because this isn't original data. This is just a sheet. So I'm gonna delete this. I am going to rename this. Let's let's go with this. This is Ah, um, buyer type average list price, but we're going to do a few things. So first of all, let's sort our data by buyer type go to a custom sort this time we're going with fire type . Okay, so first time buyers Great. Um, let's see. Bogus. So bogus. We're just gonna filter those out. We're not gonna actually. Um, you see, there's only a handful. And if we come over here just to see what they are, Um, yeah, so you have, like, an $18.5 million.1? Um, these aren't too in recent poll, but this one is what's really goofing it up. So But again, any bogus offer, and not really a much value experience buyer looks like quite a few investors, and then we have all these blanks. So, um, instead of just saying blank again, let's put buyer type, um, not listed, and we'll just go down to the bottom here, and it looks like that's quite a few. So that means the people, whatever server they filled out, just didn't didn't say what what they were, what their experience was. Let's go back here. So, first of all, let's refresh this to let's filter this out. So down here again, if we use our drop down, we can just uncheck bogus. And there we go. Uh, let's do a couple other things. Let's sort this, um, largest to smallest. Let's format it. I'm going to take out the sense. And then let's do a quick pivot chart. Let's just see. So this is kind of useful. This what else we got? We got our pie chart. I'm just to mix it up. I kind of like our column. There's also Barra, which does it this way. That might be interesting. Let's see. Column calm, calm presents a bit better. Let's go with column. Let's move this over here. We'll even type this. This is, Ah, average average list price. Um, by buyer type. So a few things guys would give it a total because, really, it's just one total or it's actually an average. We could also I believe, let's see if we can do this. Expand sex. That's not what I want. It, um, if we go format what I really want a format. OK, so there's a lot of empty space on this. I want the the's big blue bars to be even bigger, less space. You see here Siri's overlap gap with Let's start reducing the gap with actually, let's just make this, um, there's even a slider here. Let's just see how that fans out a little bit nicer there, so we can probably go down to something like 50% even. All right, so let's do that. Let's close that. There we go. That's a little bit cooler, all right? Buyer type. It said Trump, but perfect. So the average so on experience buyer, the average list prices a little bit higher. Buyer type. Not listed. Quite a few investors than first timer. So it is. It kind of makes sense. Is the first time buyers or buying on average, cheaper listed prices. So I like that information. So we're going to use that in her, uh, in our report. Now, I see this video is completely way too long, So I'm gonna end this one and then in the next video will pick up and make let's make at least one more pivot table of useful information. 9. Offer Status Type Pivot: All right, everyone. So let's figure out. Let's get one more. You know, useful data point out of this. So we have a report based on buyer type. We have our total commissions by our agents. What else could we do? So we didn't do anything with, um, offer status, So I'm curious. Let's say we could do a few things we could do again. Like an average list price by offer status. Maybe. We just want to see a total. Like how maney deals are in each in each kind of category, if you will. So this data was probably run at the end of the year. Um, you know, so let it. Let's figure out. You know how many are still pending? How many did we close, etcetera? So let's just give it a try. Um, let's go insert. Do our pivot table again. We know that's all good. All right, so we want our offer status, which is our first data point, and really, we just want account. Um, So what we can do is put offer status here, and it counts, so we kind of figured out already. You probably want some of them because there's no numerical data to some in that category, so submit it. There's five awaiting by a signature, etcetera, etcetera. Bogus offers 53 someone so we could do a couple things, guys we could, you know, create a graph. That's what we so far dashboard. We do kind of want just nice. Kind of like little clickable like, look at graphs. Um, I don't want to overwhelm people with a lot of information. Plus, they know that they can then come to the tabs and see the specific information. Um, I just want to tie. Let's see. I want to rename this first. Ah, it's this is offer status. Um, counts. So, um, we do want to make a graph of it. So let's just see what what a cool graph would look like. Let's go back to our inserts or pivot. That looks like there's quite a few and offer rejected. I believe that's what it is. Yeah, there is. So that really shows you It's almost like a bell curve, if you will. Um, and I believe this. This kind of follows like, uh, a natural progression, if you will. You know, first, you submit offer than you have to wait and submit the listing agent. I'm not quite sure. I forget what S t I stands for pending. Then it closes or, you know, buy refused to sign, offers rejected inspection fail. There's lots of reasons on why the deal and go through. So they had quite a few offer rejected. You know what might be actually interesting eyes his percentage. I want to see a percentage of the total that might actually be the rial. Um, the rial, uh, useful information. Here's let's go if you remember. So we have our account. Now we want to do a percentage of column total, so 37% of offers get rejected. That's probably some useful information for management. You know, when 1/3 more than 1/3 of your offers are being rejected, That's pretty hefty. I mean, when you have quite a few that I mean and then they could look and see what? How many close Now, if they really want to, if we want to, we could feel trying to say how many close. Um, you know how many you know? Ah, what else would be so if we just cherry picked like how many got refused. How many times that the buyer and set your But in our case, I just kind of want to show them all this and again. Let's take another graft to see how this looks. So it's the same relative, you know, kind of looking graph. I mean, the dollar amounts are gonna basically be reflected. Its just now that's in percentage format. But I think the percentage formats really what's more useful out of to mine out of this information. So the real question is, is there something you know what I think might be useful, guys? So I'm gonna exit out of here and sort this first of all, um, here we go. Sort largest to smallest. Now, you notice a lot of this. It's not like I preplanned guys exactly what we were going to create. This is kind of how it would be if you were trying to figure out for yourself. Like, here's a large amount of data. Go sort through it, pick out what seems like it's most useful and do something with it, and that's essentially what we're doing. So let's do a pivot chart now. I kind of wanted to do the bar so that somebody looking at this can kind of come down and see, like, Okay, offer rejected. Now we have to do something. Let's just create this chart and show you what it looks like. So let's make it a slightly bigger for one. Um, so these percentages, we're gonna clean up this. Ah, this down here, we'll get rid of total. I think that's goes without saying now we can kind of see the totals, but they're little cram are still going to do something there. Um, offer status, I believe. Oops. That's not what I want it. Let's see if we can. Don't know if we can was hoping I would be able to hide this guy. It doesn't look like we can about what is nice is people could filter it so we could actually filter out. You know what? Let's do that. Let's filter out some of the smaller ones and we'll just say it's the top. Uh, let's do these 123457 Let's go with seven again, right? So actually, here, let's do our value. Filters top 10 but then weaken. Drop this down to seven. Look at that little bit cleaner graph and then in our header, we're actually gonna right, um, top seven, um, offer statuses. Quite sure you type statuses, or if it's, ah, offer status. Um, turn off her status. Uh, trying to get another word. Add to this. Let's just leave it off her status types for now. Now, this guy's kind of wish I could get rid of this, but I'm not quite sure that I can, um, they're filters and see more sort. We'll just leave this for now. Something of the world. One thing I do want to do though, guys, is this So if you right click again. So if you click on anything you can then right, click and do something with just what you've selected. So if I click on this and then right click, I want to format it Now there is a way and let's see again if we can find it. Um, okay. There is basic There is a way to rotate it. So it is vertical instead of, um, see vertical alignment. Here we go. Text direction, horizontal. So again, you just have to click around search guys. It happened to be under text options than the third option here and then text direction. So we want rotate all text 90 degrees and we actually want it this way. So it's kind of pointed up, and we could actually what looks kind of slick. I don't know if they have a custom custom angle. For some reasons, I'm gonna let me do it. We'll just leave it at that. Then if it lets you rotate a set amount like, say, 45 degrees. So there we go. So now it's a little bit. We have toe make this just a bit bigger. And we have to make this just a bit smaller to get it all on here. All right. Curious two guys. What would happen if see this? Ah, this guy just doesn't want to move. I'm not sure. All right, so this is useful, I think. Right. So now if somebody looking this Mandarin can say, Oh, wow, wait. Now, somewhere it close to 37. 38% of offers or being rejected to me, not even being in a real estate position. That number sound high. So I think this is a useful chart, So let's save this guy's that were up to eight minutes. Let's end this video and then let's go ahead and create a dashboard in the next video because we could go on making more pivot tables, but I think you get the idea. 10. Dashboard Creation: Okay, guy. Next is to create a dashboard. So we have our original data that we have are three kind of pivot tables we've made with little charts. So graft a manually insert an extra sheet. Let's see, it didn't do it here. Now I'm gonna move this. And if you just click and drag, you can move a sheet anywhere to see, know, and we'll name this one dashboard. All right. And if you didn't know is well, you can color tabs. You'd actually, um, colors. Let's give this, like, a distinct color. So tab color. If I just right clicked on that, um, it would give this like a nice blue. So now, as you click around, that tab actually stands out, maybe we have are three sort of pivot tables as, like, a nice orangey color. And we ever transaction data. It's just the raw data as Ah, I don't know what would go great with this guys. Let's go with this blue gray color. It doesn't really matter. And that's just complete aside, but just kind of makes things stand out. So for our dashboard, we really want this to be the central location. People are going. We could even go so far as to lock these tabs, or I call them to have sometimes locked the sheets. We could hide the sheets so people don't really realize they're there. Lots of different things we could do. Guys, I'm not gonna get too much into that. I'm just gonna leave it. Assume you're okay with them seeing the raw data. I mean, they provided to you, obviously. So first thing we do is kind of color this entire sheet. Just so it's not a spreadsheet looking, if you will. Ah, so let's give this may be just a nice, like gray color. So there we go. We just highlighted everything and filled all the cells, so we still have ourselves. All right. So as these, this is going to be a Z Z is this guy? So first, let's give us a title like, um, dashboard report for it was 2006. Um, x wise the company. Now, I'm just going to do a few things I'm gonna sent our I'm gonna bold it and make it a little bit bigger. Let's just do that. Give it a save again. Now, all this is a simple is guys who just come and click on a chart and copy it. Now there's lots of ways to copy. You can do it through the menu. I'm a fan of the keyboard shortcuts. So if I highlight this and hit control C, then go back to my dashboard and press control V. That'll paste it. And there it is. Um, so let's resize this now. Let's see, when we start recycling things on here, we might have to rethink some of this layout. Let's get everything kind of on here first. So this is our second tabs. Let's copy this. It's just press control V. And then we can move stuff. For some reason, it didn't keep this one. You know, Why doesn't like Oh, it's because I still have that highlighted guys. So when I was pasting, it was basically deleting what I had highlighted. And then pasting the new value, there s so just click off it. Click over here. Copy this. Come over here. There we go. Sorry about that. Let's just resize this again. We're gonna clean this up, but for now, let's just kind of get everything on here and then the offer type sexual. Before I do that, let me just make sure I clicked off and I didn't click over here offer toe. And if you want to know how, moving through the sheets quickly. If you hold down control and hit page up, page down, you'll navigate through the different sheets. Okay, I So I'm gonna get rid of pivot because, you know, ultimately people would just be looking at this. So let's see now we can hide this as well if we want to create a little bit more real estate. Ah, that's perfectly fine. So let's see. So we have the top seven here, But obviously that isn't just the top seven anymore or it is top seven. Weeds can't see the mall slips. That's first of all to start fixing up this on. Maybe we have Teoh just make this a little bit bigger. That's not too bad. Uh, this it looks like we're gonna have to scoot over here a little bit because Kinka rid of these Not to the best of my knowledge. So we have a choice. Do we? You know, do we put all three side by side? We may be put these two on top and one bigger one on the bottom. Do we make them all the same size? Um, that's kind of up to you guys. You have to kind of decide what makes the most sense for you and kind of who your reporting to do. You have it. So where they do have to scroll down to look at multiple charts? Or are you really trying to get everything kind of hoops? I wasn't trying to move that. So right now we're just kind of doing some clean up, if you will, and kind of formatting. So let's see. And then let's make this and you could decide. I mean, obviously, I doubt this is what you would want, but you could, you know, just some ideas, guys. On what you could do, you get certainly put these right pretty much neck. I mean, the world is your oyster guys. After resize this just a bit. Now this kind of bothers me. I really wish I could get rid of clear filter from offer sets so we don't want to clear the filter. What we really want to do is get rid of this drop down box all altogether. Let me see if move to report, feel church hide access field buttons up there we go, guys. Found it that easiest. Let's just hide. Um, hide all field buttons. We figured it out together. There we go, and we'll do the same here. Um, we don't have a lot of different filters on these. Like if we had filters where we wanted people to be able to choose lots of different things and, you know, manipulate the reports than sure, you know, we might want to give them those options. Um, but in this case, it's really a fairly simple report. I mean, it's, for example, that buyer type and the average price. I mean, there's not a lot to it. So, um, we could monkey around with sort of formatting these and getting them all to the same size . Um, but to give you an idea, so there's Let's save that, you know, you might want to create 1/4 report, for example. Let's see. So one thing, though. Let's see, we're up to six minutes, guys. So actually gonna end this video when we come back, we're gonna add in another boxer, and it's really useful it's Ah, it's actually pretty cool, and it's a way where we can apply filters to these all at once will be able to select certain parameters, and it applied all of these so somebody can kind of play around with the statistics. 11. Dashboard Slicer: all right, but it's the last thing I want to show you with something called slicer. Now slicers basically well, let's go to, and we'll just read what it says. So it's on your insert ribbon, um, same place where pivot table was and go over to the right. Here you see something called slicer. If you click on that now, actually, should morning before you click on that, you want to select which charts you want apply this to. So let's just hover. And let's figure out what slicer does. First of all, so slight, sir, is basically a filter. Data visually make it faster and easier to filter tables, etcetera. So rather than somebody. So we have some nice charts here rather than somebody having to go to the source data filtering and make some adjustments. They could just apply it right here. Now you can, um, click on multiple charts and apply slicer to all of them in our situation that we don't really have a lot of common data, you know, like with between our different charts. I mean, this one has to do with the buyer type. This one has to do with the agents and their average commissions. This one has to do with the ah status type. So there's no correlation between all three of these, so it makes it a bit tougher toe to choose a slicer that will apply to everything. So instead, we're just gonna choose one. But certainly if you had a little bit more complex dashboard were say you had commissioned earners and then he had a second port. That was sort of average final price by, uh, agent. Then you could do something where you could clearly the slice or we could filtered by the agent. It would apply to both of them. So, actually, speaking of, let's let's apply toe slice. Or just to this, I'm just choosing this one report I'm gonna click slicer now brings up this drop down box. Now you might recognize thes each of these is the categories that the column headers, if you will. So basically, we're going to give somebody the ability to filter out based on these different things. So just to warn you, each of these that we choose is going to create its own special little box with dropped with the list of the filters that can apply. So you don't choose to many of these. You really need to think. Well, what would somebody want to filter by? Let's choose agents. So even though we have top seven, um, and you see, it's a light grid because we've already filtered out. If remember, we only have our top seven. Maybe we want to give people the ability to look at more people. Um, in that case, the top seven kind of heading wouldn't really apply. But for just demonstration purposes, let's do that. Um, there's not much else on here that would really apply. Perhaps buyer type. Like maybe we would want to see, you know, different agents for specific buyer types, etcetera. Um, for now, I'm just You know what? I'm gonna keep this very simple, actually, I will include buyer type. I'm not sure that it will work and maybe offer status. Okay, so you see it through in three boxes here. Now you can move each of these. So let's move this and this. And this is again part of just your planning. Like what do you How do you want this report to be laid out? Um, do you have enough real estate to make this look good, so you'll notice. So remember, everything is related just to this chart. Eso, in that case, actually, probably would. If you were truly doing this, you'd want to put these kind of maybe next to this and then these charts off to the side. Um, so it's only giving you options. Anything that's kind of got the blue shading means it's an option that could be clicked or on clicked. Um, things like bogus are already filtered out, so that's why that's not available. Um, so as well, we have this check box here, So right now everything is selected that is currently being shown. Now if we just click, So let's say we just click first time buyer. So our report changed drastically because now it's saying Well, for first time Homebuyers, here's the top commissioners, and here's what their commissions were. Um, if you click here, you notice it will change, and this report is changing with each one that we click on. That's changing quite a bit, right? There's definitely different makeup of who earned commissions based on our buyer type. Now you can choose multiple of these and the way you do that is you see this little check box that says multi Select all tests. If you just click that and you see, it's kind of highlight yellow. Now, now you can slack multiple items. Ah, same thing would happen over here. So even though this says top seven commissioners and currently it just it picks everybody and then it, you know, just displays the top seven. We could, though, kind of move that inherent inadvertently with me to scoot back over here. Um, we could filter out pick certain agents. So only Chris Benjamin, that's me. I didn't actually, Aaron, this commission that just put my name and, um But since I've only chosen myself, I mean the top seven. But there's only one agent that it's allowed to reference, so maybe we also want Kelly angle. But if you remember, we need to apply multiple filters. So maybe we choose now again. Say, we only choose seven people. Well, they're gonna be the top seven because there's only seven options. So filter like this, you actually probably would want everybody. But maybe you wanna, like, filter out. No agent, etcetera. Now these people didn't have amounts And that's why they're great out. Because there's nothing adding them, one that any value. Now, you see, we kind of goofed up our top seven because now there's no longer that filter applied. So really, to get that back to top seven, we would have to go back here and, um and you see now this is well, so I should mention as well, anything you do over here, what it's really doing is going referencing the source data. So it's coming here, and it's just making changes to this. It's really just applying filters to this data, which is then being reflected on this graph, which is also copied right here s so we could certainly come back here, change this back value filters top 10. Make this top seven again and hit. OK, you'll notice. This is good. And that would be what we see here. So just before weren't you know, you kind of entering with a dangerous territory, but you can kind of mess up, especially that no longer obviously would apply to the title, um, something with offers status. So we could say, you know, right now, it's just saying, hey, all offer statuses Yeah, I want to see the commission's makes sense that submit It doesn't have ah commission type, nor does know bogus property not active. Submitted awaiting by. So anything where the deal isn't actually finished. You know, there wouldn't be a commission. Um, close, definitely some of these. For whatever reason, there was a commission involved, so But you could choose different ones, and again that's gonna pick your data. And as you choose different ones, you see these air changing as well. And I mentioned that before, So anything that's close, it's only gonna let you choose from close deals Where these agents had close deals. This agent didn't have a close deal. Um, is while there was no bogus underclothes, so makes sense again. Can highlight multiple items using are yellow. And in this case, we want this so just kind of shows you the power guys of, uh and then we can choose these. But there are light blue because there's no options just so that slicer guys. I mean, it just shows you the power to give somebody an ability to filter without having to expose them to actual. You know this information if you want toe and maybe you just, like, give them the agent. So maybe it's no longer top seven commissioners. It's just commissioners, you know, it had that Messi graph, but then you give him this filter that they could mess with, and they can pick and choose from there. So that is how you apply slicer guys. And like I said, you could apply it to all of them. If you had more relevant kind of data, that would make sense. 12. Course Conclusion: All right. Everybody said that is the course, so I just want to wrap up real quick. So just to go over it, we took our raw data. Um, again. Remember, it's gonna go home here. The top a one. Um, you know, you need a nice organized data set to start with. From there, you can start building pivot tables. Um, you could certainly like I showed you how to add multiple different columns with different information. Ah, but ultimately, our goal is to create a dashboard. So just looking through these. So we took our data, created graphs, copy those over your dashboard, and then also introduced slicer so you can filter on those a little bit better, so ah, lot of powerful tools. And again, I mean, this is almost sort of Ah, the basics of pivot tables, if you will. I mean, it's much more powerful. You can see, obviously, with the amount of information you could drill down on these on these different data points . And really, all we're doing is taking raw data and making sense of it. So now you know, management has really reports riel information they can use to make better decisions, and that's our goal with this some that said, Guys, I appreciate you taking the course. Love teaching this course, Definitely. I love a big fan of pivot table, so I hope you can incorporate them into your ah, work life, your personal life, whatever you might do with them. Ah, few final points definitely appreciate if you can take the time to leave me a review for the course. I really appreciate it. Love, hearing your feedback, anything I can do to, you know, to get those five star reviews much. Appreciate it. Um, and then it's, well, feel free to message me if you have any questions during the course, something you didn't understand, just let me know kind of where we were in the course of what I did that you didn't quite catch. I'll do my best to answer for you what to do, Um and then lastly encourage you to check out my other courses. I've got many, many courses, lots of things being built in Excel courses on accounting, finance, entrepreneurship, personal productivity and lots more. So if you go to my profile looking all my courses, uh, definitely encourage you to do that if you like this one, you'll probably like some of the others as well. Ah, that's that. Guys. I wish you the best of luck and excel and with your pivot tables.