 Statistics and Excel. Histogram versus bar chart. Get ready taking a deep breath holding it in for 10 seconds and getting ready for a smooth soothing Excel. First a word from our sponsor. Well actually these are just items that we picked from the YouTube shopping affiliate program but that's actually good for you because these aren't things that were just given to us from some large corporation which we don't even use in exchange for us selling them to you these are things that we actually researched purchase and use ourselves. Here we have a western digital WD elements 20 terabyte usb 3.0 desktop external hard drive we use as part of our backup system noting that if you lower the number of terabytes of storage the price will lower dramatically as well when you're thinking about a backup system you're usually thinking about an online system or an external hard drive system like this or ideally some combination between the two given you some redundancy. You can also work directly from an external hard drive like this but there are some drawbacks to doing that one being if you use this as your primary drive you're working from it's no longer a backup drive and you're going to need a backup system possibly another external hard drive and or some kind of cloud backup system and if you're working on something that takes up a lot of short-term memory a lot of RAM as you're working on it such as video editing the external hard drive can slow up the system so you might want to come up with some kind of system where you download the project you're working on to your computer to your C drive or possibly to a solid state drive which is a much more expensive external hard drive as you do the work once the work is done then save the project to an external hard drive such as this. If you would like a commercial free experience consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com where we have many different courses you can purchase one at a time or have a subscription model giving you access to all the courses courses which are well organized have other resources like excel files and pdf files to download and no commercials. Here we are in excel if you don't have access to this workbook that's okay because we'll basically build this from a blank worksheet and therefore you could start with a blank worksheet if you do have access to this workbook then there's three tabs down below example practice and blank example in essence answer key practice tab having pre-formatted cells so you can focus in on the heart of the practice problem the blank tab where we will be working we'll just have the data set so we can practice formatting in excel as we work through the practice problem let's go to the example tab to get an idea of what the end result will be we're going to take our data we're going to sort our data we'll calculate the average and the median but our major focus is looking at the differences between a histogram that we will create from the data and a bar chart that we will create from the data now these two things look very similar but there's differences in how they will work and we can actually create in essence a histogram from the bar chart so let me give you an idea of what i mean here if we go to the insert tab up top and we go to the charts group then most of the time the first thing that comes to people's mind when they think of charts is the classic bar charts up here but the histogram is here looks similar but it's different so we'll think about what are the differences between a histogram and a bar chart and we'll think about how we can basically use bar charts to create a histogram which can have its own uses from time to time and will also give us an idea of the similarities between the histogram and the bar chart so let's jump back on over to our blink tab now if you don't have your data set here then you could copy this data set if you just want to type it in or you can create your own data set using some of the methods we talked about in a prior presentation most of this data is kind of around we're imagining wages here so it's around like 65 000 to like 80 000 is the data set and it's got a shape that's that's somewhat you know bell curved or kind of shape to it that's the general concept so if you want to put together a data set something like that then you can practice with that or you can use this data set so i'm going to start by formatting the entire sheet like i do every time because all we have is data i haven't formatted the sheet so i'm going to put my cursor in the triangle up top right click on the selected area i'm going to format the sheet for my underlying formatting which i like to be at currency negative numbers bracketed and red no dollar sign i don't need the decimals right now because these are these are rounded numbers and the decimals are not going to be relevant to me so i'm going to remove the decimals and then okay so now i can format the cells they have a comma in it they look like a number format that will be my baseline formatting i'll also select the entire thing go to the home tab font group and i'm going to make it boldened because i think that stands out a little bit more for the presentations and you might just like that too although if you make everything bold then making an individual thing bold doesn't have the same kind of effect right but then we can i'm going to hold control down and scroll in so now i'm zooming into the data i'm currently at the 250 percent all right so the first thing i want to do is sort my data so i want to put a row above this so i can put a header on it and just call it wages i'm imagining this is data related to wages i could do that by selecting the entire row which is easier than selecting one cell and inserting above it due to the fact that there's nothing on the right hand side that's going to get messed up if i add an entire row and it saves a click right so if i click on the one here and then i select the entire row right click and insert i don't have to tell excel hey move uh the cell down if that's the only option it has is to move it down right it can't move it up because it would go right we're always moving down it's the entire row okay so we're going to say this is wages and so there we have that i'll center it our home tab alignment and center that not that center i want to this center and then i'm going to make it into a table which is what i typically do i feel more comfortable when the data's in a table format so i'm going to put my cursor anywhere in the data because there's no blank cells i could put it anywhere in the data and when i go to the insert tab up top the tables group and then to the table it will guess that i want the whole table and you can see it by the dancing ants around there that it's picking the tables that we want so there it is and so now we can sort the data and so i can hit the drop down up top and possibly sort it from z to a or a to z let's sort it from a to z so the lowest point is 55 that we talked we looked at this last time the biggest point is at 84 000 if i wanted to calculate the average and the median i'm going to make this column shorter notice every time i create a table if i want to calculate the the average in the media based on that data i'm not going to start typing next to the table because i don't want anything actually touching the table because excel might get mixed up that that's part of the table so traditionally what you want to do is put a skinny column so i'll make a skinny b so that i could tell excel hey these things are different keep them separate it's like the it's like the vegetables and the potatoes on the plate you don't want that i don't like them all mushed together and that's how excel is too so then i'm going to say this is going to be the mean uh and the av or let's say or the average so remember there's a couple different ways that we can calculate this right we can use the the function method function which is the average it uses an average calculation so equals average so we just double click on the function it has an equal sign the function and then the brackets and the argument down below and we just select the field now the easiest way to do that is to put your cursor here control shift down arrow and now i could just hit enter because that'll take me back up top but if i want to check the formula i can see it in the formula bar right there and i can hold control and backspace which takes me back to the top so now i can see the formula here now technically i should close it up control uh zero but i don't have to if i don't have that last bracket and i hit enter uh excel is nice enough not to get mad at me or anything usually so it's going to say enter there it is now i can also do that average if you if we recalculate the average we could say okay the average is going to be the the uh the let's say the manual manual method let's say we sum the whole data set the famous function equals sum shift nine i'm using the keystrokes now i'm going to use the keyboard now to do this because this is a familiar formula left left left up up up notice i'm not using the mouse at all because i'm trying to be super nerdy cool and then control shift down so that now i've summed it up now i could just hit enter i can see the formula up top but if i want to see it in the cell i can say control backspace there it is i don't really need to close this one up either so i'm just going to hit enter sums it up and then i want to count so i know that i can do it this way i can say well this is two and this goes down to to 51 so like so 52 so i would think like 51 would be the count but i can also use the count function which equals the count so there's our count function once again i'll i can put my cursor over here with the arrows control shift down arrow and then there it is in the formula bar i could just hit enter but i'm going to hit control backspace and enter and then divide so divide divide that's not spelled right i know it's eight equals up up so now i'm just using the keystrokes again so i want to take i want you i want excel to take what's in cell d five which is d five divided by slash up once notice i had to hit up not down it's not going to excel is not going to start from the last cell i was in it's going to go back to the formula so take what was in cell d five divided by what's in cell d six and enter and there's our average so let's say let's make this indented home tab alignment indent and i can indent these again indent and let's just call this the average i won't say divide i won't put the function in there i'll just say average and then the median is going to be we can use the function for the median equals most people just use the median you could use quartile too but you i'll just use the median function and so same thing i'm just going to go up here and say all right control shift down and then control backspace and enter so there it is on the manual method manual method i can just say it's it's the one in the middle right if i counted all of these and then i picked the one in the middle seventy thousand nine is in the middle right so right there so meaning if if i counted the ones you know below it and above it i get to 25 below it and 25 above it right so it's in the middle so that would be just picking pick the one in the middle just like rocky does when he's in a fight and he sees three of them out there then he then just like his mickey said he hits the one in the middle in any case i don't know rocky no one knows rocky anymore what are you talking about uh let let let's go ahead and make this blue uh let's go to the home tab font group i use this blue down here if you don't have that blue you can go to the more colors and use this blue and right there i use that excel it's fun guy used to use that home tab font group i'm going to put borders around it borders around it and then let's just make a little bit more well let's i'll keep it at that all right well now let's make a histogram from this data we'll make a histogram from it which is easy to do we can just select the entire uh dataset one way you could do that is selecting like where you have the down arrow that's just showing the data uh without without the headers uh on it and so i'll pick that up and i'll go uh let's go to the insert tab and then chart group and i'm not doing the bar chart but a histogram so i'll select the histogram there's our standard histogram so i'll put that in play i'm holding control scrolling down a bit so we can see the full histogram so there we have it okay so i'm going to delete this thing up top so let's delete that oh not the whole thing undo control z or undo i just want the title to go away all right and then i'll hit the plus button and then we want to see uh the the access data sets an easier way to do it possibly is just to double click on this item down here so i want to look at these buckets that it made so it made these buckets and then put the information that lands between these buckets now i want to go to the bend size over here and i'm going to put my own bend size which is 2000 say the buckets bend size uh is 2000 and we get something that looks like this so what is the histogram doing it's saying hey look everything that's in between 55 and 57,000 i'm going to count those how many there are and put that amount here one in this case and then everything in here from 67 to 69,000 they counted and put here looks like around seven right and i could put the numbers in here let's put the numbers in here and i think the easiest way to do that is to actually right click on the data itself right here we're going to select the data and it says here uh add data labels so i'm going to add the data labels and there's the data labels if i select the data labels we can go to the insert or home tab font and embolden them we can make them you know larger uh if we so choose so now we've got our data labels so here we had seven that fall into this bucket 19 that fall into this bucket 15 that fell into this bucket and and so on and so forth so obviously we can change the size of the buckets and we'll we'll look more about that in future presentations but right now we can say okay well what what would happen if i made a bar a bar uh chart now if i insert a bar chart and i just use this data like if i just say take this data and make a bar chart so now i'm going to go to the insert tab charts and make a bar chart of it just the standard one that's not that useful right because what it's doing is it's taking it's just making its own like number set down here and saying how many how many how many ones fall into that but the problem is that that we have a more nuanced data so that's why we need the buckets right that's why we need these buckets so what the the histogram is doing is nicely sorting the data into buckets but we could do that ourselves and there's a couple ways that we we could do this you could use like a a histogram or you can use uh you can use an array and the and the two uh so i'll try to show both methods the array thing is going to be an easier thing to do but i think it's easier to kind of see what's going on if we use kind of like the formula method first so uh let's let's let's say that we're going to we're going to say that i would like to put my buckets in place between 55 000 and 57 000 i'm arranging my buckets between uh between the the low and the high and i'm going to have all my buckets increase like we did up top by 2000 so this is going to be this plus 2000 and this is going to be this uh plus 2000 so we're kind of naming the bucket size now now that i have these two cells in here i can copy that down to get all of my buckets so i'm going to put my cursor here i'm going to select these two i'm going to put my cursor on the fill handle and we're going to drag that down until we get to like 77 we got to get to like 85 i think fill handle dragging it down 83 one more time to 85 so there's now i'm going to use a kind of fancy formula so i can put these two things together with a dash between them so that i can get a label for our bar chart that looks something like this now this is the type of thing that works quite well when you're working with text so for example i'm going to say this equals and then i'm going to point to this cell so what's in c 17 c 17 but i don't want to add it to something else or anything like that i'm going to hit a space and i'm going to hit control and so i'm going to say and and then i want to put text in it so when i want to put actual text in it i have to tell excel that it's a text item by putting quotes around the text so i'm going to say shift i'm going to put quotes and then i'm going to put a dash that's what the text that i want and then i'll close up the quotes so what will happen if i was just to leave it like this it would put the 55 000 and then it would add that dash as a text item for it and then i'm going to say space and another and and then i also want this here as well so now it's going to be putting together what's in cell c 17 and this text in quotes of a dash and then what's in cell 50 in cell d 17 so if i say enter there we have it now if you if you if that's too tedious to do and you don't want to do it that way then you could just type it in there you could say uh if you don't if you don't want to have a function you don't have you don't start with a function 57 000 dash 59 000 but then of course you have to type it in going down so what's nice about a function like this is that you can then grab that on the fill handle and drag it down right so now i've got my uh data labels and then again i'm going to use a somewhat uh fancy formula to count and try to say that i want to be picking up from this data set what is in between the the 55 000 and the 57 000 so to do that i'm going to use a pretty fancy formula here so before we start the formula note we have a bit of an issue if we have a number that falls right on the line so for example if we look at this first bucket we're including everything from 55 000 to 57 000 then everything from 57 000 to 59 000 59 000 to 61 000 but what if for example the number is exactly 59 000 do we include it in this bucket or do we include it in the bucket below now the general rule is that we would include it in the bucket above and then the bucket below would not have anything that's 59 000 exactly but everything that is greater than 59 000 up to and including the 61 000 now that also leads to a bit of a problem on this first line right here because that 55 000 we want uh in this first bucket to include what's in the 59 000 the 55 000 because we want that to fall in that first bucket and we have one that falls right there so i could make this a little bit lower i could make this 49 000 so that i can pick up everything you know from 49 000 uh and above which is a little bit different than our histogram over here but that's what i'll pick up so that we can have the same formula all the way through all right so now we'll do our calculation this is going to be equal to we're going to use our count if calculation but not the normal one that we looked at last time instead count ifs because we're going to have multiple criterias the criteria being that it has to be greater than the the first area 49 000 in this case and then less than or equal to the top criteria the 57 000 so i'm going to say count ifs and so here's our here's our formula here's the first argument in the formula and so i need the criteria range i'm going to scroll up i'm going to put my cursor on the little arrow so i can set the drop down dancing ants around just the data scrolling back down i can see the formula here i can see the formula up top to get to the second argument i select comma so now i'm on argument number two which is the actual criteria so if i was going to put a criteria which was just like one number then then i can refer to the cell but i need a more complex criteria because i need it to be greater than the 49 000 and so that means i'm entering text so i'm going to have to put brackets around the greater than so brackets around or quotes i mean around the greater than and then i've got to connect that to this number so i have to use an and in order to do that and then pick up the 49 000 and so that's going to be our first argument it's got to be greater than the 49 000 notice if i just hit enter right there if i stop my formula right there and enter i'm at 51 uh items which seems reasonable let's let's double click on it again and continue the argument so i'm going to go to the end of this argument again and add another criteria so i'm going to say comma the criteria range i have to select the range again scrolling up putting my cursor on the a where i have an arrow dancing and surround the data comma takes me to the next argument which is the criteria and i want this to be less than or equal to the 57 000 so i have to put the quotes because i'm going to put kind of text in here and i have to put the less than first and then the equals so less than and then the equals less than or equal to and then end the quotes and i need to tie that together with an and so we're going to say and and then i'm going to pick the second cell 57 000 and there's our second argument i'm going to close up the brackets shift zero closing it up so it's kind of a kind of a long formula but there we have it now it picks up one which makes sense right because we just have that 55 000 so then i can select that and i should be able to just copy it down let's copy it down one time fill handle copy it down and see if that picks up the second one in the same fashion notice there's a bit of a problem here hold on a second i'm going to i'm going to hit enter now i messed up my second range i'm going to start this one at 57 000 so now this this first one was at 49 the second one's at 57 and then we're 2000 apart so now it's picking up zero which ties into our numbers above let's copy it down i'm going to put my cursor on the fill handle and copy it down and so now we've got 19 in this bucket right which is 19 in this bucket 69 to 71 and the the seven in this bucket so that looks about right right so so now now i can use my bar chart so now i can select these items and i can say i'm going to just insert a bar chart which will basically build a histogram because i've created my own buckets so i'm going to go to the to the insert up top after i've selected those two rows or columns and then the charts and graphs and instead of the histogram this time we'll make just a normal bar chart so we'll make the bar chart i'm going to pull this to the to the side so we have it over here and let's put these like on top of each other so i'll bring this one over a bit so there's one on top of the other and then i'm going to delete the title and let's see if i can click on this right click and and say if i can add data labels add data labels so there we have it so there's our our general concept with it so now we've got the one seven nineteen fifteen seven one and one one seven nineteen fifteen seven one and one and so you could see you know the so hopefully that gives an idea of of basically how you can build your own kind of histogram from the bar chart now note that that could be actually useful to do because if you have a bar chart like this you can actually add a second set of data a little bit more easily than possibly the histogram so that you can compare two kind of shapes of the data on the same bar chart now also note that the bar chart has similar features on how do you how you deal with it so if i double click on these bars for example sometimes you might think the histogram has some wider uh uh bars right so you can double click on this i'm into this bars over here and i can say that if i want you know to make them skinnier or i want to close the width between them right so now i've got it you know almost touching each other right so you've got something so that might give you more of a a histogram like feel if you lessen the gap between some of these let's do it this way so i could see it gradually so there we go something like that all right and then so so that's the general so that's the general idea and and that can be a useful tool now before we wrap this up i just want to note that you can uh this is somewhat of of a tedious process if you if you use the the another function which which is like a newer kind of function it's a less intuitive to me but faster once you understand it then you can get to this data a little bit faster and you could build this format a little bit quicker which would be useful again if you wanted to kind of put two of these on top of each other for example so to do that we're just going to put our end points on uh that we want on the end point so fifth just this column so if i reconstruct that column down here just the end points we start at the 57 000 and then i'm going to do the same thing we did before by just saying equals the one above it plus 2000 and then i'll copy that down until we get to the 85 000 putting my cursor on that second one fill handle dragging down until we get to that 85 000 so there we have it and so now i'm going to use an an array so i'm going to say this equals and we're going to use a frequency double clicking on the frequency and then i'm going to select the data array which is going to be up top selecting the same data dancing ants around the data and then i'm going to say comma and then i'm going to put my cursor around the bins array so i'm selecting the bin array here and then i'm going to close this up and what what this will do is basically spill out the data right so i'm going to say enter and now it gives me that spill of the data so the fancy way to do this obviously a lot faster than this kind of long formula but i still think the long formula is kind of intuitive to look at it as well so in future presentations we'll kind of continue on with this because i know we're going a little bit long here and we'll do different kind of histograms based on this similar data to see what the shape of the histogram will how it will change as we change the data sizes