Spreadsheet Home Page

Introduction

The inspiration for this page came from http://www.tushar-mehta.com/

The focus of this page is the graph below:

What we see here is a quality control type chart that contains data that falls into any one of eight different bands: for example, 3 - 4, 2 - 3 … -3 - -4.The beauty of this graph, though, is every band has a coloured background to it. This page demonstrates how to achieve what the diagram above shows so well!

Error Bars

The secret to the coloured backgrounds for the graph above are error bars. From Excel's Help file:

Error bars graphically express potential error amounts relative to each data point in a data series. For example, you could show error bars that span ±50 units or 40 units … or ±5 percent positive and negative potential error amounts in the results of a scientific experiment. Take a look at the graph that follows:

Here we have the basic data plotted on the graph; and for every data point, we have asked Excel to plot a Y Series error bar that spans 20 units either side of each data point. So, if the data point has the value of 118, then the Y Series error bar starts at 98 and goes up to 138.

How to Plot Error Bars

To plot error bars, first construct your basic graph (and it could be a 2-D area, bar, column, line, XY (scatter), and bubble charts. For XY (scatter) and bubble charts, you can display error bars for the x values, the y values, or both.) and, with your data series selected,

Format
Selected data series
Y Error Bars
Both (for the kind of error bar demonstrated here)
Fixed value … you choose

Play around with this as you need to check exactly what size of error bar you wish.

My Own Colour Zoned Chart

Please note that the colour zone effect relies on having a lot of data points close together. This creates the effect of a continuous colour bar. The fewer the data points that you have the less spectacular will be the effect.

Start with a thousand random numbers: to do that enter =NORMINV(RAND();0;1) in, say, cell A2 in a blank spreadsheet and copy it down to cell A1001: this allows us to have the titles in row 1.

Select your 1,000 data points and press the F11 key to create a chart. If pressing F11 doesn't give you an XY Scatter graph, change it to that type. You should get:

Right click on any of the 1,000 data points you see on your graph and set the series marker to a minus sign with a size of 2 units.

Remove any extraneous formatting: legends, gridlines, borders, plot area, etc that Excel has created automatically for you. Adjust the min and max values of the x- and y- axis to your satisfaction (we used -4 to +4 for the y-axis and 0 to 1000 for the x-axis). Remove the solid line used for drawing the axis. Also, change the y-axis attribute so that the x-axis crosses at a value of -4.

Now, add 7 more series each with a 1,000 points. The values in the first series are all -3, the second series consists of all -2 ... the fourth is all zeros, ... the last are all +3. Your spreadsheet should look like this:

Select the first new data series (the -3s) and add it to the chart. Format the data series as follows: Make sure you DON'T set an X Axis series; set the markers to none. Set the line style to the last option in the drop-down list (it is a rectangle with about 25% fill). Set the line color to red. Set the line weight to dots (the first option in the drop down list).

Step 5: Add a y-error-bar (the minus type) with a value of 1.

The result at this step will be a solid 'bar' of black in the chart. This is the cumulative effect of all the error bars.

Double-click the (error) bars to format them. In the Patterns tab, set the line style, colour, and weight as for the series itself (exactly as in the previous paragraph). In addition, select the Marker type to be the vertical type without the horizontal cross line (it's the second option in the Marker section of the Pattern tab).

You will find the result is that the 1,000 data points show up against what seems to be a lightly coloured red 'bar.'

Repeat the process for each of the other series. For the mean (zero in this example) series use a + and - error bar of with a fixed value of 1. For the +1, +2, and +3 error bars use + error bars with a fixed with of 1. The final effect:

I am deeply indebted to Tushar Mehita for letting me in on this technique. I have made a few changes to Tushar's page and have sent him a copy of this page for his approval.

Duncan Williamson 24 February 2002

Duncan Williamson
24 February 2002