 Statistics and Excel. Histograms with different bucket sizes. Get ready taking a deep breath holding it in for 10 seconds and looking forward to a smooth soothing Excel. Here we are in Excel. If you don't have access to this workbook that's okay because well basically build this from a blank worksheet so you can start with just a blank worksheet but if you do have access to this workbook there's three tabs down below we got the example practice and blank example answer key practice tab having pre-formatted cells in it so you can get right to the heart of the practice problem blank tab just having the data in it so we can practice formatting cells in Excel as we work the practice problem let's take a look at the example tab in essence looking at the end result we're going to be sorting our data we're going to imagine this is data of wage data for like a corporation for example and then we're going to make multiple histograms from that data set so we can focus in on how to adjust the histograms and mainly on the buckets so we can see what happens if we change the bucket size the bucket size is going to be quite important when you're trying to visualize your data in the format of a histogram so let's go to the tab the blank tab where we just have our data now remember that if you don't have this data you can use some of the methods we talked about before you can look for some data for wages if you so choose you can just type in this data set if you would like to just simply type in the exact numbers we have or you can use some of the methods that we talked about in prior presentations to build a data set so once we have the data in the system i'm going to format the entire worksheet like i do basically every time selecting the triangle to select the entire worksheet right clicking and then i'm going to go to the format cells and i like to go then to the number group we're in the i usually go to currency bracketed numbers are negative numbers are bracketed and read with no dollar sign and then i don't need the decimals at this point because we're dealing with data that's basically whole numbers so i'm going to remove the decimals as the default for my entire worksheet and then say okay so now it formatted it in a numbers type of format with a comma in the thousands position i want to put a header up top and make this into a table and then sort the data from top to bottom so i'll scroll in a little bit i'm currently at 190 holding control scrolling in on the wheel so now i'm at 220 now remember to put a header i need to put a row above this or a cell above this the easiest way to do that is to select the entire row but if i had something over here for example that might not be what i would want to do because it would push this down so let's try the other method of doing that this time i could select one cell right click on that cell and insert and i have an extra step because now it needs to know where which way i want to push the cells and in this case i want to push the cells down and so then i could say okay it pushes these cells down without disturbing the data on the right all right let's go ahead and type in wages that's going to be my header i'm going to tell excel it is a header by say center in it home tab alignment center you might underline it or something like that too i'm also going to select the entire data set and embolden it home tab font group bold it just so it might be easier to see in a screen recording so you may or may not need to do that i'm going to put a table into this like i usually do with my data sets putting my cursor in the data i could put it anywhere in the data because there are no blank areas in it and then go to the insert tab up top go to the tables group and then add the table and the dancing ants are doing their mama dance around the around the proper area so i'm going to say okay there is our table we now have this nice drop down up top so the next step is often to sort to the data so i'm going to hit the drop down or just tap on it i'm not going to hit it too hard i'm not going to break it or anything it's going to go from let's take it from z to a we're going from z to a and so there is our breakdown of the data so now let's just go ahead and start building our histograms and and then we'll adjust some of our histograms so to select the data one way to do it is i can put my cursor up top and select the data this way and then i'm going to go into the insert tab as we saw in prior presentations the charts and remember we're not looking at the bar column the bar charts we're looking at the histograms here and that will make the buckets right so the whole point of the histogram is it makes these buckets so i'm going to add the histogram we have a default histogram which is usually pretty good i mean they usually they usually you know uh do the histogram fairly well so if i look at what it gave me here on the default histogram we could say all right uh let's go ahead so here's our buckets down below let's click on the buckets are really the main thing most of the time that we're focused on so let's click on those buckets and then i'm going to go to the uh this symbol and then the access options so we have access by category so we're which doesn't up isn't appropriate for us because we're looking at actual numbers we've got the automatic buckets that are being put in place here and the bucket size then if i if i look here it's kind of hard to see but because it's on automatic and this is grayed out because you're not typing into it but this default is at that 3400 and then nine buckets so you got one two three four five six seven eight nine buckets and the 3400 now we can customize this by changing either the bandwidth or the number of bends either one will will change the the buckets right either either of those will will change the other one right if in other words if i add more bandwidth it's going to change the the number of bends so for example if i go into here and i say i want to make this uh 2000 then it's going to change the number of bends to 15 so now we have 15 bends that went up if on the other hand if i went back down and i say i want the number of bends to go back down to like 10 notice this number up top is at that 1933 it's going to change that to now 2900 and now if i if i want to refresh this to what it was in the automatic i could always just simply go back to the automatic and then it goes back to its default position now you'll recall we talked a little bit about this before with the overflow bend and the underflow bend so we can kind of group together these outliers uh at the far ends so now just note that if i go down here to this number tick mark if we have the categories usually we're looking at our numbers of the categories but if i hit the drop down you can also say if it was a general currency accounting if it's date date time percent function scientific text and so on so uh in some cases that might be useful you can also take away the comma if you so choose in the buckets if you want the commas gone for whatever reason for formatting purposes but for and then uh negative numbers the formatting of negatives we don't have any formatting uh of the negatives but usually most of your formatting is going to be up top here now you can also adjust this column so this column has been chosen if i click on that column once again i get my information on the right i'm going to go to the tab or this icon on the right for the access options and usually the major options you're looking at are the minimum which is the auto and the maximum so it picked up here from zero uh two thirty as as what it's going to be counting up from now then we also said if i close this back out that remember if you're clicked on the chart itself if i click off the chart these two items are going to be gone right so if i remove them they're gone if you're looking for those two tabs you have to be on the chart then you've got those two tabs where we have the add the chart elements so this is one place we can go to kind of add those chart elements the axis uh titles and the date labels and so so forth the quick layout options that we have here right so those are pretty nice you know quick adjustments to the chart the formatting of them which you can also kind of do yourself if you want to do your own formatting the colors of the of the graphs up top and then your default formatting this way of the charts which again gives you kind of another formatting you've got your data up top so here's our