Boxplots using excel

Statistical boxplots can be made by hand, by calculating median, max, min, etc. and formatting a diagram with it. This is _tedious_ but possible. I found this solution (see Source), which works very nicely. It consists of two simple components:

  • Custom diagram type for boxplots
  • A helper function to fill in the values needed for the boxplot

How to install:

  1. Download this Boxplot example, open it with excel and format the boxplot to your liking: boxplot_beispiel.xls
  2. Add it as a user defined diagram type
    1. Select the box plot diagram
    2. Right-click on it and choose “Diagram type”
    3. Select “User defined”
    4. “Add” → Enter Name and Description (Boxplot of data in the order Median, UQ, MAX, MIN, LQ).
  3. Install the helper function (not really needed, but very handy)
    1. Download “boxplothilfen.xla”
    2. Install it using the Add-In manager

How to use:

  1. Prepare data for the boxplot diagram: Select 5 cells
    1. Insert-Function Category User defined, BoxplotWerte
    2. Select the data range you want to boxplot (mouse paint)
    3. Confirm with CTRL-SHIFT-ENTER :!:
    4. You should see filled in Boxplot values for Median, UQ, MAX,MIN, LQ
  2. Draw the boxplot diagram
    1. Select the 5 boxplot value cells
    2. Select Diagram-Userdefined Boxplot
    3. voilà your Boxplot



doris Illner, 2012-03-10 20:14

Hallo Zusammen, ich versuche meine Daten mit einem Boxpot darzustellen. In Excel habe ich die Funktionen Median, Quartil, Maximum und Minimum gefunde. Es gibt jedoch nicht die Funktionen oberes bzw. unteres Quartil.

VG, Doris

Enter your comment. Wiki syntax is allowed:
If you can't read the letters on the image, download this .wav file to get them read to you.