Pivot Tables in Excel | Excel Tutorials for Beginners

what's going on everybody today we are starting our excel tutorial series [Music] now there are so many things that you can do in excel so i don't know how long the series is going to be it could be 15 or even 20 videos but what i do know is that i'm going to be covering just about every single thing that i've used since i became a data analyst and i want to show you how to do it so it won't just be the more concrete things you know like pivot tables charts vlookups things like that

it'll also be some of the more nuanced things like how to deal with missing data or how to deal with dirty data and how to clean that up within excel and so those are things that you may not be able to do you know if somebody wasn't showing you how to do it and so that's what i'm going to try to help you because i know that that is something that you will need to do or learn how to do in excel now before we get into it i want to give a huge shout out

to the sponsor of this excel series and that is udemy i took so many excel courses on udemy when i was first starting out as a data analyst and there was this one course that i kept going back to over and over again because as i got into it in my job i realized that there were so many things that were in that course that i really needed to know but i didn't realize i needed to know it and so i'm going to put the links to those courses in the description in case you want

to take those again huge shout out to udemy without further ado let's jump on my screen and get started with our very first excel tutorial all right so i'm going to go ahead and get rid of myself we are going to be looking at something absolutely pivotal in your data analytics career and that is a pivot tables and i think that's really appropriate it is probably one of the most commonly used things i think that data analysts use to convey information in excel it's super easy to group things together to display information in a very

easily understandable way especially for people who are not data analysts right i use this a lot for other managers or for higher ups who don't want to get into sql or you know aren't super tech savvy in like python or tableau they just want it in excel and so i use it all the time for that reason and so we're going to be using this data set right here bike store sales in europe i will include this link in the description um we're not going to look at the columns just yet we're going to download

it i've already downloaded it a few times but we are going to go to our downloads we're going to open it up and we're going to open up this sales right here and give it a second all right perfect and so here's what it looks like at least on my screen i'm gonna spread it out just a little bit um and really quickly let's take a very quick glance at this so we have a date a day a month a year so some some date information then we have some customer age information so how old

was the customer again this is bike sales so what did you know what did they buy and then we have some demographic information so this is their age group we have uh the gender the country state the product category the subcategory the actual product that was purchased and then we have things like um you know how much these things cost the quantity that was that was ordered so we have order quantity unit cost unit price then we have the profit cost and revenue all things that we almost everything in here we can in some way

put into a pivot table now i'm not going to go through every single variation of that but we are going to be um looking at a lot of this revenue over here because i think it's it's pretty easy to show the value of a pivot table with especially with you know currency or money so what we're going to do to get started is we're going to go up to insert and we're going to click on insert and then we're going to click on pivot table now really quick there is a recommended pivot tables and if

you click on that what will come up is some recommendations that excel gives based on the data that you have and it can kind of give you some ideas of of what you can do with pivot tables it's going to generate it for you we're not going to do that we're going to build our own but let's click on pivot table and it's going to auto select basically everything and that's fantastic but what if it doesn't come like that i just erase that if it doesn't come like that you can click right here you can

kick excuse me you can click control shift and then the right arrow and then the down arrow and that is going to select all of our data and you have right here a new worksheet or an existing worksheet we're going to create a new worksheet just tends to get too clogged up if we put it on the same worksheet that already has a lot of data in it so right over here are pivot table fields and these are all of our columns that we just looked at and we're going to be able to select those

and kind of drag and drop now if you just took the tableau tutorial series that i just finished doing last week then this is going to be pretty familiar you're going to start seeing a little bit of hopefully some patterns about how the data is kind of displayed and so we have our filters down here we have columns rows values all these things we will be using i'll show you how to use today as well as some additional things one thing that we want to start with for this demonstration is we're going to be looking

at kind of the these bottom ones right here profit cost and revenue and we're going to be doing that per country per country and state and we'll kind of do some drill downs and i'll show you how those work so for just to start out we're going to take the country right here and you'll see it populate right over here in fact um let me zoom in maybe once yeah that should be fine i don't know if i want i might zoom in again in just a little bit um so we have our country and

it's just like this very very simple oops now i'm going to include the state now i'm going to drag this all the way and i'm going to put it under you can put it above or you can put it below i'm going to put it below it definitely makes the most sense there now when you do that it kind of populates it in an expanded way but you can collapse this very easily we're going to go right here we're going to right click we're going to go down to expand and collapse and we're going to

collapse the entire field and so now here are all of our all of our countries as they were before but now each of them has this plus sign to the left and if you click on it now we can go and we see this state that we that we added to these rows and what this is going to do is it kind of is like a roll-up or it's like a grouping and so if you you know have taken the sql tutorial series and you've done things with group by this is very similar to that

and if you've done the tableau tutorial series it's kind of like a drill down it's very very similar so you can drill into the information so we can put some values in here and what what that's going to do is that's going to kind of create something some context to what this what we're grouping by so just for visual purposes let's add this revenue so this is the revenue that is bike bike sales revenue right that's what we're looking at so this is the sum of the revenue for these bike sales per country now if

we drop down right here we can see that in australia new south wales had uh 92 was that 9 million 203 495 queensland had 5 million you know et cetera et cetera so now we can break it down we can't it's we don't just have to look at australia we can now drill down even further to the actual state is what they're calling it the actual state within australia and so it's super super useful and you can do that for every single one and so we can look at canada we can look at france and

