Myself a sub nose so that we can kind of take our time and go through our pivot tables here today as well all right well very good well if you have not created one this is the perfect place for you because we're going to take our time and actually build out our pivot table you've used it before maybe you'll pick up on some new tips and tricks here as well so I'm going to go ahead and jump into my slide deck here can everyone see my presentation just want to make sure we can see everything okay excellent.
Okay so we'll start off just by talking about what is a pivot table and the way I like to answer this question is by asking a question okay so a pivot two a pivot table is a powerful tool so the question I want to ask is how many of you would like to calculate thousands of Records just with the snap of a finger calculate thousands of Records instead of moving things around right and and and sorting and filtering and moving and deleting rows and columns inserting well we're gonna that's what a pivot.
Table is for we can calculate thousands and tens of thousands of rows and Records how many of us would like to summarize multiple Fields not just one field but multiple Fields maybe you have a sales field maybe you have an orders field a quantity field we can do all of that from the fraction of a second the pivot table will do that for us and then finally how many of us would like to analyze data right so find comparisons within within your large data set I've seen some data sets out there some are very very big 13 000 rows.
Of data you know 30 30 columns I've seen some of that and so how do you kind of just make sense of all of that right so you want to find comparisons you want to find patterns you want to find Trends and that's what we're going to be taking a look at today right so really we're going to be constructing a pivot table so we're going to be so at the end of this session you will have learned how to create a pivot table from a table you will learn how to filter pivot tables.
Want only all of you to go ahead and open up that file just give me one second I'll put that in the chat for everyone okay so go ahead and click on that link it's going to bring you to a web browser so what you will need to do as a matter of fact I'm going to go ahead and demo that for you I'm going to open up my browser show you what it looks like just give me one second here.
Okay so here's my Chrome browser and so it opens like this this is just read only right you cannot do anything with it so what I want you to do go ahead and click on the file menu okay so click on the file menu and you're going to go ahead and click on save as gonna download a copy of that file and depending on where you have that downloaded just go ahead and open it up I'm going to go ahead and open up my.
File here and here we go we're good to go I'm going to go ahead and close this screen and maximize my window and I'm going to go into enable editing all right and we are good to go all right just gonna show my full ribbon here okay very good so you should have that open uh let me go ahead and just move some things around here so I can see the chat to have two monitors here as well right.
So if you have that open excellent so we have here here are our objectives make sure that you're on the very first worksheet tab notice so we do have a quite a few down actually only four right we're just going to be going from left to right we'll start on the objectives we'll talk about um orders we'll have a little time of reflection and we'll build our table here as well but either way we want to view some basic sales data observe the challenges and then we're going to go ahead and view a pivot table and observe the benefits and then once we do that we're going to.
Go ahead and create a pivot table we'll create some queries and some slicers and then we'll go ahead and explore a pivot chart as well so go ahead and type in text in the chat for me if you have that if you're good to go if you have your file open make sure that you have that open here so go ahead and type in X in the chat for me okay very good.
Posts Related:
All right let's see just counting here I'm not going to cover all of them - Make Automate
I'm not going to count all of them all right very good okay excellent okay so let's go ahead and jump right in here let's go ahead and click on our orders table here I'm sorry our orders worksheet tab okay I have a sheet one down here you don't have that so don't worry about that.Let's go ahead and click on the orders tab here and so what we're looking at is a basic order form okay so we just have a couple of fields here we have the date of the order the order ID the associate that made the sale each associate is affiliated with a particular region and we have several products here as well we have units we have the price and we actually have the sale now I have a little function over here it's called the unique function just kind of grabbing all the associates that.
Are in this data set okay so we have all these associates here and then here is a list of our products over here as well so let's examine this data really really quickly I'm going to go ahead and click anywhere inside of my table here let's see how big this data set is right go ahead and press Ctrl n so press control plus end and let me know how many rows of data are we working with here I'm going to go ahead and open the chat so press Ctrl n d yeah 1003 right lots of data.
All the way down to row I'm sorry ten thousand and three I said one thousand three so this is down to row ten thousand and three the price press control home to go back to the top here all right and so here we go so here's what happens your team leader comes to you and says hey these these are all the orders everything is up to date I want you to to create a report on this all right I'm using Office 365 so this is the current version that I'm using here right so all of our all of our um.
Trainings are going to be the latest it's office 365. right excellent okay and so we can anticipate some questions right such as maybe what was the highest you know what was the highest grossing region who was the highest grossing uh associate right or what was the total sale of everything so we can anticipate some questions here so typically what we would do we would you know probably use these filters here maybe filter by.
Region right go ahead and grab the data filter by product and then maybe drop in some some functions such as the you know um sorry such as the average sum count different functions like that as well but that takes a lot of time we can't really we don't really have the time to you know to get all of that done so we have a little dilemma here how are we going to go ahead and do this well I'm going to show you a pivot table maybe this is your first time seeing it.
Right and as you can see with the pivot table it's just like building blocks we can just move things around and we can
And let's see if we can answer some of these questions here right so you don't have this just going to go ahead and click here let me expand this a little bit so here's a fully functional pivot table right now based on this table we can answer some questions such as what is the grand total of all the sales well look at this our pivot table just all of those all of those different um I'll go ahead and put this in the chat again here's the file sorry about that okay.Um and Kim if you can help me share out that file as people come in that'll be great thank you or share that link okay and so here's our pivot table just giving us answers so we can see the grand total of all sales right it's 915 000. we get the breakdown for each region so in the east region we had 147k and look at this we actually have our products as well so the grand total for our Aspen product is 44 121. so we can answer a lot of different questions here on this data set just by.
Taking a look at this pivot table it's spitting out all of the information for us so guess what what if I want to see how my associates performed look what I'm going to do here I'm just going to remove my region click on my associate and look at that I can go ahead and collapse everything let me just go ahead and it's okay we're going to go ahead and do this together in just a little bit but look at that in a fraction of a second a return from oh from that 10 000 rows of data it returned the values for us very very quickly.
