 statistics and excel histograms and scatter plots with population data get ready taking a deep breath holding it in for 10 seconds and looking forward to a smooth soothing excel first a word from our sponsor yeah actually we're sponsoring ourselves on this one because apparently the merchandisers they don't want to be seen with us but but that's okay whatever because our merchandise is is better than their stupid stuff anyways like our trust me I'm an accountant product line yeah it's paramount that you let people know that you're an accountant because apparently we're among the only ones equipped with the number crunching skills to answer society's current deep complex and nuanced questions if you would like a commercial free experience consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com here we are in excel if you don't have this workbook that's okay because we'll basically build this from a blank worksheet so you can start with that however if you do have access to this workbook there's three tabs down below example practice blank example in essence answer key practice tab having pre-formatted cells within it so you could focus in on the core concepts of the practice problem blank tab only having the data in it so that we can practice formatting the cells within excel as we work through the practice problem let's go to the example tab to get an idea of what we will be doing we're going to sort our data into a table format we're then going to be creating multiple histograms from the data in our table which is related to population and then we'll be combining some of that information to get our scatter plots so let's go on over to the blank tab on the right if you don't have this data you can look up similar data related to population or i believe we've retrieved this from kaggle that's kaggle.com so you can look for the data set there and pull it in and then we can work with this information so i'm going to delete that and now i'm going to do the first thing i do every time with our data set i'm going to first format the underlying formatting that i would like on the excel sheet so i'm going to select the entire sheet with the triangle right click on the selected area and format the entire sheet i like to go to currency negative numbers bracketed and red remove the dollar sign and if i scan the data you'll notice there's no decimals in it so i can say let's go ahead and remove the decimals and so there's my baseline formatting i'm going to make it bold home tab font group and bold and there we have that i'm going to hold control scroll in a bit so now i'm currently at 190 percent on the scroll in i'm then going to work with my headers so i'm going to select the header in a one drag over to j one notice that nothing is in column k it's just that this one's hanging over into column k so what i'm going to do is go up top and go to the home tab we can go to the alignment we might want to center these and we might want to wrap them so that we can see them so there we have that now i'm going to insert a table around the entire data set now note that you have blank data over here so sometimes that can kind of throw off where the table will be put in place but i believe if we just select any cell the table will properly but be put in place so let's go to a insert tab table group and insert the table dancing ants around a one to j 79 i believe that says which looks right so we're going to say let's go ahead and insert that so once done sometimes you might want to to take your tabs up top i'm going to select all of them and make them a little bit smaller right so because what it did is it widened out the titles so that you could see the whole title without wrapping it but we entered the wrapping so i'm going to select the entire thing and and then i'm going to put my cursor in between any of these cells and make it smaller and that will make all of them the same size now you may not need all of them the same length of smallness so you could adjust them as you so choose so this one for example we might make that a little bit smaller even still and so there we have our data so our data uh we have the the name we have the population we've got the age less than 18 age 18 to 34 35 uh to 65 65 plus number of households families and number of households uh with 100k plus all right so based on this data let's first just make our histograms with this data i can take any piece of this data we could sort the data it's currently sorted by the way a to z by uh location and we could sort it by population so i can sort it by population and of course i can see uh get an idea of the data by population i could sort it you know by by age less than 18 and so on and so forth and then we can go through here and we can make a histogram from each of these individual data sets if we so choose so we can do that i'm going to select for example this data set the total population and i can do that by selecting the dropdown up top if i want to include like the title sometimes i like to just go to the control shift method and then the down arrow and that takes me all the way down i want to be back up top to the data so i'm going to cool down control backspace to get back up to the top and then i'm going to go to the insert tab and we're going to go to the charts and let's make a histogram histogram pull pull that on over to the right i'm just going to copy the title here and i'm going to put that control c and i'm going to put that in the title now i'm going to do these fairly fast so because we've seen these techniques before we're just doing it all in one kind of data set now so we're pulling different items from this one data set so you can see this is basically skewed to the right and you can adjust the buckets and so on let's take the second data set and i'm going to put my cursor up top hold down control shift down so there we have it now i'm going to hold down control and select the backspace getting back up to the top and then i might want a better position i'm going to be like right here so that when i insert the histogram it's going to put it pretty close to where i want it to be so i'm going to go to the insert tab charts histogram add another histogram so there it is so and then i can copy the title and i'm going to put that in the title of the histogram so title of the histogram all right let's do a few more of these i'm going to go then to age what is that doing there get out of there we're going to go next from age 18 to 34 so i'm going to select e1 holding down control shift down arrow to the bottom of the data set and then i'm holding down control backspace back up to the top of the data set then i'm going to scroll down just a little bit to be where i want to be which is right here somewhere around k30 insert tab and histogram drop down enter there we have that one i'm going to scroll back up double click in e1 copy the name control c put that down here in the title okay i'm going to do the rest of them here i'm going to put my cursor in age 65 plus control shift down arrow and then control backspace scrolling back down to where i want to place it insert tab charts group histogram drop down histogram histogram and then i'm going to double click in g1 select the data control c or right click and copy and put that in our title so there we have the title all right next one that was 65 let's take the total households control shift down selecting all the data control backspace scrolling down to where we want to place it insert and then charts and histogram entering the histogram pulling it in place scrolling back up double clicking on the name selecting it control c or right click and copy so that i can then put it right in our title field all right let's do the next one families control shift down control backspace scrolling down to where i want to place it which is all the way at the bottom i could have stayed down there this time and then insert charts histogram there we have that one i'm going to go back up top double click on the families copy and put that in the title and let's do one more histogram and i'm going to put my cursor in this one now if i hit control shift down arrow it doesn't go to the bottom because of those blank cells so i have to continue selecting control shift down till i get to the bottom you don't typically want to have blank cells in your data set if you can avoid it because that causes some problems now this time i'm just going to scroll down and hit insert and charts and histogram so let's put our histogram down here double click on the data set up top and copy that and then put that right here so we'll paste that in all right so and then we might want to make some relationships between this data so let's say we're going to take the total population and see you know if there's a relationship between total population and age less than 18 so let's say total population is dependent x or horizontal variable and then less than 18 is going to be the dependent in a scatter plot so now i'll select uh this one i'm going to hit control shift down so there's that data holding control backspace now i'm holding control to select the one next to it and then control shift and down again so that i have the second set of data holding control backspace you could do that by just highlighting and scrolling down the most intuitive way would be to do it that way but this way if you get used to using the keyboard uh it could be a little faster now i'm gonna i'm gonna take the bar here go to the right so that i'm kind of close to where i want this to actually be placed and then go to the insert tab and go to the uh scatter or the charts and i want a classic scatter so there we have it so we've got the scatter so if i pull this to the right there it is okay so i'm gonna delete the title because what i really want is to show the x and y axes now in this in this case the the x the one on the left will typically be the one that was on the left the column on the left will be the dependent or x as we saw in prior presentations with the scatter and the dependent will typically be on uh the vertical or the y so if i add my axes axes and i want to look at the titles i'm going to select the bottom or x axis and hit equals so you could see that in the bar up top equals and then i'm going to pick the population uh the population right this is the population and enter so now we've got the total population at the bottom i'm going to select this axis and say equals which you could see up top in the formula bar and we want 18 or less so there we have it and so now we can see like as the population goes up the the number of people 18 or less has a trend line up which you would kind of expect because you would expect a larger population to possibly have more more people within any particular age range and so you can look at different trends such such as that right you can look at a trend and say well what if you had people that are that are in the range to have kids that maybe that would be a stronger correlation between uh an increase uh in in them and and the and the number of of kids right so you can graph these relationships if i was going to add a trend line let's add a trend line and i want to then more options on the trend line if i want to add a formula there's the formula for the trend line if i go to the box on the left we can then say maybe i want the trend line to be simply a line maybe i want it to be uh a different color like red and then maybe i want to glow around it so i'm going to say let's make it glow and we'll make it glow uh with the orange closing that out let's do another one let's say that we were looking at the population versus uh versus the people that are making over uh versus over what was that last one here this one number households uh uh 100k plus and now this one's missing some data down below but i think we could still plot with it so let's say that we're going to take the population so we'll compare uh the population put in my cursor here control shift down so that's the whole thing and then control backspace back to the top and then i'm holding down control to select this one a non-adjacent uh column and then control shift down to get to the bottom it's not at the bottom because there's these blank cells so i'm going to have to say control shift down again control shift down again and now it's at the bottom then i'm going to say control backspace to get back to the top i would like to get my cursor somewhere where i want the data so i'm going to put it over here and then let's say insert chart and enter our scatter so there's our scatter and so now i'll delete this and let's see so you can see here we don't have as big a correlation we could still we could still uh let's plot let's put our axes on here we're going to say that we want uh the axis title and down here i'm going to say this equals scrolling to the right so we had the total population and then this axis put in my cursor here equals and then we had the number uh 100 k plus so there we have it if we put a regression line in here i'm going to say add trend line so there's our trend line i can add more options to the trend line let's make the equation so we can say show me the equation let's make the trend line uh orange this time let's make it so it's a solid line let's go to the colors and say the glow we want now we can have a bigger glow around it so we can have a wider glow than we had up top and you could see although they could you could put a trend line around it you don't you don't have a not you know the trend obviously isn't as packed together as a trend like this so it might not be as meaningful of a trend line let's do another one uh let's say that we had then let's pull up another one now i'm doing these fairly quick because we've seen these charts before we're just going to practice putting them together now let's say we had the total population and we're going to compare that to we're going to compare the total population to let's say families over here so again you would think as the populations go up you'd probably have more families you would think let's check that one out see how close that trend line is so we're going to say population i'm going to say control shift down so that takes the whole thing i'm going to say control backspace now i'm holding control down so i can select families which is a non adjacent cell not next to each other control shift down taking us to the bottom take us all the way to the bottom i'm holding control backspace to get back to the top i know these are a lot of keystrokes but notice you don't have to use the keystrokes you could use just click and drag holding down control to get over here however the keystrokes will be faster so i'm going to try to emphasize those as we go i'm going to try to then be kind of where i want to be with the graph so that when i insert it insert tab charts scatter plot the graph we're basically located in general where we want to be i'm going to remove the title we're going to add the axes so axes and then on the x the independent we've got the population and then on the y selecting the y equals we've got then the families and so you can see this trend line you know is a lot closer together here population goes up you've got families go up that kind of makes sense so we're going to say let's say we hit the plus button make a trend line through that we can add some details on the trend line let's say that we want the formula again and we want let's just make the trend line red this time we'll make it a red line so there's a standard line it doesn't want to make it red there it goes all right and then let's now now notice that this is generally over here the independent versus the dependent but sometimes there's a question as to which which is the independent which is going to be the dependent kind of variable you can use the same graph to read it the other way you could say well yeah as families go up the population goes up but let's just make let's see if we can flip these and put the families over here in the population over here just to practice that graphing technique so we're going to say all right let's take I can take these two again but what I really want these are this one's on the left so it's going to automatically plot it as though it's going to be on the x-axis and we'll have to then reverse it so same starting point control shift down there's that one control backspace putting my cursor holding control down and on I1 control shift down and then I'm at the bottom control backspace and then I'm going to scroll to the right before I insert put myself where I want the chart to be and then insert charts scatter there's our scatter but this time it looks similar but this time we want to swap the x and y's so I'm going to go into the to the chart design I just go to the data and then I'm going to add edit the data so here's the x here's the y they're backwards so I'm going to go into the x I'm going to delete what's in there I'll select this one and then we're going to say in the x I want the family so I'm going to put my cursor well I could just select the data on families control shift down so there is that one control backspace back up and then I'm going to say all right that looks good and then in this one on the y I'm going to delete what's in there and then I'm going to go into the population I'll just go into the data here control shift down and so that looks good and then control backspace back up to the top and let's say okay and then okay so we've switched the x and the y so now I go down here and it looks like they have been switched because the 35 was on the x 35s on the y now so we've switched those so it looks like a similar trend line but now we've switched you know the x's and the y's so if I hit the plus button and I look at my axis titles now on the x axis we have scrolling up trying to move over to the right we've got the families and then on the y equals we've got the population and I can add then my trend line we can say let's plus button trend line adding more detail to the trend line more options and we can say show me that formula bucket let's make it let's make it green this time let's make it like a yellow line and then we'll make it so then we'll go to the glow and on the glow we'll make like a medium size orange glow something like that just to be different so I know I did those fairly quickly but that's just we've seen all these charts before so these are a way that we can take a full data set and you can kind of make a button obviously you can make basically an endless amount of graphs from a set of data such as this as we look at the relationships between multiple sets of data we could put graphs on top of each other we can make bar graphs and put them on top of each other we can plot multiple items on the same graph and so on and so forth