Chad Orzel recently graced the blogosphere with his diatribe, Why Does Excel Suck So Much? He goes into Excel’s shortcomings in great detail, starting with its graphic capabilities. He starts by displaying a typical chart turned in by his introductory physics students, showing the results of an experiment to measure how the strength of a magnetic field varies with applied current.
What’s wrong with this chart?
The first point Chad makes is that a line chart was used where an XY chart was called for. It is a shortcoming of Excel that these terms for the two types of chart were originally assigned by the first Excel programmers back in the bronze age.
This is one of the first lessons learned by first-time Excel chart makers. The little icons used in the Chart Wizard show lines with markers for line charts and markers only for XY charts, and the labels further confuse the issue by subtitling XY charts “Scatter charts”. There is no difference in chart series formatting. Line charts and XY charts allow you to format your series the same, with or without markers, with or without lines.
The difference between these two chart types is that Line charts treat the X values as non-numeric labels, regardless of any value we humans infer from the numbers. This has to be explained many times a day on the various online forums, because people write in to ask why their charts don’t look right. At least these people are more aware than Chad’s students, who apparently don’t notice anything amiss with their charts.
I don’t mean to belittle Chad’s major point or Chad’s students. The nomenclature and icon usage in the Chart Wizard is confusing. Chad would help his students by giving them a five-minute demonstration of introductory Excel charting techniques: X in the first column, Y in the second, select the range, choose an XY chart. Actually, Chad should start by beating a little common sense into them: look at your data, look at your chart, does the chart look like it represents the data? At least you should know something’s wrong, even if you don’t know what.
The same data in an XY (or Scatter) chart at least shows a consistent and expected electromagnetic behavior.
What else is wrong with this chart?
Chad states it well:
“There’s the godawful grey background, the inexplicable pastel color scheme, and the axes running right through the middle of the plot, rendering them completely illegible.”
It’s those unsightly default formats, designed by the painters of the cave murals at Lascaux. Actually, the Lascaux artists had a better sense of color, even if they had fewer colors on their stone age palettes. I suspect the first programmers of Excel’s charting machine took the 16 original Windows colors and mixed and matched them as well as they could. Given their lack of color sense, and the inability of Windows 3.0 to satisfy even their limited color sense, the best they could do was barely tolerable. We’ve been stuck with these defaults ever since.
It’s a tedious matter to change these formats. Remove the gray background first of all. Format the axes so their labels appear in the low position. Make the plot area border medium gray or remove it altogether. Make the axes medium gray, and remove the gridlines, or make them light gray. Use bolder colors for the data points and lines.
You can then save this as a user-defined chart type, and it will be available to apply to any future charts. There are weaknesses to this approach: it is difficult to exchange these user-defined chart types between computers, the chart types replace the titles and data labels of your new chart with those of the original chart used to create the user-defined type, and sometimes the user-defined chart gallery workbook becomes corrupt, as on three of my machines.
The chart default formats have been changed in Excel 2007. At first glance they look to be improved, though I still find myself changing chart formats substantially in Excel 2007. The lines are too wide and some lines are still too dark; the colors are better but still not great. There are also several other reasons Why I don’t like Excel 2007 charts. But at least they don’t start out quite as ugly as in Excel 2003 and earlier versions.
The good news is that Excel 2007′s user-defined charts are stored in individual chart template files, which are more easily shared among users, and which seem immune to the corruption I’ve suffered with Excel 2003′s user chart gallery.
Okay, what else?
Chad doesn’t find Excel’s trendlines useful. He and his students probably have problems turning the trendline coefficients into predictions, but that can be remedied by increasing the number of displayed digits of the trendline’s number format before copying the numbers.
This is not convenient, since the numbers are in the chart, and not in the worksheet. There are means of calculating these coefficients in the worksheet, for example, the LINEST worksheet function. LINEST is a fairly powerful regression tool, and tutorials abound on the internet showing how to use it to the fullest: Extended Statistics and Polynomial Fits with LINEST, How to use Excel’s LINEST() function, and Trend Analysis With Excel to name a few, and even LINEST on the Microsoft web site.
In addition, Excel comes with an add-in, the Analysis ToolPak, which contains a number of useful analysis tools, including a regression module. Chad couldn’t find the Analysis ToolPak in Excel 2007, but it’s there, in the Analysis group at the far right hand edge of the Data tab of the ribbon. It’s possible he didn’t install this add-in; I always just perform a full installation, so I am not familiar with how to custom install the ToolPak.
Chad also doesn’t like the initial lack of axis labels in Excel 2007′s charts. These are in fact easy to apply in Excel 2003 and earlier, because the Chart Wizard includes a step where the user can easily input chart and axis titles. In their wisdom, Microsoft removed the Chart Wizard from Excel 2007, so the chart pops out before there is any opportunity to customize it. Instead the user is forced to trek through the contextual Chart Tools tabs. Hint: look for the Labels group on the Chart Tools > Layout tab.
Chad was pretty straightforward about his criticisms. I agree in principle with most of his statements. I can only argue around the fringes, and only because I have an unhealthy amount of experience with Excel.
The comments following Chad’s posts are a different story. There is much chest pounding and Microsoft bashing. As I said to a colleague, “Oh my, such anger, such invective, such inability to use Google.” One comment lamented on the poor support for Error Bars in Excel Charts. Another decried the inability to Plot an Equation in Excel.
In fairness, a good fraction of the comments pointed out workarounds or approaches similar to those cited in this article. A number suggested alternatives to Excel, both expensive commercial products and open source offerings. A few suggested that Chad enlighten his students about the secrets of Excel XY charts.
Excel is everywhere, which is both a blessing and a curse. It’s a blessing because it means there is (or was, pre-2007) a standard platform and interface that nearly everyone had on their desktop to accomplish spreadsheet-related tasks. My kids use it in school, my wife uses it, my clients use it, my dad uses it (not my mom though, but she’s an expert at Google). Excel’s omnipresence is a curse, because it leaves little incentive for Microsoft to improve. There’s no market share left to capture, aside from users of older versions, and these users upgrade mostly for nontechnical reasons.
Even when Microsoft took the opportunity to retool Excel and Office, Excel received no significant enhancements to its useful features. Excel did get a facelift, with fancy colors and themes, and more rows and columns. But the charting engine has hardly any new features, and some existing features were deprecated, broken, or otherwise compromised. The creaky statistical functions received a little attention, I guess, but they are still being criticized by the technical community. All that comes to my mind is the way the trendline formula algorithm was waylaid by an errant algorithm intended to correct rounding errors; fortunately this Excel 2007 Regression Error has been Fixed in SP1.
So, does Excel “suck” or what?
I am of mixed opinions on this question. There are problems with Excel. The statistical functions are nowhere near world class, nor are the default graphics capabilities. The new interface introduced in Excel 2007 seems like change for the sake of change; it has definitely hurt productivity greatly. The whole Office help system is pathetic; fortunately there are hundreds of third-party sourcesof help, and Google has the capability to find them.
In general, though, I think that Excel is a pretty good package, with a great deal of good characteristics, and some problems. The greatest thing about Excel is its extensibility through VBA. If you want a feature that doesn’t exist, you can write a little code (or a lot) to implement the feature. On this web site and blog I’ve shown innumerable examples of code and manual techniques to make Excel do what you want.
Excel has been good to me. I’ve used it extensively in my own work as a scientist and engineer, and more recently as an Excel developer it has paid my mortgage. A cynic might welcome the burgeoning business in upgrading existing Excel solutions to be compatible with Excel 2007, and I’ve done a number of these projects. Because of unexpected gotchas, though, I consistently underbid these jobs, and I’ve eaten more hours than I’d like. (Bidding is a tricky business. You estimate how long something will take, then double it, to compute your hours. Except in Excel 2007 projects you should double it again, and maybe a third time, to allow for the unexpected.)
Microsoft would do well to read Chad Orzel’s post and all of the similar ones they can find. Microsoft should look at the criticisms from the point of view of a regular user of your products. While Microsoft employs many heavy users of Excel, they are not the programmers and designers, but the accountants and finance people who work with numbers and dollars all day. The major users are not Ma and Pa Kettle and anyone else who they can pull in off the street to muddle through a focus group session.
What do you think? Leave your opinion in the comments section below.