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
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