Sometime back I wrote a post on **making control charts using Excel**. The chart was initially created in Excel 2003. The chart consisted of the plot values, the average line and upper & lower control limits. While the main data series was a line chart, the average, upper and lower control limits were plotted as dots of an X-Y and then were extended into lines using the horizontal error bars. Shown below is a sample.

The main data series referred to a range in the sheet.

The average, upper and lower control limits were dynamic in nature and named ranges were used to provide them with values.

## From Excel 2003 to Excel 2007

Coming to the point – All was hunk dory when I made this in Excel 2003. Then someone pointed it out that the charts did not come out looking all that great in Excel 2007.

On closer inspection, I found that the disproportionately large error values that were used to create the error bars were causing this errant behavior . I had, in all my enthusiasm to make the chart ‘future-proof’ had provided an error value of 1000.

While that worked well in Excel 2003, it make a caricature of a well meaning chart in Excel 2007. Since the chart’s plot area remained the same, the actual data values (some 42 data points) got squeezed into a proportionately smaller area compared to the 1000 assigned as the error value. The result was that it appeared as if all the actual data points had been aligned to vertical axis !

This was easy to fix however, and when I changed the error values back to 42 (equal to the number of data points) from an earlier value of 1000, the chart regained sanity.

## From Excel 2007 to Excel 2010

**Note : ** Please see update below.

~~Guess how the chart we rectified for Excel 2007 turned out in Excel 2010 (beta). Here’s how:~~

~~The average, upper and lower control limit lines were all gone. Actually they were there but hidden. I could get them back on changing the visual layout of the chart.~~

~~That got us to this point.~~

~~But the formatting was gone! There isn’t chance in hell that someone would be able to tell the upper and lower control limits from the gridlines.~~

~~I noticed another thing that I really could not fathom. While the spreadsheet that we had originally created in Excel 2003, atleast opened up correctly in Excel 2007, when I tried opening it in Excel 2010, the row heights for the rows (where the control chart had been placed) had somehow increased all the way from the default 12.75 pixels in Excel 2003 to something like 409 pixels in Excel 2010). (Apparently the same treatment is meted out to a few ~~**other charts** as well.)

~~The result – dashboards crapped out to the max 🙁~~

*Courtesy:www.icanhascheezburger.com*

Here’s the culprit duly zipped and handcuffed. If you try opening it first with Excel 2003, followed by Excel 2007 and then finally with Excel 2010, you would see what I mean.

**Update**: Tell you what … Microsoft wrote back confirming that the issues have been addressed in the latest builds. So much for saying that the MS doesn’t listen … well they did this time 🙂

To make the error bars “future proof”, all you had to do was put the necessary length into a cell, and use the custom error bar value option, linking to this cell.

Or use two points per horizontal line, at X=0 and X=1. Format the series with a line and no markers; and put it onto the secondary X axis, which is scaled from 0 to 1. Delete the secondary Y axis, so all data will use the primary Y axis.

January 11th, 2010 at 5:38 pm@Jon – The first one would not have been so elegant but second one’s sounds good. Let me try that. Thanks!!

January 13th, 2010 at 3:58 pm