Boxplots in Excel 2013

in this video we're going to do a demonstration of creating a boxplot in Excel before we can start with the boxplot we have to have the five number summary so we're going to use some Excel formulas to calculate those the minimum will be calculated easily by doing equal sign min and then in parentheses we need the array for the list of numbers so the easiest to do is to just highlight that list of numbers and then you can just press Enter now we'll find your minimum value we can do a similar formula for quartile it just has one extra parameter so we're going to go equal quartile in parentheses we're going to do the array and then comma and then what quartile we're finding so in this example since we're doing quartile one will do the array and then comma one so highlight your list of numbers to get your array in there and then we're going to do comma one close that parentheses the median is actually the same thing as the second quartile so equal sign quartile again parentheses get our array again this time we will go comma to port aisle 3 similarly array tamo three and four max we can do equal sign max and again our array of values so then we have our five number summary next we need a list of differences between these values in the first cell we just need to keep that minimum so we can just do equal and get that value in the next cell underneath that we need to do the difference between q1 and q3 so we'll do equal click on your q1 number minus your minimum equal again here we're going to do median minus q1 equal sign here we'll do q3 minus median and finally equal sign here we'll do max minus q3 the differences are the numbers we'll actually use to create the chart so once we have those we're going to highlight those five values go to the insert tab to a chart you can either do a stacked column chart or a stacked to D bar chart depending upon whether you want your box plot to be vertical or horizontal I'm going to demonstrate with the horizontal so we're going to choose that stacked bar obviously this looks nothing like what we need the next thing we want to do is come up to this button right here switch rows and columns so that we have one section there we're getting closer to what we need now we just need to sort of format to get this to actually look like the box but we don't need this bottom value here in the plate blue so we can just right click on that and say format data series and we would just like that fill to be no fill we're going to do similarly with this orange section we don't want any fill there so we're going to click on that again and you know fill next we're going to get the actual whisker part here so on this second region there where we just took care of that fill color we're going to go up to the add chart elements we're going to add error bars more error bars we're going to format that error bar on the lower end we want a - error bar and we want the percent to be 100 and that will give you your lower whisker we're going to go to the top stat section here again we are going to format data series but we can just go to the fill button we want no fill there this time we're going to click on this series four point in the yellowish area here and we're going to do another error bar so go back to add chart elements error bars more error bars again we want to format that error bar so this time we want a plus err bar and again in the percentage we want 100 percent so we have our two whiskers and then our general regions here normally these are the same color so probably you want to format those so we can click on either section to begin with go to the fill in line get a solid fill whatever color you would like get a solid line again whatever color you would like and then do the same thing to the other bar you could leave them different colors or make them the same color of course you want to go ahead and give your chart a title I would get rid of the series number over here and if you want to adjust your number line down here you can right click format axes and you can set your high and low number here so maybe we'll go from 100 to 1200 and then you have a box and whisker plot

Channel: Paula Schute

Views: 427,602

Rating: 4.5651321 out of 5

Keywords: boxplot

Id: DNpvSg2X0xQ

Length: 7min 0sec (420 seconds)

Published: Mon Jan 13 2014