Creating a boxplot in Excel 2007 with negative values in dataset

Keywords: Excel, boxplot, box plot, stem and leaf plot, box and whiskers plot, negative values

Dowload:

Since my previous articles on boxplots in Excel, Improved advanced box-plot and whiskers chart in Excel and Box plot for Excel 2007, I have recieved a lot of questions on how to do boxplotswith negative values using Excel, since it would break the original method that I suggested.

There is a way, and I will guide you through it step by step. Let’s say you have the following data:

As you can see the boxplot (from the example found in Improved advanced box-plot and whiskers chart in Excel) is broken.

Now we need to trick Excel by adding an offset to your data so that all of them are in the positive range and show a “fake” y axis to make them appear in the negative range.

1. Add an offset to your data. Keep your offset in mind (lets pretend its 60 as in our example)

2. Create a dummy data series, with arbitrary values (though it’s practical if they are in the range of your real data).

The boxplot now looks a bit warped, like this:

3.  Now, move your 3 offset box-plot series to the secondary axis ( you will need to use the up/down arrow to select your invisible data series).

4. Set the scale on your secondary axis to appropriate fixed values (lets say 0 and 90 in our hypothetical example).

5. Set the scale on your primary axis to fixed values that are offset (subtracted) with the same value you used  to offset your boxplot data, in our example -60 and 30 (0-60 and 90-60 gives -60 and 30)

6. Hide the dummy series by making no fill and no line (right click -> format data series).

7. Hide secondary axis labels under (right click -> format axis).

Your boxplot should now look like this:

A “faked” boxplot showing data series with values in the negative range! Mission accomplished.

Popularity: 9% [?]