we can really drill down into the revenue for each of these countries as well as the states within them now over here this is not the most pretty it just says sum of revenue and then it has some numbers not not the most pretty thing i've ever seen really quick we can go like we can kind of highlight over these and we can go back to home you can do it in a couple different ways we can go to home and we'll type currency now it has these two dot zero zeros at the end you

can get rid of those really easily by going like that um already this looks quite a bit better just visually especially if you're looking at it in you know dollars you can change the currency to different currencies if you want to do that now we don't just have to do the sum of revenue we can do a lot of different things so let's go to the value field settings so we can customize this name so we can do um revenue oops we could if i could spell revenue per country that's fine that you know it's

just a placeholder trying to show you but we don't have to just do that you know we could do the count the average the max the min we can do just about anything we want but let's keep it the sum right now and if we want to we can show this value as different things so we percentage the percentage of column total percentage of row total let's do really quick just for demonstration purposes the percentage of grand total so when we do that we can see that the united states the per revenue per country the

united states has 32 percent just between these um you know these countries and australia has the next one so you know it might be kind of hard to glance at this really quickly to know who has the highest but what we can do is we can go right here and we can go to sort and we can do largest to smallest and there we have the united states on top now when you do it right here it's not sorted largest to smallest you'd have to go and again click sort and do largest to smallest and

so now we can see that california has the has the um you know biggest percentage they're pulling in 20 of that 32 of revenue so i'm just going to click ctrl z a few times and get us back to where we just were and what i want to do is i want to show you a few different things pretty quickly so we want to pull in this profit in this cost and so i'm going to pull in this cost next and then i'm going to pull in this profit again i'm going to change the currency

on this and i'm not going to change the names right now but yeah you absolutely can do that now the revenue is the how much is actually being sold so you know for the united states it was 27 million now the cost is how much did it cost to manufacture or or store um or distribute all of these products so that was 60 million and the profit is actually how much money is being made at the end of the day after um you know all their costs after all their employee costs after everything they're still

making the united states is still making 11 million dollars now you might look at this and you might say well you know i can kind of glance at it and say know that this profit is correct based off these two numbers um but we can do a calculated field um if you remember what calculated fields are that's something from tableau very uh basically the exact same thing and so we can create an additional column right here that is a calculated field that can add and subtract these things to make sure that our numbers are adding

up correctly so let's do that really quickly let's go do pivot table analyze we're going to go over to fields items and sets and go to calculated field now we can name this anything and i'm just going to for demo purposes i'm going to say oops calculated field demo i'm sure yours will be different now if you want to you can go in here and this is the formula it's almost like you know we haven't looked at formulas this is our first tutorial but you know when we look at formulas it's basically the same thing

as writing it if inside of a cell but here it gives us kind of this open text to do how we do what we want with it now what we're going to do is we're going to do revenue i'm going to insert that i'm going to get rid of this i'm going to do revenue and so that's the the very large number and then we're going to subtract and we're going to subtract our cost i'm going to insert that and let's do this and click ok so this is our calculated field demo column that we

just created and as you can see it matches our sum of profit column exactly and that's exactly what we want to see we want to kind of check to make sure that this revenue and cost fields are generating the correct profit and sometimes those are off and so it's really good to kind of check those and have that additional column you probably wouldn't have this if you were you know going to submit this to somebody just so you know now that this is an actual column you can't go here and do something like cut or

and paste it over here you know that's not i won't let you do that what it is is now an actual column and so we can go and remove that and we can add it back at any moment so if we want to go back and add that oops add that down here we can do that because we've created that column it's now permanently there unless we go and delete all of that data and so we can just click this check mark and it will get rid of it for us all right now the last

thing that we have not used down here is the filters now the filters is exactly what it sounds like it's going to allow you to filter on certain things but probably not things that you already have included in your pivot table so if you add something like the country down here it's going to kind of expand everything and then if you then go and filter on it it kind of breaks it down that's really not what the filter is kind of used for or meant for for example right up here we have customer gender okay

so let's take the customer gender and we'll put it in this filters now we can see all of the revenue all of the cost all the profit and we can do that based off of the gender so we can filter by a gender not really having to change anything about our pivot table and so at a super quick glance we can see that the males are the the profit from the males is 16.487 million and the profit from the females is 15.733 million so at a super uh basic level at a really quick glance we

can see that the men or the males are you know spending a little bit more than the females by about about seven hundred thousand dollars now let's go ahead and create one more pivot table uh we're going to create a pivot table right over here let's go back to the sales right here again control shift right down it's going to select all of our data and we'll click ok so one thing that we're going to look at is we're going to use some of this date information right here so let's select our country just like

we did before and what we want to do is see you know what year were we performing our best when were we doing our absolute best with let me go back with our sales so i'm going to select the year and put that in our columns and so now we have 2011 through 2016 and we want to look at our revenue so let's put our revenue right down here and now we have all of our revenue notes again make this into a currency just like that and super quickly now we can get a really quick

glance at how australia was doing each year and we can see that there was a huge uptick in 2013 and a huge uptick in 2015. that didn't happen for every single country it did go up for most countries very slightly for some but we can see on a large scale from year to year what that's like and so within just a few minutes we're able to create some really useful pivot tables that anybody could look at and understand and that's really the biggest use of these pivot tables is that you can kind of group these

things together show some information data at kind of a broad larger scale and make it to where anybody who's looking at it can understand it that is why pivot tables are so useful and so i hope that this video was helpful i hope that i was able to walk through it and help you better understand how pivot tables work and how you can use them when you are working within excel thank you guys so much for watching i really appreciate it if you like this video be sure to like and subscribe below and i'll see

in the next [Music] you