Download Creating a box and whisker plot in Excel and more Lecture notes MS Microsoft Excel skills in PDF only on Docsity!
OPA_T#973_June- 20 - 2017 1
OPA Excel Tips: Creating a box and whisker plot in Excel
What is a box and whisker chart?
A box and whisker plot is a way of showing and comparing distributions. The box represents the middle half of the data (from the 25th^ to the 75th^ percentile), with an additional line showing the middle value (the median or 50th^ percentile). The whiskers show the extreme values and can either show the minimum and maximum values, or as here, the 5th^ and 95th^ percentiles, as often these give a better idea of the distribution of the data than the minimum and maximum. Box plots can be drawn vertically or horizontally.
Box and Whisker charts in Excel
Line and bar charts are easy to produce in Excel. In Excel 2016 a new box and whisker plot has been added. In older versions, stock charts exist, and are similar to box and whisker plots but require data in a very specific format, which isn’t always possible with data that you wish to convert to a box and whisker plot. Box and whisker plots can be produced in older versions of Excel though there are a few steps to go through to convert the data to the correct structure and format the chart, which is a stacked bar chart with error bars. There are other software packages that automatically produce box plots such as Tableau, though most of these require a license. The advantage of Tableau over Excel, as well as there being less formatting required, is that you can add additional information to the chart, such as outliers (values below the 5th^ percentile, or above the 95th^ percentile) as additional data points (dots on the chart). It is very complicated to do this in Excel, involving additional data series and secondary axes so this is not show here. You can produce a single box and whisker plot, or a chart comparing the box and whisker plots for multiple categories. For example the categories could be Fiscal Year, Activity code, RFA, Portfolio. The Y axis will be a numerical range, for example $s or RCR.
OPA_T#973_June- 20 - 2017 2
In this tip sheet, we show how to create a single box and whisker plot like the one above, and how to expand the process to show multiple box and whisker plots on the same chart.
Example 1
Step 1. Decide the data you want to present In this example we will look at Relative Citation Ratio (RCR), recreating the chart that is available in iCite. The y-axis will show the RCR value, the x-axis will just have one value – heart disease data. Step 2. Structure the data Create a new worksheet in Excel and add the data to it in columns, with one column for each category that you wish to display a box and whisker plot for. In the first example we will produce a chart with a single box and whisker plot. We have downloaded heart disease publications from iSearch for several years. To produce a box plot for RCR we copy the RCR column to a new sheet. Then create values for: 5 th^ Percentile (=PERCENTILE.INC(x:y,0.05)), Quartile 1 (=QUARTILE.INC(x:y,1)), Median (=QUARTILE.INC(x:y,2)), Quartile 3 (=QUARTILE.INC(x:y,3)), 95 th^ Percentile (=PERCENTILE.INC(x:y,0.95)) Where x is the first cell in your column of data and y is the last cell.
OPA_T#973_June- 20 - 2017 4
But we don’t want separate bars. Right click on the chart > Select data… > Switch Row/Column
OK
Now to add the error bars. Left click on the bottom bar. In the top menu tabs, select Design > Add Chart Element > Error Bars > More Error Bars Options. Under Direction, select Minus. Then scroll down and select Custom > Specify Values. In the Negative box, select the Q1-5th Percentile value and click OK. An error bar will appear on the chart.
Do the same thing for the 95th^ Percentile. Left Click on the top bar, then go through the same process but when you get to More Error Bars Options, under Direction, select Plus. Then scroll
OPA_T#973_June- 20 - 2017 5
down and select Custom > Specify Values. In the Positive box, select the 95th^ Percentile – Q value and click OK. An error bar will appear on the chart.
Now we just need to tidy up the formatting. Right click on the bottom bar, select Fill > No Fill.
Then we can change the color of the other two blocks to match, and add borders. Right click on each block in turn, select Fill and pick a color, then right click and select Outline and the color e.g. black.
OPA_T#973_June- 20 - 2017 7
Example 2: Multiple Box and Whisker plots on the same chart
In this example we will use the same dataset but show Box and Whisker plots for individual years. Step 1. Format the data The data needs to be in a similar format to the previous one, but with individual years in separate columns. To do this we’ll filter the original dataset on Fiscal Year then copy the RCR values for that year to a new column on the worksheet we are using to create the chart.
Step 2. Calculate the values required for the chart We can then calculate the values required for the chart, as we did above, using the same formula for each year of data but making sure the cell values include all the data for an individual year
OPA_T#973_June- 20 - 2017 8
Step 3. Produce the chart and format it Select the values for the chart in the same way, though this time the chart will default to the stacked bar that we are after without the need to switch rows and columns.
We can then add error bars in the same way as before, selecting a group of cells instead of the single value we selected previously. With some reformatting we end up with something like the version below which tells us that there is little variation in RCR over the years.
More help
There is more help on formatting charts in the Excel help. There is plenty more detail about box and whisker plots on line which can be found via google or another search engine. If you have any questions or comments about this tip sheet, contact OPA training: OPA_Training@mail.nih.gov
0
2
4
6
8
10
2010 2011 2012 2013 2014 2015
RCR
Fiscal Year
RCR for Heart Disease publications
