Use of Excel in Data Analysis
Start in a couple seconds give people a few more seconds okay all right hello everybody on behalf of the learn it team we are super super excited to see you here today i know there are a ton of familiar faces and people who have joined us before and if you have you probably can tell this is a little bit different of a setup than we usually have usually we are in a zoom room but since excel is one of our most searched topics and something that we really excel here at learn it um we are on a different platform because we had so many people sign up for this event which is amazing so that's why it looks a little bit different and if you are new here if this is your first time welcome my name is marissa and i am the events and community coordinator here at learn it again if you're new learn it is a learning and development organization and we are extremely passionate about helping you and your team stay up to date on your professional development skills today's event is data analysis with excel which like i said one of our most searched topics here at learn it so we knew this was going to be a big one how this works we do these community events every single week and we usually do them um we do them every single friday and then sometimes we do them on wednesdays and they last about 50 to 55 minutes we have monthly themes and the monthly theme for february is visualizing and presenting data we also just started doing something new where when you join us for every session during the month you get a badge so we will be recording um and keeping track of who comes to every event and if you come to every event in february you will get a downloadable va badge that looks like the one on the screen for visualizing data and this is great because you can put it on your linkedin for employers to see you can send it to your managers your employers or just share it with your friends these events are always led by subject matter experts and guest speakers and just a couple of more housekeeping things any questions that you have you can put them in the live stream chat on youtube we will be checking that chat and we will be answering any of your questions that you have my colleague carly who is our events and community partnerships lead will be checking the chat and sending me any questions sending them to our speaker so we will answer those for you and this session will be recorded so if you by any chance have to drop off early or you have a co-worker that was had to miss it this will be recorded and will be put on our community site off-site which i will explain explain a little bit later on at the end as to why you should join the benefits of it and how you can check out the recording so our guest speaker today is not guest speaker he's one of our internal internal instructors here at learn it there's truly nobody better than him he's amazing joe patty he is an amazing facilitator he is an office 365 expert he has designed so many of our programs for some of our largest clients and fortune 500 companies he resides right now in alameda california but he is a new jersey native so if you ask him which he likes better he might just say new jersey so don't ask but without further ado i will pass it over to joe thank you so much marissa i really appreciate that and thank you carly for setting this up uh really excited to be here with everyone today talking about excel one of my favorite topics data analysis so uh we have a great topic here for you today we have exercise files in the description so if you want to follow along you're more than welcome to just click that link below you'll click on more you'll see the exercise files there and you can download them and as i said follow along if you have any questions of course ask but let's get right into it let's have some fun here so what i'm going to do is i'm going to share my screen there we go all right so now i'm going to just simply open up our data analysis webinar file here and this is what it looks like and we're going to be talking about how we can analyze data this is really important right why do companies actually have data well they have data so they can analyze it so they can solve problems that they're having within the company or so that they can grow as a company now since computers came out data has been one of the biggest drives to improving companies all over the world internationally so here i have a great little example called jp company payroll information and it's just some simplistic data nothing too crazy here you'll notice that i have a numbering system i have a first name last name employee number division that they work at department and some other information about the employee now when we're talking about data we may want to view it in a certain way and to actually view data in a certain way we have to use some of the tools that excel gave us for instance maybe my boss comes in and says to me well hey joe is there any way that we can view this employee list by last name in ascending order and i think about it i'm like yeah sure you can actually use a feature in excel called sorting data so to do that the first thing i'm going to do is i'm going to click anywhere in my last name column now a lot of people try to just highlight everything the good news is you don't have to do that just select one of the last names anyone that you like let's say for instance i don't know i'll just click around and select rows after i do that i'm going to want to go over to my data tab because well that's what we're talking about here we're talking about data so i'm going to go over to my data tab and you'll notice that once i'm clicked in my data tab here underneath my sort and filter command group i have my sorting tools and i have my filter tools and we're going to talk about filtering data as well but for my sorting data tools you'll notice there's three buttons there we have a quick button here for a quickly ascending order sort a to z or a descending order sort from z to a and you can use either of these buttons depending on how you want to view the data you want to organize it in some sort of way so since my boss asked me to sort it in ascending order by last name i'm simply going to click anywhere in this last name column here and click a to z and when i do that looks beautiful perfect but the thing is and this is a really big tip for you if you are analyzing data you're sorting this data like this and you're ever asked to bring back the original data well if you don't have a number set it's not going to work so let me explain a little more let's say i just sorted this by last name a to z and then later down the road my boss asked me to sort it by department in descending order so i'll click anywhere in department and click z to a well if my boss ever asks me hey joe can you sort it back to the original data entry i'm going to say i don't think i could do that unless i put a numbering system which you'll notice i have right here i put this numbering system so that if i ever want to get the original order of the data back i can just simply click into the numbering system here and sort it a to z so for instance if i do that the first person should be sarah so i'm going to click anywhere a to z there's sarah i always suggest you do this with your data put some sort of numbering system you could even write id there you don't have to put number i just put number there just because that's what i wanted you can put id you could put num you could put number anything that you like as long as you just have this ordering system here you can always go back to your original data now besides that what i also want to talk about is filtering your data we've just talked about how we can organize the data in a certain way now i want to talk about how we can see certain data that we need to look at we don't need to look at all the data all the time let's say for instance my boss asks me hey joe is there a way that i can take a look at all the sales reps in our connecticut division i'm like yeah sure i could do that for you the first thing you have to do when it comes to filters is turn them on and it actually matters where you are within excel to turn on the filters let's say for instance i'm over here and i go to turn on filters now remember filters is going to be in that data tab right next to our sort so this is where i can find the filter button and if i turn it on right now if i give that a click well you'll notice whoa it can't actually be applied the reason being is because we are not nowhere near the data we're nowhere near it so it doesn't know where to apply the filters to so i'm going to click ok on this and i'm going to try again what happens if i click one below it and i click filter okay that worked actually really great but my biggest thing for you is this if you're turning on filters use your headers actually click on your headers here i don't care which one you click on it doesn't really matter you can click on division you could click on benefits you can click on hourly rate and once you click on one of those let's say i click on i don't know department i turn on my filter and there we go now we have this little drop down and i'm just going to zoom in here so you can see now this drop down if i give it a click it will house all of the different options i have within this column here so for instance if my boss wanted me to filter for sales if i click the drop down here department because that's where my sales is located i can just simply see that sales is right here but right now it's showing everything it's showing development it's showing research sales staff and i only want to see sales so what i'm going to do is i'm simply going to deselect select all by clicking on it and i'm going to select sales and give that an ok now you'll notice that all i'm viewing is sales how do i know this though well if i take a look at this you'll see that it looks a little different than my normal filter for instance you'll notice that the rest of my filters that weren't touched yet look like this but the one filter right here looks a little different that's because there's a filter turned on from the department column and you'll notice that by that little funnel another way to know that you're filtering is looking over at the rows now i haven't been in math class for a long time so i don't know but i'm pretty sure it doesn't go 1 2 3 4 5 6 7 8 11 17 19 24.
Now there's some rows being hidden here because it's filtering them out so this is another sure way to know that this data is actually being filtered and the last way to know that your data is being filtered is right up top you will see right up top here that the clear sign is now available that means that somewhere in your data data is being filtered now let's say for instance that i want to clear all of the filters in my data i would simply just click on this clear button here or if i wanted to i can click on each individual filter like this department filter and just simply select clear filter from department only okay so if i had multiple on you're more than welcome to turn them off individually or all at once now i've already did the first step my boss asked me to just do sales department but they've also asked me to do the sales department for the connecticut division so i'm going to click into division i'm going to deselect all and i'm going to select connecticut because that's all my boss wants to view at this moment and when i click ok you will now notice that my data is really really cut we have only the sales team and only the connecticut division now at this point you might be saying well this is great we can now shrink the data but what happens if your boss asks you hey joe how much are we paying hourly rate total for our sales department in connecticut well the really cool thing about excel is that if you just simply highlight just like this i'm going to click and highlight and select all the hourly rates you will notice that right below here if you just go right down here it gives you a quick analysis on your data it tells you the average of the data how many cells actually have the data in it which i selected six and the sum of the data so you can quickly give that information to your higher-ups you could say oh yeah 88.
12 without having to do any functions formulas or calculations what happens if my boss says what's the average for the gross pay once again i'm going to just select and i'm going to look right down here the average is 552 33 so as you can see this can be really beneficial because we don't even have to really do much except just select cells and it gives us the analysis data all right so how do we clear all this out well i'm going to just simply click clear and now i have all my data back if i want to turn off my filters you're more than welcome to by just clicking the filters button and it will turn them off now the next thing i want to talk to you about is not so much just sorting which we organize the data or filtering where we only wanted to see certain amounts of data but we can also do something called data mining what does that mean well it's actually similar to what it sounds like mining right if we were to mine something maybe gold we would be kicking it out of the earth bringing it out and selling it all right so it's the same concept what we're doing is we're taking this data that lives here and we're finding more information that we can pull out of it so how do we do this well let's say for instance my boss wants to know how many people we have that are full-time versus part-time and he wants to know which person is which are they part-time or full-time so that's what i'm tasked with to do that i'm gonna just create another column here in my l column so i'm on l4 and i'm simply going to put p t slash ft or the other way around whatever way you want so part time or full-time now my boss also wants me to well find the year of hire because we're going to analyze that data later on to see when we hired the most employees this is real life examples here this is what companies do to actually analyze their data they have to mine it out of the data they already have so i'm going to just click tab on my keyboard to go over to the right and i'm now going to put in here let's do year of higher i'm going to press tab one more time because my boss also said hey can you also pull them once i'm going to say yeah sure of course month of fire now i don't like the way this looks as you can see here these headers look nothing like these headers here and that's okay i can actually use a little trick called format painter now what format painter does is it just copies the way that this looks and it applies it to this so to do that the first thing i have to do is go to my home tab because that's where it's located so i'm going to go to the home tab right up here and this is where it's going to be it's one of the first tools that we see on the left format painter now that i've located it i actually have to tell the format painter what i want it to look like so i'm going to say listen i want it to look like this i wanted to have that blue line the dark blue text i'm going to click format painter you're going to see the marching ants here and i'm going to now select pt all the way through to month of higher and release and look at that i'm just going to make it a little more wide there by double clicking and now they all look the same perfect so now i can get started on working on the pt slash ft now how do i do this how do i actually pull if they were part time or full time well yeah i guess i could just go like this anything less than 40.
It's part time et tt ft no come on we would have to do that for all of these and that's a lot of data to go through i don't want to do that how about we automate it let's make excel do all the work so that we don't have to what i'm thinking here is to use an if function now what is an if function well an if function simply asks a question and whether that question is answered in true or false it'll do something or it'll do something else so let me give you a funny example and don't make fun of me because it's pretty funny let's say that i was asked in the morning hey joe did you sleep okay well if that's true i'm going to jump up and i'm going to do 10 jumping jacks all right so because i said that that was true i did wake up and i had a great night of sleep i'm gonna do jumping jacks but what happens if they ask the same question another day and i say no i didn't get enough sleep well then i'm gonna lay there so depending on whether my answer is true i'll do jumping jacks or whether the answer is false i'll just lay there that's the same thing for the if function we're simply saying that if it's true do this but if it's false do something else so let's actually do it in excel the first thing i'm going to do is click into l5 give that a nice click now to start off any function formula or calculation you have to use the universal sign of mathematics which is my good old equal sign now if you're new to functions that is okay i actually love if you were new to functions because i'm going to show you a tip that has helped me to understand functions and write them out a lot easier so i'm going to do equals i'm going to type in if for if function and when i do that you'll notice that there's a little populated list here it says if if error you don't have to worry about any of these just select the if now to select the if you're going to press tab on your keyboard right next to the queue and that's going to open up the parentheses for you so so far all we've done is this equals select the function you need if and press tab on your keyboard to open up the parentheses now most of you are probably like here's the part where i have to write out you know the logical test what is that i have to put a comma what is that i have to put value of true what is that don't worry you don't have to know all this all you have to do is this after you do equals put the function tab taupe in the parentheses i want you to head over to your formula bar right over here and i want you to select the fx okay right after you do that so equals if tab key to open the parentheses and click the fx i like to call this my help box don't tell people you learned about the help box today they're not going to know what you're talking about i just coined it the help box because it helps me out so much it's actually called the function arguments box but if you want to call the help box go right ahead now the reason i call it the help box is because it really helps us out the first thing it tells me is what the function does it says checks whether a condition is met and returns true or false basically asks a question and if it's true do something it's false do something else the next thing it tells me is what the logical test is is any value or expression that can be evaluated true or false once again it's simply saying it's asking a question true or false value if true if you click in there it tells you what it does if the value is true then it does something if the value is false it does something else so let's actually do this together let's ask our first question here the first question that i want to ask is simple i know that anything less than 40 hours worked is part time anything 40 or more is full time so i'm going to do this i'm going to click on the hours and i'm going to say that if this is greater than or equal to 40 okay so if it's greater than 40 or if it is 40 well if that's true which we know it's not right now it says false but if it were true i would want them to write ft that's what i want excel to do for me so if that's true if it's greater than or equal to 40 hours i want you to put ft in there and we can't just put f key the reason why is because well excel's a computer language so they only understand numbers and codes they don't understand regular text so we're going to have to put it in quotation marks so i'm going to do quote ft quote now the easy part comes what happens if it's false well if it's not full time the only other option is part time whoa dt well if i zoom in here you will notice that it tells me that this is false right because 35.
5 is not greater than or equal to 40. and since it's false it's going to result in a pt because if that was false it's pt but if it was true is ft now that we have this here i'm going to click ok and look at that it worked perfectly now how many of you want to sit there and write that if function over and over and over again yeah no i didn't think so so what we're going to do instead is we're going to use autofill now to activate autofill we're just going to simply move our cursor you'll notice that in every cell there's this little square box here on the right bottom right hand side all you're going to do is you're going to simply hover over just like that and give it a double click so once the cursor turns from that white crosshair cursor to the black crosshair cursor give it a double click and look at that it is working perfectly 42 that's full time 40 that's full time 35 that's part time so we have just mined whether they were part-time or full-time and this is going to be very beneficial because we need to analyze this data by part-time full-time so let's keep going with this the next thing my boss asked me to do was pull the year now this one's a little more simple as a function to do this function once again we have to start off with that equal sign and since we want the year we're going to use something called the year function and i'm going to type in year once i type in here i'm going to hit my good old tab on my keyboard next to the queue and now it's going to ask me to select where what date they want to pull the year from so i'm going to select this date because i want the year of hire so i'm going to select the date of hire i'm going to press enter and look at that now once again we don't want to keep you know putting year year year year so i'm just going to click and do a double click for my autofill and there we go perfect last but not least my boss has asked me to pull data from this original data for the month now to do this i'm just going to simply use you would think the month right tab open the parentheses click on the date press enter beautiful the problem though is this my boss tells me joe you did really great i you know this looks great but is there a way that you don't put the number here i don't want the number i actually want the full name i want this to say december i want this to say july i want this to say july i want this to say june and i'm like oof um yeah i know the month function won't work because when you use the month function it only gives you the numeric value as a result so what do we have to do here well we actually have to use a different function called the text function because we want the text of the month we don't want the numeric value so in knowing that i'm now going to actually delete this out and to select everything i'm going to use my control shift down arrow it's going to select everything to the next empty cell and i can press delete on my keyboard now i'm going to write equals text function instead of equals month remember if you don't mind having a numeric value for the month then you can use equals month but since my boss specifically wants the word december in there i have to use the text function so i'm doing equals text tab to open up that parenthesis and it's going to ask me for two things the first thing it's going to ask me for is the value so i'm going to select the date the next thing it's going to ask me for is the actual text in the way that it needs to be formatted now i want you to think of it as very simple the normal numeric format of a date it would be something like this month slash days slash year okay you all know that format that looks good some of you if you're coming from europe you might know it as date slash month slash year right but we typically use that same formatting so if i know that m is for month well i only have to put in here an m so watch what happens when i put an m in here now remember it doesn't understand english we have to use quotes so i'm going to do quote one m if you do one m it's gonna be a numeric value watch there we go but if i do two m's going to be another numeric value there we go but if i do 3 m's it's going to be abbreviated isn't that cool and if i do four m's it's going to be the full month okay and now all i would have to do double click and there we go so if you ever find yourself in that situation where your boss says thank you for doing you know equals month and you got the numeric value but is there a way that i can actually pull the word this is how you would do it and that is just a little intro to some of the data mining skills that you can use when you're trying to pull data out of data that already exists what we're going to talk about next is one of my favorite things it's actually formatting data into a table now there's so many benefits of doing it but i want to share some of my frustrations when i don't do it let's say i have this data here my first frustration is that if my boss asks me to filter this data i have to turn the filters on which we already showed if you click on here you go to data you can turn on your filters another frustration of mine is i have to create different functions and calculations at the bottom here if i want to find totals and my third frustration is that i can't really see the data so well it's all the same color it just all blends together it's hard to look at and my last frustration is this and tell me if you ever did this before let me know in the comments if you've ever done this where you scroll right you're scrolling through the data you're like what is this number again oh yeah it's the hourly rate okay what is this number oh yeah it's the gross pay all right what what is this right it happens especially when we have large amounts of data i'm going to show you a way that a table can fix all those issues i just talked about so how do we do it well the first thing we need to do is select all our data select to effect so i'm going to just click anywhere in my data anywhere you want i don't care anywhere in the middle there and you're going to use a keyboard shortcut and that keyboard shortcut is ctrl a which means select all data okay i like to think of the a for all that's how i remember that so i'm going to just click anywhere in the middle use control a and that will select everything as you can see it's all highlighted and now i can use control t and it will create a table control a a for all control t t for table now if you don't like keyboard shortcuts that's okay you're more than welcome to just click and highlight like this and then what you can do is you could go to the insert tab and in the insert tab you will see the option to create a table but you can do that i just like keyboard shortcuts because it's quicker for me but you're more than welcome to do it any way you like so once again i selected all the data i'm going to go to insert table and the first thing it's going to ask me is where's your data on your table now we've already selected it so we're good to go we could skip that but what's really important is that you explain to this that your table has headers that's really important because well your table has headers so i'm going to make sure that's check marked on and click ok all right so look at this beautiful table now let's explain all the things that i had problems with that now it's helping me with well the first thing is the filters look tables automatically come with filters that's amazing so i can start filtering and organizing my data right away the second thing is that i can see my data more clearly because every one of these rows is banded i have banded rows now which means that there is a differentiator between each row so it's a lot nicer to look at the next problem that i had was if you remember when i scroll down watch what happens to my a a b c d e f g watch what happens to all these ready when i start to scroll how cool is that what is this number oh it's the hourly rate what is this number oh it's the gross pay what is this the year of higher so i can quickly see that all of my columns become my headers which is really neat and then another cool benefit is i can actually take this table and i can turn on what's known as a total row now to do this you're going to click in your table and you're going to notice that you have a design tab now and if you click on this design tab you will see different options now if you're not selected on your table watch what happens to my table design tab it disappears you have to be selected on your table remember select to effect so i'm going to click on my table and there it is now i can turn on the totals row i can even turn off the filters if i don't need them i can choose banded columns maybe my data is going vertically i can turn on both which well that doesn't do much for me so i wouldn't turn them on i can turn on the first column which means that it's going to change the appearance of the first column or even the last column and i can turn on the totals row so if i do that you'll notice the totals row here which is pretty cool but a lot of people don't know about the totals round the totals row has so much more functionality than people know for instance my boss wants to know how much we spend total an hourly rate if i click on that little blue totals bar now i can click the drop down and i can actually do a sum the average of gross pay i can click the drop down average how many hours on average do we have 35.
9 how many departments do we have listed in this data we could do account 94.
So we can actually get so much more information to analyze through a table you can also change colors of a table with different table styles whatever you like here but turning your data into the table is really such a benefit and i would really you know do that i would go back as a best practice and take your data and turn it into a table if you're like hey you know i i don't know if i gonna use this then you can always convert it back to a range and still keep the formatting for instance if i want to turn this back to just a normal list of data not a table i can click on my design tab and convert it to a range now when you do that it's going to say are you sure you want to change this back to a list say yes and it is a list of data i promise i know it doesn't look like it but you'll notice that the totals bar is gone here if i click on it see the drop downs they don't exist i noticed that my filters automatically came off and i also noticed that when i click on my table i don't get that design tab anymore so just something that you want to keep in mind if you really don't like it you can always go back let's actually talk about something different though we've spent a lot of time on that so if you're following along you're more than welcome to click on that quick analysis tool uh we do have limited time here so if you're like joe it's hard to keep up that is okay this is recorded you can watch this again so no worries for those of you that are actually in here following along with me all right you have a question yes perfect go ahead okay so first question is how does the total row feature handle missing values and or spelling errors that's a great question so depending on what you're trying to look at let's say for instance i'm trying to find missing data right so i'm going to do a comparison here i know for a fact that all of my and let me just turn this back into a table for everyone do this there we go so let's say for instance i know my department staff this is fully filled out there's no missing data in my department staff but there are missing data somewhere else now i know that my department staff has 94 entries so if i wanted to use this to my benefit to see if there's missing data i would use the count click count okay 94 entries perfect click count whoa something's not right here 94 entries 82 entries missing data oh missing data missing data missing data and then when i start to fill it out i'm like oh yeah let me put all this in i can say all right now you'll notice this number changes to 83.
So this is a great way to actually see if any of your columns has missing data when you know for sure one of your columns you can compare it to so that's a great question that's what i would use it for the totals great question one more and then we'll save some of them but is it best to remove spaces in the headers for more advanced data analysis to say that again to remove spacers in the headers is it best to remove spaces in the headers for more advanced data analysis yes it is and if i'm getting that correctly if you mean having a space in between if i insert a space here and there's nothing here yes that is important we want all of our data to be all together the reason why is let me show you an example of this and i'm just going to turn this back into a list let me show you what this looks like i'm going to select all of it and i'm just going to change this back to just a normal looking list here let's say for instance that i have this information and i have a just a space here and my boss tells me hey listen can you sort by last name well if i do that let's say i go to data and i sort this it's not going to sort the rest of this information the reason being is because there's a space here now if you have your data where you've created a table or you've done a bunch of things to it it may work but the way that it registers this is as two separate lists of data and you never want that so a well-defined list of data is this one that has headers one that has no spaces like in my column i and one that has no missing data great question by the way any other questions you're good right now joe awesome thank you maurice i appreciate that uh so let's actually move on to the quick analysis tool if you're following along go ahead switch over to worksheet and we're gonna focus on sales figures for january 2020 real quick and i'm just going to do a quick overview of the quick analysis tool and then we'll get into pivot tables so if you were waiting for that it's going to be a lot of fun so sales figures for january 2020 you'll notice here that we have these sales figures and if i quickly want to analyze them whether i want to do that visually whether i want to do that by using i don't know some icon set or maybe i want to use a line graph or a bar graph maybe i want sparklines to track trends of data maybe i want to use some conditional formatting if you don't know what you want to use you can simply test it all out and the reason why you can do that is because if i highlight all my data here what you'll notice is that right when i do that in the bottom right hand corner you'll see this little quick analysis tool and if i click on it watch what happens it gives me a bunch of options whether i want to format whether i want to create a chart a totals column whether i want to do tables or sparklines it will do that for me so let's go through some of them formatting what does formatting mean well it just simply means the appearance of something so when i say conditional formatting it simply means based off of a condition or criteria the appearance changes so here it's saying we can have data bars and if i hover over look at my data how it changes i could do a color scale i could do an icon set i can even calculate the greater than or the top ten percent of sales i can move into the chart section and i can create a clustered column chart and all i'm doing is hovering to just see what i want gives me a little preview of each an area chart a bar chart anything that i like totals maybe i want to do a sum total at the bottom an average a count a total percentage a running total maybe i want it on my vertical maybe i want to sum there an average account so we can quickly use this to our benefit to analyze the data i can turn it into a table real quick or i can even apply sparklines whether i want to track the trend of the sales they're going through i could use bar or if we're watching stocks where there's a win loss obviously here there's no negative so it's going to not do us so much on the visual side of things and there we go so this could be really big benefit for you all you have to do once again is select your data click on it and you have all the different options with that said let us get into pivot tables pivot tables is one of my favorite things in excel this is truly the foundation of analyzing data and answering questions based off of the data let's say for instance that i have this data here pear creamer is sales for the fiscal year of 2020.
We have a lot of data here you'll notice it goes pretty far down and my boss starts asking me questions about this data the first thing she asks me is this she says to me joe how much should we make total okay simple enough right we could just simply select all the data using control shift down for sales and i could say oh we made five five 1.5558 point six seven and i can tell that total that's great but then she asks me the next question she says to me well joe how much did each sales person make okay i could think of a couple waves maybe a subtotal maybe i could filter and figure each one maybe i could sort it but then she says how much did each sales person make in each region okay um how much of each sales person making each type of ice cream uh how much of each sales person make in each year okay now i'm feeling a little overwhelmed even though i have excel skills i don't have them that fast i'm not a computer so i can't answer all those questions and she wants those questions answered right now so i'm going to show you a way that we can use pivot tables to analyze the data and answer questions instantly the first thing we have to do is understand what a pivot table is well the word pivot essentially means to turn so we're taking this data turning it into a table with interchangeable rows and columns and those interchangeable rows and columns will help us out in answering these questions so the first thing you're going to do is select all your data i'm going to use control a you can click and drag whatever way works for you if i like control a after that i'm going to go to the insert tab and i'm going to select in my answer tab the first option to the left which is pivot table i'm going to give that a click i'm going to pop up this dialog box it's going to say hey listen choose the data that you want to analyze now we've already done that you can tell by the marching ants the next thing it's going to ask us is where do you want to put this pivot table now i always suggest putting it in a new worksheet the reason why i say that is because if you put it in an existing worksheet you want to make sure that you don't skew your other data if it's already existing there so my best practice for you is to just put it in a new worksheet once i do that i'm going to click ok and yay we have our pivot table so it looks a little different though there's a lot of things happening here the first thing is we have a blank pivot table so that's our blank pivot table we also have all the way to our right here something known as pivot table fields this is where we can actually start to select our data to put it into the pivot table so i'm going to we could just call this pivot table fields what we have after this is two new tabs we have an analyze tab and a design tab where we can use these different commands to manipulate or modify our table we have a lot going on in a pivot table but i don't want you to feel overwhelmed because it's actually pretty simple to use a pivot table so let me explain the first thing you're going to notice here is that we have all of our different headers you'll notice in our original data we had the month the year type sales person region sales and units and here we have the month the region sales sales person type units here we have them all here they're just all plugged up into these fields now you'll also notice down below we have four different rectangular boxes and that is different areas where we can put our information now i'm going to make it simple because microsoft actually was really amazing when they did this we have the column section here and those are columns going across we also have the rows section and those are simply rows going down and then we have the values section now the value section is really amazing because the only thing that really goes here is numeric values you'll even see the mathematical sign for number so the only thing that's going in our values is anything that is a numeric value and that's going to go right here so essentially what is it well it's a table for instance if i decided to put let's say my sales person in my columns well that would go across the columns i would have their names going across and if i put the sales person in my rows well essentially that would be just me putting their names going down the rows and if i put my sales person which is not a numeric value into my values well that's just going to give me a count so it's not going to really do much for me so now that i know this a little better what we're going to do is we're going to actually use it now the last thing we have here is the filters and it actually works quite the same as a normal filter if i put my sales person in my filters well it'll just have a little drop down with all my sales people so let's actually answer the first question my boss asked me my boss asked me how much did we make total in sales and i'm going to just take my sales and i'm going to click and drag it because my sales is a number guess where it's going here and look at that we now have our total so we just answered the first question within seconds but then my boss asked me how much should each sales person make in sales so i already know what i need i need salesperson and sales now we already have sales and the numbers so that's good we're already we already know where one of these go but now we have to choose where does salesperson go does it go here or does it go here or here here here here where does it go i'm going to tell you a secret who cares yeah who cares i don't care where it goes if i go to column watch bishop made 531.
Poland made 450 watts made 575. if i put my sales person rose bishop made 531 pulling me 450 watson made 575. it's all the same data if i put it into my filters bishop made 531 colon made 450 and watson made 575. so don't stress on where to put this as long as you always remember to put your numbers in the values box it doesn't matter where you put your other fields so we've answered that second question now what i'm going to do is i'm going to clear this out there's two ways to clear it out you can either just deselect this little checkbox or you can click and drag it onto the actual spreadsheet and release sometimes i like to play a little game how far can i go i'm just kidding i don't do that so now what we're going to do is answer the second question the second question is this how much should each sales person make in each region so now i already know i need sales salesperson and region so where does my sales go well i already know it's a number it goes to values where do my regions and sales person go well follow my tip who cares i could do this looks good bishop made 134 in central i could do this looks good bishop made 134 in central i could have done this bishop made 134 in central i could have done this harder to read but bishop still made 134 in central so you'll know just play around with it you'll know when you mess up you'll do something like this oops that makes no sense so you'll know as long as you put numbers here it doesn't matter where you put these just play around see what you like better and i personally i like this better i like my region going down the rows and i like my sales person going across the columns so that's the great benefit of a pivot table because we can quickly analyze this data and ask questions and answer them quickly now let's say we did this we created the pivot table it looks beautiful looks good we have all this data now in a pivot table and my boss says to me this joe is there any way to make it numeric value yeah there actually is just simply select the numbers and turn it into a currency by clicking currency from your home tab and when i click on that typically when we're dealing with such large numbers we'll decrease the decimal and there we go now the next thing my boss asks me is how much did each sales person make and each type of ice cream so i'm going to remove the region and i'll swap it out for type and now we quickly have how much each sales person made for each type of ice cream now the last thing i'm going to leave you with before i turn it back over to marissa is this the biggest benefit of a pivot table which a lot of people don't know about is drilling down on data we've now supplied the results the answers to analyzing the question right my boss said how much did each salesperson make in each type of ice cream we've given him that but now she says to me i don't remember how did bishop make 263 000 in ice cream sales how where do we have that information watch how cool this is ready double click on it and it will pull into a new sheet all that data all of bishop sales for ice cream isn't that amazing and it turns it into a table right away so you can quickly start analyzing it once again maybe she says watson only made 83 000 how double click on it only brings watson's frozen yogurt sales isn't that amazing so we can drill down on the data and then name it and i would just name this one watson frozen and then i would have named this one bishop ice cream so we can drill down on that data just by clicking on it we made a watson made a grand total of 575 how double click on it gives you all of watson's sales with that said i hope you got a lot out of this thank you so much for being here uh feel free to subscribe like the video also i will turn this over to marissa if you want to view this video i know it's going to be on our page here and if you thought you know because i had to kind of squeeze it into time no worries this is recorded so you can always go back and watch it and you all have the exercise file which is in the description if you click more you'll have the link to that so feel free to download that re-watch the video and follow along stay safe stay healthy thank you so much i'm going to turn it over to marissa and then i'll probably be answering questions later anyway so thank you all really quickly joe i do have one question um what version of excel were you using and are there any large noticeable differences between excel 2019 and excel 2016 when it comes to data analysis great question so i was using 365 which is at the moment housing 2019 so you know i always recommend to get on to 365 but if if not if you're still using 2016 that's fine everything i've shown you today is also applicable in 2016.
So you have all the tools the same tools that i did and there's not a difference on how i went to certain things everything's in the same place everything is exactly the same the only thing that is a little different is my contextual tabs which if i click into the table you'll notice mine says table design yours may just say design in 2016. so that's the only difference it's still the same tools.
Here great question and thank you so much for that amazing thank you and then i'll stop sharing my screen for you marissa go ahead okay well thank you so much joe that was amazing i'm sure you guys can all tell he's an expert he's awesome um really quickly just before we end here like i said the recording to this session will be on off site if you know somebody that missed it or you had to drop off early you can access the recording on our online community off site by learn it it's completely free to join and some of the other benefits of offsite is you can get live instructor help from instructors like joe who are constantly checking in there and answering questions whether you have excel power bi professional development questions you can ask them an off-site we also offer discounts in there when we offer any discounts and one really really cool thing is we are almost at a thousand members in offsite and once we reach a thousand members we will be doing a fun giveaway so definitely join us have your friends join have your co-workers join so you can be a part of the giveaway the link is right here off site by learn it and here is our schedule for the rest of february so as i said february is all about data analysis next week we are doing storytelling with data using data to tell a story and again if you come to every single one you will get a data analysis badge thank you guys so much for joining thank you thank you joe so much for hosting today and i know this is a little bit different so i'm not sure if we're really going to be able to answer many questions right now but like i said join off site and you'll be able to ask any questions thank you everybody you.
Now there's some rows being hidden here because it's filtering them out so this is another sure way to know that this data is actually being filtered and the last way to know that your data is being filtered is right up top you will see right up top here that the clear sign is now available that means that somewhere in your data data is being filtered now let's say for instance that i want to clear all of the filters in my data i would simply just click on this clear button here or if i wanted to i can click on each individual filter like this department filter and just simply select clear filter from department only okay so if i had multiple on you're more than welcome to turn them off individually or all at once now i've already did the first step my boss asked me to just do sales department but they've also asked me to do the sales department for the connecticut division so i'm going to click into division i'm going to deselect all and i'm going to select connecticut because that's all my boss wants to view at this moment and when i click ok you will now notice that my data is really really cut we have only the sales team and only the connecticut division now at this point you might be saying well this is great we can now shrink the data but what happens if your boss asks you hey joe how much are we paying hourly rate total for our sales department in connecticut well the really cool thing about excel is that if you just simply highlight just like this i'm going to click and highlight and select all the hourly rates you will notice that right below here if you just go right down here it gives you a quick analysis on your data it tells you the average of the data how many cells actually have the data in it which i selected six and the sum of the data so you can quickly give that information to your higher-ups you could say oh yeah 88.
12 without having to do any functions formulas or calculations what happens if my boss says what's the average for the gross pay once again i'm going to just select and i'm going to look right down here the average is 552 33 so as you can see this can be really beneficial because we don't even have to really do much except just select cells and it gives us the analysis data all right so how do we clear all this out well i'm going to just simply click clear and now i have all my data back if i want to turn off my filters you're more than welcome to by just clicking the filters button and it will turn them off now the next thing i want to talk to you about is not so much just sorting which we organize the data or filtering where we only wanted to see certain amounts of data but we can also do something called data mining what does that mean well it's actually similar to what it sounds like mining right if we were to mine something maybe gold we would be kicking it out of the earth bringing it out and selling it all right so it's the same concept what we're doing is we're taking this data that lives here and we're finding more information that we can pull out of it so how do we do this well let's say for instance my boss wants to know how many people we have that are full-time versus part-time and he wants to know which person is which are they part-time or full-time so that's what i'm tasked with to do that i'm gonna just create another column here in my l column so i'm on l4 and i'm simply going to put p t slash ft or the other way around whatever way you want so part time or full-time now my boss also wants me to well find the year of hire because we're going to analyze that data later on to see when we hired the most employees this is real life examples here this is what companies do to actually analyze their data they have to mine it out of the data they already have so i'm going to just click tab on my keyboard to go over to the right and i'm now going to put in here let's do year of higher i'm going to press tab one more time because my boss also said hey can you also pull them once i'm going to say yeah sure of course month of fire now i don't like the way this looks as you can see here these headers look nothing like these headers here and that's okay i can actually use a little trick called format painter now what format painter does is it just copies the way that this looks and it applies it to this so to do that the first thing i have to do is go to my home tab because that's where it's located so i'm going to go to the home tab right up here and this is where it's going to be it's one of the first tools that we see on the left format painter now that i've located it i actually have to tell the format painter what i want it to look like so i'm going to say listen i want it to look like this i wanted to have that blue line the dark blue text i'm going to click format painter you're going to see the marching ants here and i'm going to now select pt all the way through to month of higher and release and look at that i'm just going to make it a little more wide there by double clicking and now they all look the same perfect so now i can get started on working on the pt slash ft now how do i do this how do i actually pull if they were part time or full time well yeah i guess i could just go like this anything less than 40.
It's part time et tt ft no come on we would have to do that for all of these and that's a lot of data to go through i don't want to do that how about we automate it let's make excel do all the work so that we don't have to what i'm thinking here is to use an if function now what is an if function well an if function simply asks a question and whether that question is answered in true or false it'll do something or it'll do something else so let me give you a funny example and don't make fun of me because it's pretty funny let's say that i was asked in the morning hey joe did you sleep okay well if that's true i'm going to jump up and i'm going to do 10 jumping jacks all right so because i said that that was true i did wake up and i had a great night of sleep i'm gonna do jumping jacks but what happens if they ask the same question another day and i say no i didn't get enough sleep well then i'm gonna lay there so depending on whether my answer is true i'll do jumping jacks or whether the answer is false i'll just lay there that's the same thing for the if function we're simply saying that if it's true do this but if it's false do something else so let's actually do it in excel the first thing i'm going to do is click into l5 give that a nice click now to start off any function formula or calculation you have to use the universal sign of mathematics which is my good old equal sign now if you're new to functions that is okay i actually love if you were new to functions because i'm going to show you a tip that has helped me to understand functions and write them out a lot easier so i'm going to do equals i'm going to type in if for if function and when i do that you'll notice that there's a little populated list here it says if if error you don't have to worry about any of these just select the if now to select the if you're going to press tab on your keyboard right next to the queue and that's going to open up the parentheses for you so so far all we've done is this equals select the function you need if and press tab on your keyboard to open up the parentheses now most of you are probably like here's the part where i have to write out you know the logical test what is that i have to put a comma what is that i have to put value of true what is that don't worry you don't have to know all this all you have to do is this after you do equals put the function tab taupe in the parentheses i want you to head over to your formula bar right over here and i want you to select the fx okay right after you do that so equals if tab key to open the parentheses and click the fx i like to call this my help box don't tell people you learned about the help box today they're not going to know what you're talking about i just coined it the help box because it helps me out so much it's actually called the function arguments box but if you want to call the help box go right ahead now the reason i call it the help box is because it really helps us out the first thing it tells me is what the function does it says checks whether a condition is met and returns true or false basically asks a question and if it's true do something it's false do something else the next thing it tells me is what the logical test is is any value or expression that can be evaluated true or false once again it's simply saying it's asking a question true or false value if true if you click in there it tells you what it does if the value is true then it does something if the value is false it does something else so let's actually do this together let's ask our first question here the first question that i want to ask is simple i know that anything less than 40 hours worked is part time anything 40 or more is full time so i'm going to do this i'm going to click on the hours and i'm going to say that if this is greater than or equal to 40 okay so if it's greater than 40 or if it is 40 well if that's true which we know it's not right now it says false but if it were true i would want them to write ft that's what i want excel to do for me so if that's true if it's greater than or equal to 40 hours i want you to put ft in there and we can't just put f key the reason why is because well excel's a computer language so they only understand numbers and codes they don't understand regular text so we're going to have to put it in quotation marks so i'm going to do quote ft quote now the easy part comes what happens if it's false well if it's not full time the only other option is part time whoa dt well if i zoom in here you will notice that it tells me that this is false right because 35.
5 is not greater than or equal to 40. and since it's false it's going to result in a pt because if that was false it's pt but if it was true is ft now that we have this here i'm going to click ok and look at that it worked perfectly now how many of you want to sit there and write that if function over and over and over again yeah no i didn't think so so what we're going to do instead is we're going to use autofill now to activate autofill we're just going to simply move our cursor you'll notice that in every cell there's this little square box here on the right bottom right hand side all you're going to do is you're going to simply hover over just like that and give it a double click so once the cursor turns from that white crosshair cursor to the black crosshair cursor give it a double click and look at that it is working perfectly 42 that's full time 40 that's full time 35 that's part time so we have just mined whether they were part-time or full-time and this is going to be very beneficial because we need to analyze this data by part-time full-time so let's keep going with this the next thing my boss asked me to do was pull the year now this one's a little more simple as a function to do this function once again we have to start off with that equal sign and since we want the year we're going to use something called the year function and i'm going to type in year once i type in here i'm going to hit my good old tab on my keyboard next to the queue and now it's going to ask me to select where what date they want to pull the year from so i'm going to select this date because i want the year of hire so i'm going to select the date of hire i'm going to press enter and look at that now once again we don't want to keep you know putting year year year year so i'm just going to click and do a double click for my autofill and there we go perfect last but not least my boss has asked me to pull data from this original data for the month now to do this i'm just going to simply use you would think the month right tab open the parentheses click on the date press enter beautiful the problem though is this my boss tells me joe you did really great i you know this looks great but is there a way that you don't put the number here i don't want the number i actually want the full name i want this to say december i want this to say july i want this to say july i want this to say june and i'm like oof um yeah i know the month function won't work because when you use the month function it only gives you the numeric value as a result so what do we have to do here well we actually have to use a different function called the text function because we want the text of the month we don't want the numeric value so in knowing that i'm now going to actually delete this out and to select everything i'm going to use my control shift down arrow it's going to select everything to the next empty cell and i can press delete on my keyboard now i'm going to write equals text function instead of equals month remember if you don't mind having a numeric value for the month then you can use equals month but since my boss specifically wants the word december in there i have to use the text function so i'm doing equals text tab to open up that parenthesis and it's going to ask me for two things the first thing it's going to ask me for is the value so i'm going to select the date the next thing it's going to ask me for is the actual text in the way that it needs to be formatted now i want you to think of it as very simple the normal numeric format of a date it would be something like this month slash days slash year okay you all know that format that looks good some of you if you're coming from europe you might know it as date slash month slash year right but we typically use that same formatting so if i know that m is for month well i only have to put in here an m so watch what happens when i put an m in here now remember it doesn't understand english we have to use quotes so i'm going to do quote one m if you do one m it's gonna be a numeric value watch there we go but if i do two m's going to be another numeric value there we go but if i do 3 m's it's going to be abbreviated isn't that cool and if i do four m's it's going to be the full month okay and now all i would have to do double click and there we go so if you ever find yourself in that situation where your boss says thank you for doing you know equals month and you got the numeric value but is there a way that i can actually pull the word this is how you would do it and that is just a little intro to some of the data mining skills that you can use when you're trying to pull data out of data that already exists what we're going to talk about next is one of my favorite things it's actually formatting data into a table now there's so many benefits of doing it but i want to share some of my frustrations when i don't do it let's say i have this data here my first frustration is that if my boss asks me to filter this data i have to turn the filters on which we already showed if you click on here you go to data you can turn on your filters another frustration of mine is i have to create different functions and calculations at the bottom here if i want to find totals and my third frustration is that i can't really see the data so well it's all the same color it just all blends together it's hard to look at and my last frustration is this and tell me if you ever did this before let me know in the comments if you've ever done this where you scroll right you're scrolling through the data you're like what is this number again oh yeah it's the hourly rate okay what is this number oh yeah it's the gross pay all right what what is this right it happens especially when we have large amounts of data i'm going to show you a way that a table can fix all those issues i just talked about so how do we do it well the first thing we need to do is select all our data select to effect so i'm going to just click anywhere in my data anywhere you want i don't care anywhere in the middle there and you're going to use a keyboard shortcut and that keyboard shortcut is ctrl a which means select all data okay i like to think of the a for all that's how i remember that so i'm going to just click anywhere in the middle use control a and that will select everything as you can see it's all highlighted and now i can use control t and it will create a table control a a for all control t t for table now if you don't like keyboard shortcuts that's okay you're more than welcome to just click and highlight like this and then what you can do is you could go to the insert tab and in the insert tab you will see the option to create a table but you can do that i just like keyboard shortcuts because it's quicker for me but you're more than welcome to do it any way you like so once again i selected all the data i'm going to go to insert table and the first thing it's going to ask me is where's your data on your table now we've already selected it so we're good to go we could skip that but what's really important is that you explain to this that your table has headers that's really important because well your table has headers so i'm going to make sure that's check marked on and click ok all right so look at this beautiful table now let's explain all the things that i had problems with that now it's helping me with well the first thing is the filters look tables automatically come with filters that's amazing so i can start filtering and organizing my data right away the second thing is that i can see my data more clearly because every one of these rows is banded i have banded rows now which means that there is a differentiator between each row so it's a lot nicer to look at the next problem that i had was if you remember when i scroll down watch what happens to my a a b c d e f g watch what happens to all these ready when i start to scroll how cool is that what is this number oh it's the hourly rate what is this number oh it's the gross pay what is this the year of higher so i can quickly see that all of my columns become my headers which is really neat and then another cool benefit is i can actually take this table and i can turn on what's known as a total row now to do this you're going to click in your table and you're going to notice that you have a design tab now and if you click on this design tab you will see different options now if you're not selected on your table watch what happens to my table design tab it disappears you have to be selected on your table remember select to effect so i'm going to click on my table and there it is now i can turn on the totals row i can even turn off the filters if i don't need them i can choose banded columns maybe my data is going vertically i can turn on both which well that doesn't do much for me so i wouldn't turn them on i can turn on the first column which means that it's going to change the appearance of the first column or even the last column and i can turn on the totals row so if i do that you'll notice the totals row here which is pretty cool but a lot of people don't know about the totals round the totals row has so much more functionality than people know for instance my boss wants to know how much we spend total an hourly rate if i click on that little blue totals bar now i can click the drop down and i can actually do a sum the average of gross pay i can click the drop down average how many hours on average do we have 35.
9 how many departments do we have listed in this data we could do account 94.
So we can actually get so much more information to analyze through a table you can also change colors of a table with different table styles whatever you like here but turning your data into the table is really such a benefit and i would really you know do that i would go back as a best practice and take your data and turn it into a table if you're like hey you know i i don't know if i gonna use this then you can always convert it back to a range and still keep the formatting for instance if i want to turn this back to just a normal list of data not a table i can click on my design tab and convert it to a range now when you do that it's going to say are you sure you want to change this back to a list say yes and it is a list of data i promise i know it doesn't look like it but you'll notice that the totals bar is gone here if i click on it see the drop downs they don't exist i noticed that my filters automatically came off and i also noticed that when i click on my table i don't get that design tab anymore so just something that you want to keep in mind if you really don't like it you can always go back let's actually talk about something different though we've spent a lot of time on that so if you're following along you're more than welcome to click on that quick analysis tool uh we do have limited time here so if you're like joe it's hard to keep up that is okay this is recorded you can watch this again so no worries for those of you that are actually in here following along with me all right you have a question yes perfect go ahead okay so first question is how does the total row feature handle missing values and or spelling errors that's a great question so depending on what you're trying to look at let's say for instance i'm trying to find missing data right so i'm going to do a comparison here i know for a fact that all of my and let me just turn this back into a table for everyone do this there we go so let's say for instance i know my department staff this is fully filled out there's no missing data in my department staff but there are missing data somewhere else now i know that my department staff has 94 entries so if i wanted to use this to my benefit to see if there's missing data i would use the count click count okay 94 entries perfect click count whoa something's not right here 94 entries 82 entries missing data oh missing data missing data missing data and then when i start to fill it out i'm like oh yeah let me put all this in i can say all right now you'll notice this number changes to 83.
So this is a great way to actually see if any of your columns has missing data when you know for sure one of your columns you can compare it to so that's a great question that's what i would use it for the totals great question one more and then we'll save some of them but is it best to remove spaces in the headers for more advanced data analysis to say that again to remove spacers in the headers is it best to remove spaces in the headers for more advanced data analysis yes it is and if i'm getting that correctly if you mean having a space in between if i insert a space here and there's nothing here yes that is important we want all of our data to be all together the reason why is let me show you an example of this and i'm just going to turn this back into a list let me show you what this looks like i'm going to select all of it and i'm just going to change this back to just a normal looking list here let's say for instance that i have this information and i have a just a space here and my boss tells me hey listen can you sort by last name well if i do that let's say i go to data and i sort this it's not going to sort the rest of this information the reason being is because there's a space here now if you have your data where you've created a table or you've done a bunch of things to it it may work but the way that it registers this is as two separate lists of data and you never want that so a well-defined list of data is this one that has headers one that has no spaces like in my column i and one that has no missing data great question by the way any other questions you're good right now joe awesome thank you maurice i appreciate that uh so let's actually move on to the quick analysis tool if you're following along go ahead switch over to worksheet and we're gonna focus on sales figures for january 2020 real quick and i'm just going to do a quick overview of the quick analysis tool and then we'll get into pivot tables so if you were waiting for that it's going to be a lot of fun so sales figures for january 2020 you'll notice here that we have these sales figures and if i quickly want to analyze them whether i want to do that visually whether i want to do that by using i don't know some icon set or maybe i want to use a line graph or a bar graph maybe i want sparklines to track trends of data maybe i want to use some conditional formatting if you don't know what you want to use you can simply test it all out and the reason why you can do that is because if i highlight all my data here what you'll notice is that right when i do that in the bottom right hand corner you'll see this little quick analysis tool and if i click on it watch what happens it gives me a bunch of options whether i want to format whether i want to create a chart a totals column whether i want to do tables or sparklines it will do that for me so let's go through some of them formatting what does formatting mean well it just simply means the appearance of something so when i say conditional formatting it simply means based off of a condition or criteria the appearance changes so here it's saying we can have data bars and if i hover over look at my data how it changes i could do a color scale i could do an icon set i can even calculate the greater than or the top ten percent of sales i can move into the chart section and i can create a clustered column chart and all i'm doing is hovering to just see what i want gives me a little preview of each an area chart a bar chart anything that i like totals maybe i want to do a sum total at the bottom an average a count a total percentage a running total maybe i want it on my vertical maybe i want to sum there an average account so we can quickly use this to our benefit to analyze the data i can turn it into a table real quick or i can even apply sparklines whether i want to track the trend of the sales they're going through i could use bar or if we're watching stocks where there's a win loss obviously here there's no negative so it's going to not do us so much on the visual side of things and there we go so this could be really big benefit for you all you have to do once again is select your data click on it and you have all the different options with that said let us get into pivot tables pivot tables is one of my favorite things in excel this is truly the foundation of analyzing data and answering questions based off of the data let's say for instance that i have this data here pear creamer is sales for the fiscal year of 2020.
We have a lot of data here you'll notice it goes pretty far down and my boss starts asking me questions about this data the first thing she asks me is this she says to me joe how much should we make total okay simple enough right we could just simply select all the data using control shift down for sales and i could say oh we made five five 1.5558 point six seven and i can tell that total that's great but then she asks me the next question she says to me well joe how much did each sales person make okay i could think of a couple waves maybe a subtotal maybe i could filter and figure each one maybe i could sort it but then she says how much did each sales person make in each region okay um how much of each sales person making each type of ice cream uh how much of each sales person make in each year okay now i'm feeling a little overwhelmed even though i have excel skills i don't have them that fast i'm not a computer so i can't answer all those questions and she wants those questions answered right now so i'm going to show you a way that we can use pivot tables to analyze the data and answer questions instantly the first thing we have to do is understand what a pivot table is well the word pivot essentially means to turn so we're taking this data turning it into a table with interchangeable rows and columns and those interchangeable rows and columns will help us out in answering these questions so the first thing you're going to do is select all your data i'm going to use control a you can click and drag whatever way works for you if i like control a after that i'm going to go to the insert tab and i'm going to select in my answer tab the first option to the left which is pivot table i'm going to give that a click i'm going to pop up this dialog box it's going to say hey listen choose the data that you want to analyze now we've already done that you can tell by the marching ants the next thing it's going to ask us is where do you want to put this pivot table now i always suggest putting it in a new worksheet the reason why i say that is because if you put it in an existing worksheet you want to make sure that you don't skew your other data if it's already existing there so my best practice for you is to just put it in a new worksheet once i do that i'm going to click ok and yay we have our pivot table so it looks a little different though there's a lot of things happening here the first thing is we have a blank pivot table so that's our blank pivot table we also have all the way to our right here something known as pivot table fields this is where we can actually start to select our data to put it into the pivot table so i'm going to we could just call this pivot table fields what we have after this is two new tabs we have an analyze tab and a design tab where we can use these different commands to manipulate or modify our table we have a lot going on in a pivot table but i don't want you to feel overwhelmed because it's actually pretty simple to use a pivot table so let me explain the first thing you're going to notice here is that we have all of our different headers you'll notice in our original data we had the month the year type sales person region sales and units and here we have the month the region sales sales person type units here we have them all here they're just all plugged up into these fields now you'll also notice down below we have four different rectangular boxes and that is different areas where we can put our information now i'm going to make it simple because microsoft actually was really amazing when they did this we have the column section here and those are columns going across we also have the rows section and those are simply rows going down and then we have the values section now the value section is really amazing because the only thing that really goes here is numeric values you'll even see the mathematical sign for number so the only thing that's going in our values is anything that is a numeric value and that's going to go right here so essentially what is it well it's a table for instance if i decided to put let's say my sales person in my columns well that would go across the columns i would have their names going across and if i put the sales person in my rows well essentially that would be just me putting their names going down the rows and if i put my sales person which is not a numeric value into my values well that's just going to give me a count so it's not going to really do much for me so now that i know this a little better what we're going to do is we're going to actually use it now the last thing we have here is the filters and it actually works quite the same as a normal filter if i put my sales person in my filters well it'll just have a little drop down with all my sales people so let's actually answer the first question my boss asked me my boss asked me how much did we make total in sales and i'm going to just take my sales and i'm going to click and drag it because my sales is a number guess where it's going here and look at that we now have our total so we just answered the first question within seconds but then my boss asked me how much should each sales person make in sales so i already know what i need i need salesperson and sales now we already have sales and the numbers so that's good we're already we already know where one of these go but now we have to choose where does salesperson go does it go here or does it go here or here here here here where does it go i'm going to tell you a secret who cares yeah who cares i don't care where it goes if i go to column watch bishop made 531.
Poland made 450 watts made 575. if i put my sales person rose bishop made 531 pulling me 450 watson made 575. it's all the same data if i put it into my filters bishop made 531 colon made 450 and watson made 575. so don't stress on where to put this as long as you always remember to put your numbers in the values box it doesn't matter where you put your other fields so we've answered that second question now what i'm going to do is i'm going to clear this out there's two ways to clear it out you can either just deselect this little checkbox or you can click and drag it onto the actual spreadsheet and release sometimes i like to play a little game how far can i go i'm just kidding i don't do that so now what we're going to do is answer the second question the second question is this how much should each sales person make in each region so now i already know i need sales salesperson and region so where does my sales go well i already know it's a number it goes to values where do my regions and sales person go well follow my tip who cares i could do this looks good bishop made 134 in central i could do this looks good bishop made 134 in central i could have done this bishop made 134 in central i could have done this harder to read but bishop still made 134 in central so you'll know just play around with it you'll know when you mess up you'll do something like this oops that makes no sense so you'll know as long as you put numbers here it doesn't matter where you put these just play around see what you like better and i personally i like this better i like my region going down the rows and i like my sales person going across the columns so that's the great benefit of a pivot table because we can quickly analyze this data and ask questions and answer them quickly now let's say we did this we created the pivot table it looks beautiful looks good we have all this data now in a pivot table and my boss says to me this joe is there any way to make it numeric value yeah there actually is just simply select the numbers and turn it into a currency by clicking currency from your home tab and when i click on that typically when we're dealing with such large numbers we'll decrease the decimal and there we go now the next thing my boss asks me is how much did each sales person make and each type of ice cream so i'm going to remove the region and i'll swap it out for type and now we quickly have how much each sales person made for each type of ice cream now the last thing i'm going to leave you with before i turn it back over to marissa is this the biggest benefit of a pivot table which a lot of people don't know about is drilling down on data we've now supplied the results the answers to analyzing the question right my boss said how much did each salesperson make in each type of ice cream we've given him that but now she says to me i don't remember how did bishop make 263 000 in ice cream sales how where do we have that information watch how cool this is ready double click on it and it will pull into a new sheet all that data all of bishop sales for ice cream isn't that amazing and it turns it into a table right away so you can quickly start analyzing it once again maybe she says watson only made 83 000 how double click on it only brings watson's frozen yogurt sales isn't that amazing so we can drill down on the data and then name it and i would just name this one watson frozen and then i would have named this one bishop ice cream so we can drill down on that data just by clicking on it we made a watson made a grand total of 575 how double click on it gives you all of watson's sales with that said i hope you got a lot out of this thank you so much for being here uh feel free to subscribe like the video also i will turn this over to marissa if you want to view this video i know it's going to be on our page here and if you thought you know because i had to kind of squeeze it into time no worries this is recorded so you can always go back and watch it and you all have the exercise file which is in the description if you click more you'll have the link to that so feel free to download that re-watch the video and follow along stay safe stay healthy thank you so much i'm going to turn it over to marissa and then i'll probably be answering questions later anyway so thank you all really quickly joe i do have one question um what version of excel were you using and are there any large noticeable differences between excel 2019 and excel 2016 when it comes to data analysis great question so i was using 365 which is at the moment housing 2019 so you know i always recommend to get on to 365 but if if not if you're still using 2016 that's fine everything i've shown you today is also applicable in 2016.
So you have all the tools the same tools that i did and there's not a difference on how i went to certain things everything's in the same place everything is exactly the same the only thing that is a little different is my contextual tabs which if i click into the table you'll notice mine says table design yours may just say design in 2016. so that's the only difference it's still the same tools.
Here great question and thank you so much for that amazing thank you and then i'll stop sharing my screen for you marissa go ahead okay well thank you so much joe that was amazing i'm sure you guys can all tell he's an expert he's awesome um really quickly just before we end here like i said the recording to this session will be on off site if you know somebody that missed it or you had to drop off early you can access the recording on our online community off site by learn it it's completely free to join and some of the other benefits of offsite is you can get live instructor help from instructors like joe who are constantly checking in there and answering questions whether you have excel power bi professional development questions you can ask them an off-site we also offer discounts in there when we offer any discounts and one really really cool thing is we are almost at a thousand members in offsite and once we reach a thousand members we will be doing a fun giveaway so definitely join us have your friends join have your co-workers join so you can be a part of the giveaway the link is right here off site by learn it and here is our schedule for the rest of february so as i said february is all about data analysis next week we are doing storytelling with data using data to tell a story and again if you come to every single one you will get a data analysis badge thank you guys so much for joining thank you thank you joe so much for hosting today and i know this is a little bit different so i'm not sure if we're really going to be able to answer many questions right now but like i said join off site and you'll be able to ask any questions thank you everybody you.