 Statistics and Excel. Wages, data box plot or box and whiskers. Get ready, taking a deep breath, holding it in for 10 seconds, looking forward to 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, purchased and used ourselves. Focusrite Scarlet Solo 3rd Gen USB interface with software suite. I've been using a focusrite for years for my audio needs before which time I had a USB microphone which plugged directly into the computer. But I think you'll find as I have found, if you want to increase the quality of your microphone, you will need an interface and the focusrite is the go to interface as far as I'm concerned. I've been using this for years now. It works well. It's easy to use. It seems quite durably built because I only do the screen recordings. I only need the one solo interface. However, if you have multiple microphones you need to plug in or if you have other instruments you need to plug in, you can look at a similar model that has more input ports. 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 can just open up a blank worksheet. If you do have access to this workbook, we have three tabs down below. Example, practice blank. Example, in essence, answer key, practice tab, having pre-formatted cells within it so you can focus in on the core component of the practice problem. The blank tab, just having the data set within it so we can practice formatting the cells as we go. Let's take a look at the example tab, the answer key to see where we will be going. We're going to be sorting our data. This data is related to wages so we're imagining we have a set of data possibly for an organization for wages that we would like to glean some insights from. We're going to do so by creating a box and whisker chart on the right hand side. That's really what we're focused in on in this presentation and then in future presentations we will continue on to analyze the box and whisker chart with our analysis tools over here. We'll also make another box and whiskers just to note that you can put them on a side by side if you had two sets of data and then we'll compare the box and whiskers to a histogram type of format as we lead in to then starting to talk about histograms and the dynamics of them. So if I go to the tab to the right, the practice tab, we have some pre-formatted cells over here so you can more easily simply put in your box and whiskers if you wanted to do that without the formatting. And the blank tab, we just have a list of numbers. Now note that if you don't have access to this workbook you can look up wages for a particular area if you so choose. We've talked about different methods that you can pull data from if you want to get some practice data to work with. Obviously you can type in the data if you so choose if you want your information to tie out exactly to this information. Although I know that's a little bit tedious. One of the methods that you can use in Excel to get some data would be to use a random selection of data. Although then you would want to adjust it to have some outliers and whatnot so you can kind of get an idea of what's going to happen with your box plot. So to do that you can say equals in a cell and you want to take the random between so that you can choose the bottom and top. And so you have your function here and basically most of our numbers here are between like 65 and 75. So if I took the random of 65 and then I say comma the next argument on the top is like 75 and then close up the brackets. You'll get some random numbers and if I copy that down I can then get a set of random numbers and then you might want to add some outliers to it. Now obviously when you have random numbers you can get an idea of what the outcomes are going to be because you would think they would be coherent to some randomness. Whereas if you had actual numbers in the real world you're going to see possibly different trends that are going to be different than simply random. So the kind of things you're looking for the differences between the random and what actually is in the world and what those differences might be telling us. Alright in any case this is going to be our set of numbers on the right. I'm going to scroll in a bit so I'm going to hold control down and scroll in. I'm currently at 205 you can also do it by hitting this plus button over here and then I'm going to format my entire worksheet. I do this basically every time I'm going to select the triangle button up top. I'm going to put my underlying baseline formatting and then I'll format other cells that might be different or deviant from that baseline formatting. So I like doing this by right clicking formatting the cells. Here's my formatting box. I'm in the numbers group. I will typically format with the I like the actually what I've been currency and then negative numbers are bracketed but no dollar signs and then I don't really need decimals here. I'm going to move the decimals because we're working in thousands and then when I need decimals I'll add them to those particular cells. I'm going to say okay. I'll also bold them home tab font and bold. And I think that's easier just for the presentation purposes or you might just like the bold might stand out if you have bad eyes like me or something like that at least. Then you could use that let's put a let's put a header up top so I want to put something above up top here so I could put a header on it. So the easiest way to do that is to select an entire row putting my cursor on the one selecting the entire row and there's nothing on the right hand side. That's why it's the easiest thing to do because I don't need to tell Excel hey I would like you to move this group down. I could just say just add an entire row so it's one less click than moving the entire thing down so I could just say insert. Insert notice it doesn't say insert cells insert a column because I have the entire row selected so it inserts there and then I'm going to put up top I'll just say wages as a header. And then I'm going to I like to put this whole thing into a table before we create the box and whiskers so I'm going to put my cursor anywhere within here and then I'm going to go into the insert up top tab. So remember these are the tabs up top insert tab and then in the tables group in the insert tab section we want to insert a table. Now I only have one thing selected so the dancing ants I know it looks like they're marching but if you looked at them close they would they'd be really grooving they'd be grooving like their hips are moving everywhere man. But you can't tell because we're but any case if you select those then then you can say OK and it'll pick up the entire table which will result in a new table tab up to the right. So if I'm off the table no table tab if I'm on the table there's a table tab what we really want to do here is now organize the data top to bottom that's usually one of the ways we can organize the data. If I look at this data and I just took a random sample. I just looked at people's salaries basically an alphabetical order or something like that and I picked up their salary numbers then alphabetical orders not going to tell me much right about this this ray of numbers. The first thing that I would want to do is say hey if I put this an order from top to bottom then I can get a vision I can at least get a visual look at what what is happening so that's clearly what we will typically do. I don't have to do that by the way in order to enter or create a box and whisker the box and whisker will figure it out the chart. But the first thing we would do and we're analyzing the data would probably be I want to see it from Z to a Z to a. And so now we've got the highest on top and like alright now I can get a sense of kind of where this where this group of people are are lying in this number set. Now the next thing we might do is start pulling some some break this data down using the average the the mean using quartiles and whatnot. But let's first makes the box and whisker chart and then we'll kind of analyze some of those other numbers in accordance with the box and whiskers will also get into some more of the math. On those kind of analytics later but for now we just want to get a pictorial representation. So to do that I can select the entire thing this way there's some faster ways you can select like if I click the data like that I can select the data that way. But I'm going to include the header here too so I'll select it I'll just drag it down select the data I'm going to scroll back up again and then I'm going to go into the insert. And then we're in so that's the insert tab we're in the charts group within the insert tab. And now we want to look at this one here now the history notice it kind of looks like a histogram not the box and whiskers because the histogram is really kind of like the go to oftentimes for sorting our data which we'll talk about later. The box and whiskers like you can kind of imagine I imagine it kind of like before before we had the the Excel and whatnot it's probably easier to draw by hand like a box and whiskers right. Whereas it's a little bit more difficult to draw like a histogram if you're going to do it kind of by hand. So the box and whiskers possibly is maybe a little bit more outdated although it's it's still a nice visual effect here and then next time we'll get into the comparison of it and kind of like the histogram. So let's go into the box and whiskers there we have it and boom so now we've got this pictorial representation and I'll put it I'll just put it right here for now. We'll get into some more kind of idea of what exactly the box and whiskers is doing but for now let's format this I'm going to get rid of the title at least for now. So remember we can format these items it won't I messed it up delete the title make it a little bit bigger notice if I'm off the chart. I'll try to make it a little bit bigger this way then I don't have those two tabs up top if I go on to the chart just like we saw with our bar graph we get these two tabs up top so with the chart design. You've got the add chart elements you know the accesses and so on the quick layouts you've got the color changes and whatnot that you can do to it and everything blues the standard. So usually you probably want to change it from blue if you're trying to impress somebody because otherwise they're like yeah you just pick the standard one. And you could change it you can change these this is an easy way to change it as well here's your data sets. So if I go into my data sets we can see the data that was pulled in if we edit the data set you can see the data set in this format that we pulled in to the box and whiskers and if you wanted to add another data set that have two on the same graph for example then you can add it this way that's one way that you can kind of add a data set. So and then change the type you've got the formatting up top and this gets more into like the formatting stuff the current the the formatting selection that you know how you're going to the shape fills and all that kind of stuff within your now this so you know that gets a little bit confusing when you look at the formatting because you're like hey some of that stuff's kind of repeated possibly over here in the homepage. But when you're working within you know a chart or a graph you're typically going to want to go to the formatting on the right hand side which is specific to it. Now if I click on any of these elements within it so if I if for example I have a lot of empty space back here which is kind of squishing up the box and whiskers up top. So I might say hey look can I just start this thing maybe at like 50 or something like that and take it up to like 85 maybe and then it'll be a bigger thing so one way I can do is I can I can click in here. And that will give me on the right hand side if I go to the right hand side I'm on my axis so if I select the axis here. So now I mean these are the numbers that I'm kind of working that I'm focused in on so the minimum is at zero what if I pull that minimum up to like like 50. So now it went from auto it didn't just auto assign it I forced it to start at 50 so now it started at 50 so we don't have all that blank space we have a little bit wider of a box and then I can maybe I'll put the top at like 85000. And so now we have a little bit wider of a pictorial representation now you have your other formatting options over here with you know the shadow and the glow this more kind of of the formatting I won't get into that in too much detail at this point. But if you wanted to you know change some of those elements any of the elements within here you can click on these elements and again you could go into the information that is related to it in particular. We also have the plus button so that gives us our accesses so if we want to label the accesses we probably don't need this one down here right so I could probably remove the one. And so so this is the access title now on this one I again I probably don't need this access maybe but I might want like this access to be you know wages or maybe I just put the title you know as wages since it's a box and whisker and then I can if I go into these chart title data labels. This could be helpful depending on how large the item is right so we can add the labels to it and I can give you a sense of exactly you know what is going on and then the legend which would be applicable if you had like multiple data sets that that you were working with so. So that's the general idea so next time we'll get into more detail exactly you know what what this is saying and we'll basically tie it out with the reason I don't want to get into it in detail now is because. We can like do the math with it and we can do a formula to see exactly what is going on so we'll pick up you know quartile one quartile to the median the average and what the outliers. Basically mean and how exactly they are calculated and once you have a sense of that then it'll be easier to kind of just intuitively look at this. However at this point I also just want to note that we could put multiple box and whisker charts which might be a useful tool in one area so let's go ahead I'm going to I'm going to copy and paste the data set over here to column R. And then make a simple formula just to make another data set just so we can see that what that comparison might look like. So if I go in if I select this entire column I'm going to put my cursor on a so the drop down so now the entire column all the way down is selected I only need it down to the end of the table. But this is the way to copy the whole thing I can right click and I can copy it and then I'm going to move over here to column R. So I'm in column R. And then I'm going to put my cursor on R1 I can't put it on R2 by the way because if I did and I try to paste it on R2 there's not enough room because I copied the entire column even though I only really need so I have to put it on R1 right click and paste. And so there's our wages so I'm this time I'm going to call it wages one and then let's make a wages two. Now note that when I hit enter on wages two it automatically updated my table because it's it's because I put something in right next to the table. And so Excel saying hey you probably want to add that to the table and we do so that's that's fine. So now we're going to say I'm just going to do a little formula here and I'm just going to say this equals the one to the to the left of it times one point one. So we're going to add 10% to it so 100% plus 10% just to get a similar but different data set. And so if I hit enter Excel guesses that we want to do that to every line and we do so good job Excel thank you. We appreciate the help and looking out for us there always thinking thinking ahead to what we're what we need like a good staff member or something. So then if I select these two then let's do it that let's just copy the data set. I'll just copy the entire thing then we can then insert. So I'm going to go to insert and then I'm going to go to the charts and the histogram here and we want the box and whiskers. So there's our two box and whiskers now that have been placed side by side. So that's just the you know that again just could give us a different kind of visualization. Let's do the same kind of formatting we did with it so I probably don't need this one down here. I can remove that I can I can say let's go into the side part and let's say that we're going to bring it back up to 50. So I'm going to bring it back up to the minimum of let's say 55,000 I said this time that's what I had in my example and then we'll keep the top. Let's make the top like 95,000 95,000 so it zooms in a bit more so that looks good and so I can close that I'll make this maybe a little wider. If I hit the drop down the access titles may maybe I need I can just say let's get rid of this access and this one I'm going to just call it wages. Okay and then the chart title I may not need because I have it right there the data labels those are kind of neat maybe to have although they're kind of convoluted. But and then the legend here here we kind of need a legend right because now we have two data sets so now I have to say that this is data set one in the blue and data set two in the orange. Now if you if you go into the options of the legend you could put it on the right left on the bottom and so on but now that's what a legend is right that's when you need the legend. Clearly we don't need a legend if we only had one one of these items in place so it's too wide we'll put it like right there. All right so any case that's so that if I if I select on on the data by the way and we or if we go into our data selection now you'll note that I have now two sets of data so on the same graph. So notice I did that by selecting the two sets of data but I could have if I added the data and I wanted to add it to the same graph. Like if I didn't have you know this data set and I only had this data set well then then I could say okay now I've added this other column I already made my graph instead of deleting the graph. And then selecting both columns or maybe these columns weren't next to each other or something like that I can add another data set. And I'm going to say this is you know what wages wages to that will have here and we'll say that this comes from this data. And by the way there are shortcuts like if I put my cursor up top here and then I hold down a control shift and the down arrow just to show that again. It's a little bit faster right so like like if you get fast at this because this data set could be quite long so when you like go like this and you got to select the entire thing it could be kind of tedious. If it's in a table format you can put your cursor right at the top so it looks like that arrow and then select it. And so now it's selecting just that data you see the dancing answer grooving right there. And then if you if you put your cursor on the first one and you hold down control shift and then down arrow control shift down arrow. It takes you to the bottom and highlights to the last number if you have blank spaces in the data by the way which is a problem. You don't really want blank spaces in the data but if you did then then you might have to hit the down arrow you know like a couple times. And then when you want to go back up you can you can select control and the backspace not delete but the backspace and that'll take you back up to the top. So that so that when you hit enter you're going to be at you know the top of the of the item here and that'll also help you to kind of see what you're looking at if you were in a formula. So we'll try to touch on that from time to time those little tricks but there's the there's the the two side by side. So in future presentations we're going to be continuing on with this worksheet and we'll analyze some of the components of this box plot and then we'll compare it to like a histogram of the same data.