E
Edmund
edmund dot r at gmail dot com
This is a HTML post.
I've searched high and low for a simple Excel box-plot that can do outliers, does not require drawing each line individually and can have more than one box in the plot. I could not find one and so devised one myself. All it needs is the basic summary data and once you create you can save it to your Custom Charts so next time its just a matter of selecting it like a normal chart.
Create Box Plot
EXCEL 2003
Open High Low Close
Q1 Upper Tail Lower Tail Q3 Med Group Med Outlier Group Outliers
Chicken 40 99 9 70 1 54 1 5
Fish 70 217.5 22.5 135 2 120 1 105
2 10
2 227
2 233
You only need to create the box plot once since you can save it to your user-defined custom chart types.
1) Create Open-High-Low-Close chart with the blue higlighted group (must select series in columns in the chart setup).
2) After chart is created add the median series (only the yellow, not x-values) to the the chart.
3) Select the median series in the chart and change it to the secondary axis: Select the top point of an upper tail, this will probably be Series 2. Then use the left or right arrow keys to move to the next series until Series 5 is selected; change the axis. Also, set the secondary axis scale equal to the primary axis.
4) Select the chart and with the chart selected open the Excel Visual Basic Editor. In the Immediate window change the "Series 5" plot type to Scatter Plot by entering;
ActiveChart.SeriesCollection(5).Type = -4196
Then press enter.
5) Return to the chart. Update Series 5 "X-values" in the Source Data|Series dialog with the median group values.
6) Change the "Series 5" plot pattern to no line and and the dashed symbol.
7) Add Series 5 X Error Bars in both directions of a fixed lenght of 0.195 with no end marker and matching the stlye and weight to that of the boxes. If you can see the end bars of these then adjust the width of the chart slightly until they fall in line.
8) Add the outliers series (only the pink, not x-values) to the chart
9) Select the outlier series in the chart, Series 6, and change it to the secondary axis.
10) Change Series 6 to Scatter Plot and add "X-Values" with outlier group as done with Series 5.
11) Change the outlier plot pattern to no line and the symbol you want to use for the outliers.
12) Be sure each axis is large enough to show the outliers and that the axis are equal to each other. Also, it maybe best to switch off Auto scaling for the min and max of both axis.
13) Select the chart and save it to your user-defined custom charts. Navigate to Chart|Chart Type|Custom Types. Then select the User-defined option and click add. See next for data setup for the box plot.
Use Box Plot
EXCEL 2003
Categories Q1 Upper Tail Lower Tail Q3 Med Outliers Outlier Group
Blue 40 99 9 70 54 5 1
Purple 70 217.5 22.5 135 120 105 1
Red 195 236 176 215 210 10 2
227 2
233 2
239 3
1) Arrange your data as above (you can have more or less categories and outliers).
2) Select the highlighted area and select to create a box plot from your user-defined custom charts. Be sure to select series in columns in the chart setup. Note that the outliers will not be in the correct group but this is corrected in the next step. Also you may have to change the scale of both y-axis for the data.
3) In the Source Data|Series dialog select Series 6 and select the Outlier Group (the last column) for its X-values and the Outliers (the second last column) for the Y-values.
4) Box plot!
edmund dot r at gmail dot com
This is a HTML post.
I've searched high and low for a simple Excel box-plot that can do outliers, does not require drawing each line individually and can have more than one box in the plot. I could not find one and so devised one myself. All it needs is the basic summary data and once you create you can save it to your Custom Charts so next time its just a matter of selecting it like a normal chart.
Create Box Plot
EXCEL 2003
Open High Low Close
Q1 Upper Tail Lower Tail Q3 Med Group Med Outlier Group Outliers
Chicken 40 99 9 70 1 54 1 5
Fish 70 217.5 22.5 135 2 120 1 105
2 10
2 227
2 233
You only need to create the box plot once since you can save it to your user-defined custom chart types.
1) Create Open-High-Low-Close chart with the blue higlighted group (must select series in columns in the chart setup).
2) After chart is created add the median series (only the yellow, not x-values) to the the chart.
3) Select the median series in the chart and change it to the secondary axis: Select the top point of an upper tail, this will probably be Series 2. Then use the left or right arrow keys to move to the next series until Series 5 is selected; change the axis. Also, set the secondary axis scale equal to the primary axis.
4) Select the chart and with the chart selected open the Excel Visual Basic Editor. In the Immediate window change the "Series 5" plot type to Scatter Plot by entering;
ActiveChart.SeriesCollection(5).Type = -4196
Then press enter.
5) Return to the chart. Update Series 5 "X-values" in the Source Data|Series dialog with the median group values.
6) Change the "Series 5" plot pattern to no line and and the dashed symbol.
7) Add Series 5 X Error Bars in both directions of a fixed lenght of 0.195 with no end marker and matching the stlye and weight to that of the boxes. If you can see the end bars of these then adjust the width of the chart slightly until they fall in line.
8) Add the outliers series (only the pink, not x-values) to the chart
9) Select the outlier series in the chart, Series 6, and change it to the secondary axis.
10) Change Series 6 to Scatter Plot and add "X-Values" with outlier group as done with Series 5.
11) Change the outlier plot pattern to no line and the symbol you want to use for the outliers.
12) Be sure each axis is large enough to show the outliers and that the axis are equal to each other. Also, it maybe best to switch off Auto scaling for the min and max of both axis.
13) Select the chart and save it to your user-defined custom charts. Navigate to Chart|Chart Type|Custom Types. Then select the User-defined option and click add. See next for data setup for the box plot.
Use Box Plot
EXCEL 2003
Categories Q1 Upper Tail Lower Tail Q3 Med Outliers Outlier Group
Blue 40 99 9 70 54 5 1
Purple 70 217.5 22.5 135 120 105 1
Red 195 236 176 215 210 10 2
227 2
233 2
239 3
1) Arrange your data as above (you can have more or less categories and outliers).
2) Select the highlighted area and select to create a box plot from your user-defined custom charts. Be sure to select series in columns in the chart setup. Note that the outliers will not be in the correct group but this is corrected in the next step. Also you may have to change the scale of both y-axis for the data.
3) In the Source Data|Series dialog select Series 6 and select the Outlier Group (the last column) for its X-values and the Outliers (the second last column) for the Y-values.
4) Box plot!
edmund dot r at gmail dot com