Learning how to make a boxplot on Microsoft Excel 2007 will be tricky, because Microsoft Excel 2007 does not have a function for boxplots. There is a way to create a boxplot using existing plot types, though, and this guide will show you how to do it.
- Create a table. Create a series of data containing median, 25th percentile, 75th percentile, 10th percentile and 90th percentile data.
- Create a plot data table. This table will have five data series like the data series above. The first data series will be the bottom segment. This is equal to the 25th percentile data series.
- Add a formula. You will next need to add a formula in the plot data table for the maximum value of each data set. This will be the maximum minus the third quartile values.
- In the plot data table, add a formula for each data set's third quartile value. From the summary table, this is the third quartile minus the values of the median.
- Add a formula. In the plot data table, add a formula for each data set's median value for the median minus the first quartile values from the summary table.
- Copy the first quartile value from the summary data table, and in the plot data table add a formula for each of the data sets' first quartile value. You are halfway done with making your boxplot in Microsoft Excel 2007.
- Copy the first quartile value from the summary table. In the plot data table, add a formula for each of the data set's first quartile.
- In the data table, add a formula for each data sets' minimum value. From the summary table, this is the quartile minus the minimum values.
- Create the plot. In the plot data table, select the range containing the third quartile, median and first quartile of all the data sets.
- Open the "Insert" ribbon. Click "Column" on the charts. Select "Stacked Column" from the "2-D Column" type charts.
- In order to build a boxplot in Microsoft Excel 2007, on the "Chart Tools" section of the ribbon, click "Select Data" under "Design." Modify the series order so that series three will be the bottom segment on the chart and series one will be the top segment on the chart.
- On one of the columns in the chart, click on the bottom segment. On the ribbon, select "Layout" under "Chart Tools." Click "Error Bars" and select "More Error Bars Options." The "Format Error Bars" window will appear.
- Select "Minus" for direction. Select "Custom" for the error amount. Click "Specify Value" and select the range for all the minimum values in the plot data table for the "Negative Error Value" in the "Custom Error Bars" window. Click "OK" to exit this window. Click "Close" in the next dialog box to return to the chart.
- Click on the top segment of one of the columns in the chart. Go to the "Format Error Bars" window. For the direction, choose "Plus," and for the "Custom Error Amount," use the maximum values in the plot data table for the "Positive Error Value" range.
- Right-click on the segment of one of the columns on the bottom of the chart. Click "Format Data Series." Set the "Fill" to "No fill." Set the "Border Color" to "No line." Close the window.
- Delete the chart legend. Format the chart colors, add a title. Make changes to anything else you would like to for style.
- On the plot data table, select the rows. On the ribbon under "Home," select "Format." Click on "Hide Rows" under the "Hide & Unhide" sub-menu. You have now made it to the last step and built a boxplot in Microsoft Excel 2007.
While the process is complicated, it is not impossible. Hopefully, you found it easy to learn how to make a boxplot in Microsoft Excel 2007.