Chad Orzel recently graced the blogosphere with his diatribe, Why Does Excel Suck So Much? [Sorry, the link is no longer available] 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: MS EXCEL: LINEST FUNCTION and How to Use LINEST Formula in Excel to name a couple, and even LINEST function and WorksheetFunction.LinEst method 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.
And…?
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.
dermotb says
Eloquently put, Jon. I agree completely – Excel is a wonderfully flexible business tool, but once Microsoft achieved market dominance, it has neglected the polishing that would have made it a professional tool.
Naomi B. Robbins says
The bottom line is that yes, Excel does suck for casual users who do not change defaults or understand the assumptions Excel makes (e.g., line chart vs. XY chart described above.) No, it does not – it is very powerful – for experienced users who know VBA. But there is more powerful software that is easier to use for tasks not on Excel’s menus.
Jon Peltier says
Dermot – Thanks for your take on this issue.
Naomi – Thanks for your thoughtful assessment.
I’m honored to have two respected colleagues comment almost before the ink on this post has dried.
Anonymous says
To say Excel sucks (for the reasons pointed out) is like saying “my car sucks it does not fly”
Jon Peltier says
I don’t agree with your analogy.
Having difficulties knowing which chart type to use is akin to using your own chamois cloth to clear the rain off the front window because you aren’t aware of the wipers. Having unsuitable chart formatting defaults is like the car seat being all the way forward, the seatback reclined all the way, and the radio on full blast whenever you start the car.
Your “my car does not fly” analogy is almost like not being able to make a Gantt chart or boxplot in Excel out of the box. Not as bad, really, because I can’t redesign my car to fly, but I can carry out some manual procedures or write some code to get Excel to do these things.
Ken Puls says
Does Photoshop suck? It’s the premier graphics software in the PC market, immensely powerful, and yet it takes me forever to do anything because it’s too complicated or way overkill for what I want or both. I don’t think it sucks though, I just don’t believe I know it nearly well enough to make good use of it. In short, my skill set, as it applies to that program, is the thing that sucks.
Excel doesn’t suck, in my opinion. I think it is a very powerful program that does a lot of things. Just because I can’t figure out why the charting engine does what it does has never left me feeling that it was Excel’s fault. I get frustrated that I can’t make it do what I want. Maybe that’s too introspective and accepting, I don’t know, but that is my feeling.
Despite that attitude, there are definitely ASPECTS of Excel that do suck, of that there is no question. “Help” is one, and charting obviously does have some issues. User interface design and intuitiveness is also a major issue, but you know what? That is actually a major issue in probably 90% of the software on the market. It doesn’t make it right for Excel to fall into the pattern, but it is a truth. The majority of software is built and driven feature first, not around making it easy to get things done as efficiently as possible. From defaults to the screen layouts, those are areas that the majority of software manufacturers would be wise to go and study their users to see how THEY work, rather than how the designers think they should or do work.
It’s funny, you know. I look at both the posted graphs and they don’t mean a thing to me. Is it because they suck, or because I don’t know anything about the subject? I’d say the latter, and no amount of complaining about it is going to help me until I invest at least a little bit of time in learning. As you pointed out, Jon, it would have been wise for the instructor to spend a little time teaching the students about charting in Excel. I’m sure he’s putting in the time to teach about the main subject, so why would you just expect that they can use Excel?
At any rate, if you want to see something that really sucks, go back to doing it by hand. Trust me, in accounting I’ve done both. THAT sucks.
Bob Phillips says
Ken,
Your comments may be accurate, but Excel should be leading the trend, not just using the excuse that others suck, and yeah Excel sucks but lots of people use it.
Even without knowing anything about charts, surely you get smacked between the eyes by that godawful grey plot area. I know its gone in Excel 12, but look what they introduced to make up for it.
The problems in Excel have been around forever, and there seems to be no desire at MS to improve it. Instead we get Excel 12, the chav spreadsheet, all bling.
Excel is in dire need of a release that brings the functionality and fundamentals into the 21st century, not revamp the superficial gloss again.
Other spreadsheets such as OO are making inroads, if Gnumeric gets its act together, Excel may well suffer. By the time that is recognised, it’s game over.
Mike Alexander says
Really? Another post about how Excel sucks?
I completely agree that a little training could go a long way here.
I have always thought that the world of Academia misses the boat in the area of toolset training. How are Chad’s students (or Chad for that matter) supposed to know how to effectively use Excel if there are no courses available for their level of usage. I know that most schools have Spreadsheet and Database courses, but none at the level needed for the real world.
Anyone that has spent one day outside the Academic realm in the last ten years, would know that basic Excel training just doesn’t cut it in real-world scenarios. And that is what you get in the colleges and universities right now – basic Excel training. You would laugh at how many MBAs I encounter in my everyday operations that come out of school (top tier schools) never knowing how to analyze data with a PivotTable.
I don’t agree that there is an undue burden in formatting or configuring a chart. No software will ever get you 100% of what you need. To expect Excel to come out of the box with “one-touch” capabilities is not only unfair, but naive. Instead, Excel gives you multiple ways to customize your work, and save that customization for future use.
As Ken pointed out, we can pick any of the top 10 best selling software and write a diatribe about how ‘It Sucks’.
No, Mr. Orzel, what sucks is your school’s Microsoft Office immersion curriculum. Give them a solid foundation and watch them improve.
Jorge Camoes says
Paraphrasing Churchill: “Excel is the worst charting tool, except for all those other tools that have been tried from time to time.”
People usually learn Excel from the IT narrow perspective: “what can be done, and how to do it”. The right perspective is, obviously, “why it should be done this way, and how to do it”.
Yes, Excel training sucks.
JP says
Every program has default settings, which the end user must tweak to their own specifications. How can any program (sucky or otherwise) know how their end users want their data presented?
The designers of Excel had to pick a color for the default chart background. If they picked something else, we’d just be complaining about that color instead. Or, if you could pick the background color before the chart was generated, some people would complain about the extra step before the chart appears.
The point is that when your program becomes very popular (if I’m not mistaken, Excel is considered one of the best programs ever written), there will always be a vocal minority who complains about anything you do.
Also, it’s a long stretch from showing how a few charts are bad, to saying the entire program sucks. If I don’t like a part of a program, I say so, but I don’t say the whole program is bad.
Larus says
I would use R instead of Excel. See http://www.r-project.org, http://www.statmethods.net/,
Jon Peltier says
Larus –
Many people have mentioned R, and it’s on my list of new tricks to learn. Kelly O’Day of Process Trends has written several tutorials on R for Excel users. However, I doubt a third party package such as R is a viable solution for 99% of Excel users.
Naomi B. Robbins says
I sloppily wrote Excel when I meant Excel charts; I suspect others did the same. This discussion is about charting and not Excel in general.
To anonymous on the car analogy – We judge a car by how well it performs what it was designed to do. Certainly, one of the things Excel charts was designed to do is communicate numbers using bar charts. Have you ever drawn a pseudo-3-D bar chart using Excel and asked your audience to read the values to you? I have. Thousands of times. Almost everyone underestimates the values of the bars in Figure 2.6 of “Creating More Effective Graphs”. I could offer many more example of situations where Excel charts mislead the audience. The critics of Excel were not just complaining about default formatting.
There are efforts to make R accessible to Excel users in addition to Kelly O’Day’s tutorials. Watch for the forthcoming book,” R Through Excel: A Spreadsheet Interface for Statistics, Data Analysis, and Graphics” by Heiberger and Neuwirth. Amazon says it will be out in July.
Colin Banfield says
“The designers of Excel had to pick a color for the default chart background. If they picked something else, we’d just be complaining about that color instead. Or, if you could pick the background color before the chart was generated, some people would complain about the extra step before the chart appears. ”
There’s a pile of research done by data visualization specialists (Naomi included) that could be easily used as the basis for chart defaults. As it stands, Excel defaults violate virtually every recommended principle.
The solution is simply to bring Excel charting into the 21st century, taking into consideration data visualization research work and the needs for business intelligence, for example. The basic structure of Excel charts haven’t changed much in nearly 20 years. Having said that, there’s a great deal of flexibility built-into the charting tool (and Jon has demonstrated this perhaps more than anyone else on the planet), but most of that flexibility is beyond the capability of most casual users.
I do think that PivotCharts really suck though. PivotCharts are completely unable to properly represent the data in the underlying pivot table…unless there is only a single dimension (level) in the pivot table. Add multiple dimensions to the pivot table and you’re screwed. The problem, of course, is that the pivot chart thinks like a standard chart (designed to represent data in a table) and not as a multi-dimensional data charting tool. The pivot chart gets my vote for one of the worst implemented features in all of Excel.
Jon Peltier says
“The [original] designers of Excel had to pick a color for the default chart background.”
The designers of Excel 2007 had the opportunity to correct the ills of the legacy charting defaults, and in fact, they did pick a better background. Unfortunately the “improvements” included themes and innumerable visual effects which have somehow become synonymous with “Professional Charts” thanks to BI companies who know computer graphics but not information visualization. Microsoft owns Dundas, one of these purveyors of “Professional Charts”. Microsoft even has a Business Intelligence group, with their own Business Intelligence blog, but I haven’t seen anything there which is either valuable or unique. Microsoft Labs came out with a Chart Advisor to help people decide which chart to use. Rather than basing recommendations on visual design principles, however, the algorithm strongly weights in factors like “57.3% of users would make a pie chart out of data like this”, so the results are disappointing.
As Stephen Few states in Excel’s New Charting Engine: Preview of an Opportunity Missed, Microsoft missed their chance to make meaningful improvements to charting. Instead they opted to dazzle users with effects that would make Industrial Light and Magic proud.
Peter Jung says
I think the answer to whether Excel sucks should depend on how it is being used. I think it’s a wonderful tool for distribution purposes, for the following reasons: for better or worse, almost everyone has it; the spreadsheet format makes for easier viewing than delimited plain text; auto-filters are wonderful; and having multiple tabs enables me to include documentation and figures (not generated by Excel, btw) along with the table in one neat package. For simple calculations it is usually adequate; however, for sophisticated statistics most authorities consider it hopeless.
My beef with Excel is not with its capabilities. Excel is perfectly capable of generating wonderful charts (from what I saw in Stephen Few’s book, “Show Me the Numbers”). My problem is (to borrow a term from Thaler and Sunstein’s book, “Nudge”) with its poor “choice architecture.” Its defaults are often, as mentioned in this discussion, thoroughly unappealing. Furthermore, Excel makes it all too easy to make bad choices (e.g., exploding “3D” pie charts with negative numbers) and makes it difficult to make good choices (why is it so hard to generate things as fundamental as boxplots and histograms?). Excel thus essentially encourages and trains bad habits to people who are unaware of Tufte’s principles. (This is an important point! Humans end up hurting themselves because of the way their choices are presented to them.) If you end up having to use macros, scripts, or other tedious workarounds, you might as well abandon Excel and use a programming language better suited to the task, like the aforementioned R. R’s defaults are sensible, and you can exercise flexibility and customization without any real effort. Computations are easy, statistics are cutting-edge, and the quality of its graphical output is second to none.
Yes, I am yet another user of R, and I am somewhat surprised to hear assertions that R is harder to use than Excel. As has been pointed out multiple times in this discussion, most Excel users don’t know a whole lot about Excel. I don’t think this is an accident, nor do I think all of the blame should be placed on a lack of training. I find Excel’s user interface rather opaque. Trying to accomplish relatively simple tasks often involves hunting through menus and pop-up windows, navigating unhelpful help, and combing the internet. On the other hand, R’s documentation is clear, transparent, and self-contained. I’ve never turned to the internet, instead relying on two things: help.search() and help(). While I am confident predicting R’s behavior, I am to this day learning of some Excel behaviors (default or otherwise) that no reasonable person would predict. Often, these “features” do their work unnoticed and without warning, which can get you in a whole lot of trouble. Several examples are discussed in Patrick Burns’ eloquent essay, “Spreadsheet Addiction,” at http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html. As Burns says, “The hard way looks easy, the easy way looks hard.”
Jan Karel Pieterse says
I agree Excel charting has problems.
I’ll never forget my switch from Quattro Pro to Excel 5 ages ago. I detested Excel’s charting engine. QPW’s was far better, even then.
Stating that 3D bar charts are misleading however is not something we can hold against MSFT. In my opinion, it is not MSFT’s job to decide which chart types are needed to properly represent the data.
It IS their task however, to present properly designed defaults for each available chart TYPE, so the message about the data that particular chart type needs to convey is done in the best possible way. If the user wants to dress up such a chart afterwards and thus wreck it, so be it.
Selecting the right type of chart is a task of the user and that user needs proper education on how to present data graphically in such a way that the representation is not misleading and actually shows and clarifies the effects hidden inside the data.
The user of course also needs to be educated in how to achieve such charts with Excel.
Jon Peltier says
Peter –
Thank you for your very thoughtful analysis. I think the phrase “poor ‘choice architecture'” is a good description of many of Excel’s problems.
Jon Peltier says
Jan Karel –
“. . . it is not MSFT’s job to decide which chart types are needed to properly represent the data.”
This is very true. However, Excel offers many horrible and ineffective chart types, and Excel makes it too easy to use these bad types. This is the “poor choice architecture” mentioned by Peter in the comment before yours.
Naomi B. Robbins says
“Stating that 3D bar charts are misleading however is not something we can hold against MSFT.”
Not all 3D bar charts are created equal. Take a few simple numbers like 1, 2, 3 and create a 3D bar chart of them using Excel and also Microsoft Graph in Powerpoint. Compare the results. I hate both of them but think that the PowerPoint chart is far superior to the Excel one. Different software packages use different algorithms to create these charts and some mislead more than others. Other packages are designed so that you read the value from the front of the bar.
By the way, in its favor R does not include 3D bar charts (unless you count contributed packages that users send in.) As Peter said, its defaults are sensible.
Primo says
I’m not sure why Excel is getting all the blame here. That first graph as apparently turned in by the students is just wrong. If they can’t see that then it’s clear that they haven’t understood waht they’ve been learning about magnetic field and current. If Excel had been designed as Chad Orzel wants then his students would have turned in a load of identical, correct graphs, but there would still have been no understanding. He should be grateful to Microsoft for flagging up which students need further help. :-)
Another point is that when I was studying science graphing was a major part of all courses, and as such it was taught in detail, and poorly designed or layed out graphs would result in poor marks. Do even teachers now expect the computer to do it for them?
Jon Peltier says
Good point, Naomi. Actually, Excel up through 2003 and MS Graph, the applet that provides charting support in PowerPoint and Word up through 2003, use the same charting engine. MSG is adjusted so it uses its own internal data sheet rather than an Excel worksheet, but other than that they are about the same.
What Naomi likes better about MSG charts can be explained by showing examples of MSG and Excel charts. Here is a MS Graph 3D bar chart:
The bar thickness reaches all the way to the back wall of the chart, so you can easily see where the tops of the bars are with respect to the gridlines.
Here is the same Excel chart:
The chart is formatted such that the bar thickness is less than the chart thickness. The bars do not reach the back wall, so it is necessary to guess where the tops of the bars line up.
The difference between these charts is a setting on the Format Series dialog > Options tab called Gap Depth. In the MS Graph chart, the Gap Depth is zero by default, so there is no gap between adjacent rows of bars, or in this case, between the bars and the back wall. In the Excel chart, the Gap Depth is 150 by default, meaning the gap between adjacent rows of bars is 150% as thick as the bars, and the gap between the bars and the back wall is 75% as thick as the bars. If I change the Gap Depth of the Excel chart to zero, it is now indistinguishable from the MSG chart.
Likewise, I could change the MSG chart’s Gap Depth to 150 to make it as bad as the Excel version.
Note that I’ve also adjusted other formatting (font, etc.) to make the charts more alike.
Ken Puls says
Okay, now that is interesting… I’d definitely agree that the Excel default there IS misleading. In fact, unless you knew your data very well, that could possibly be difficult to identify without deeper examination. That’s not good.
Jon Peltier says
Primo –
This is a good point, which I made in the article, but not strongly.
1. An introductory science class should give at least rudimentary instructions into the creation of charts, starting with hand-drawn and leading to the use of whatever common software the students may have at their disposal.
2. Students should know enough about the behavior they are measuring to know whether their chart is correctly representing their understanding.
The first chart in Professor Orzel’s rant shows that neither of these were achieved.
Colin Banfield says
…and then there’s the Chart Advisor add-in. This tool is an even bigger embarrassment than the standard Excel defaults. Here was an opportunity for some “redemption.” Instead, it’s made the Excel charting issues worse. This tool is testimony that the folks at Microsoft simply don’t get it, period. It’s chilling to think of what might be in store for Excel 14…
Jan Karel Pieterse says
Naomi: “Not all 3D bar charts are created equal”
Correct of course and not my point.
Key is this part in my post I guess:
“…present properly designed defaults for each available chart TYPE, so the message about the data that particular chart type needs to convey is done in the best possible way…”
If MSFT would get that right it would help a lot. They obviously did not get it right for the default 3D chart of Excel and (by accident?) did get it right for PPT.
Jon Peltier says
“They obviously did not get it right for the default 3D chart of Excel and (by accident?) did get it right for PPT.”
Which somehow conveys the message that 3D charts are okay, when in general they work against displaying data effectively. My point is that Excel’s charting functions would be improved by removing half of the chart types, or at least by making it harder to get to them. Maybe force the user to read a disclaimer and click “I Agree” (like a license agreement, eh?).
Naomi B. Robbins says
I second Jon’s comment. The Excel – PowerPoint example was just one reason why I dislike 3D charts. Even if PowerPoint’s default is better, it still is unacceptable to have to read some bars from the back and others (from different software) from the front. Another problem with the 3D charts is that they imply we have more informtion about the data than we do. Three dimensions should correspond to three variables, but in this case we only have two.
Peter Jung says
Jon, thanks for the link to perceptualedge. This is a great new resource for me. Naomi, you have some very insightful work there. I also noticed one of Stephen’s blog posts from last year made the same point I made, applying a principle from an economics book to charting software. And I thought I was so original…ah well.
Abhishek Tiwari says
Nice article, and great communication between blog writer and reader. I love these kind of blogs where blogger have real balanced answer to the problems. Chad Orzel’s opinion is very much one sided, if you just want to criticize anything then off course there nothing which is not faulty- that includes R too.
sam says
I think the “excel sucks” comment is a result of a gap between expectations and supply.
People who hate excel do so not because they dont like the product but because they realise that there is so much scope for improvement….some of which is so obvious to them but not to MS….
After 2003 almost every one expected the next version to be revolutionary…with all the “known issues” to be sorted… and a host of improvements in “features” and large number of “power formulas”…..instead all we got was more colors and a reorganized, difficult to customize UI…
Once you realise that Excel is a monopoly and till there is a substantial loss of market share it is not likely to improve further.. the expectation reduces and you learn to live with what you have…
Will Dwinnell says
I agree that Excel can do many useful things, but it sure doesn’t make it convenient.
Jon Peltier says
Will –
This inconvenience keeps me in business, but sometimes I still find it aggravating.
George Story says
I have used Excel as an application development platform since Excel 3.0.
(How does 20 years go by so quickly?)
The National Ocean Service has many thousands of lines of vba code in production. The functions that Excel adds have served us well for plotting, displaying and editing our data products. With the release of Office 2007, I have begun a move to migrate away from Microsoft products all together for application development. I feel that they have decided that they are not going to support the Office platform for development. We are moving on.
Hunter says
I would recommend using Lyzasoft instead of Excel. Their graphs are gorgeous, you have more options, and they are much easier to set up. You use Lyza for free at http://www.lyzacommons.com.
Jon Peltier says
Hunter –
Many folks just want to use a program that’s installed on the local desktop, and that is the context of this article.
For those who want to use a web-based charting program, I’d recommend Tableau.
I opened a Lyzasoft example, and got a notice about the Java applet. Many people run screaming away from this kind of notice, however innocuous it may be. On closer inspection, Lyzasoft does seem easy to use. I’ll have to try it out.
Rick Gilbert says
Jon,
A colleague was struggling with the charting defaults in Excel 2007 and saved his painfully customized XY(scatter chart) as a template for re-use. Easy enough. But when he tried to create a new chart using the template, Excel insisted on creating a line chart, plotting the original X and Y series against a generic 1,2,3… axis. I thought maybe there was a data problem with the new chart, and Excel had coped as best it could. So I tried the same exercise myself. I charted some random data as an X-Y chart, saved the chart as a template and then created a new chart using the template and THE SAME DATA RANGE on which the template was built. Excel politely ignored my template and gave me a line chart, retaining most of the custom formatting. I tried describing the problem in a Google search, but got no meaningful hits, so thought I’d ask you if you’ve seen this or seen it reported elsewhere – and more importantly, do you know of a fix? I’ve found one clunky workaround. If I insert a scatter chart using Excel’s built-in chart types and then change the chart type using my custom template, I get what I was looking for. Apparently the “X-range guesser” for scatter plots doesn’t work for templates.
Haven’t tried this in my beta of 2010 at home, but that would be of little use here in the office, where we are just at the tail of having converted (I won’t say upgraded) to Office 2007.
Jon Peltier says
Hi Rick –
I just did a quick test in Excel 2007 SP2, made myself a custom XY chart template, then applied it to the same data used in the original chart.
The result was a line chart, with a plain old category axis, and two data series, one which had been the X values of the single data series in the original chart, and the other which had been the Y values.
The result in 2010 was the same, a line chart with two series. In Excel 2003, the result was instead the expected XY chart.
Looks like a bug in 2007 & 2010.
The range checker worka as expected, assuming the chart is a line chart, because Excel assumes for a line chart the first column of data is just another series unless it has a different format, like text or dates, or unless the other series have a header and the first one does not.
Deke says
A little late, but as someone who has used excel for decades, the new charts are an extreme dissapointment. The ability to edit and modify is severly dumbed down to the point where I simply need to look for another tool. I am really disgusted at the poor usability work that went into all the 2007 products. It stinks to high heaven of designers posing as usability experts. I suppose MS is once again trying to emulate Apple, but as always they miss the art and science. I cannot believe half of their new designs were based on anything approaching solid user study and testing methods. They can say whatever they want, but my productivity has been severly limited with 2007 and yet I have used excel for years and years doing many interesting and useful things with all kinds of data – from research to business.
Ravi says
I have trying to put label for depth series (series names) as C1, C2, C3, …, C10 for plotting ten series 3D bar chart. I am not able to do so in the custom I put C#. It shows as in a box C1234. But then it does nothing in the chart.
I think this much should have been easier to handle. Anyways till now I am not able to do it.
It seems peltier tech can do the job, I don’t know how.
Jon Peltier says
Ravi –
Is there a question hiding somewhere in your comment?
Also, why are you using a 3D bar chart?
Ravi says
Dear Jon
Actually I want to plot ten series each series having 17 values. This may be case of multiseries. I just wanted that axis label for the series be renamed as c1, c2, c3,… instead of series1, series2, series3 etc. It would have been better if I could put it as battery, sparkplug, … etc.
Jon Peltier says
Ravi –
The easiest thing to do is set up the data (I tell people that spending five minutes with the data will save 5 hours of frustration).
Put the X data in column A, cells A2:A18. Put the Y values for your ten series in columns B:K, cells B2:K18. Keep cell A1 blank, put the series names in row 1, cells B1:K1. Now create your chart. The blank cell in A1 will tell Excel, categories (X values) in column A, series names in row 1.
Ravi says
Dear Jon,
Thank you very much for the help. It works really well. I am new to excel. I got the key word of “setting up the data”.
I almost broke my head working with menu for two hours.
Thank you very much.
ckz says
Unrelated to the above topic – I do have a question about automating the format of a given range.
I have a workbook that based on a condition will automatically add a border to a specified range or remove the border. In cases where the range already had a pre-existing border, then the condition is accepted and the code attempts to create the border, I get an error.
Is there a way to create an if/than or select case statement based on the format of a cell – whether it already has a border or not? Or for the code to be able to overlook the pre-existing format and “re” border the given range any way?
I appreciate any guidance.
Jon Peltier says
CKZ –
I don’t know why your macro won’t simply overwrite any existing border. Without seeing the code or the error message, it’s hard to make a guess.
ckz says
The error message is as follows: “Unable to set the LineStyle property of the Border Class.”
The code is as follows:
ActiveSheet.Range(“J9:J21”).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
In the above case – the borders currently exist in the worksheet, and now the code is to remove the existing borders.
Thank you.
Jon Peltier says
Presumably the first .Borders() line is highlighted in yellow. Nothing in the code indicates why it shouldn’t work.
Try something like:
With ActiveSheet.Range(“J9: J21”)
.Borders (xlDiagonalDown).LineStyle = xlNone
‘ etc.
End With
Marutan Ray says
i have long been an excel user. excel 2003 rocks (SP3). excel 2007 and 2010 sucks – no matter how much and how many times i try, i still am not comfortable.
i have developed almost application like spread sheets in 2003. but with 2007 / 2010 i cannot perform even the simplest tasks – e.g. copy and paste! if i have relative references i expect paste to have relative references, and similarly absolute to absolute. but excel 2007 / 2010 are creatures of hell, designed to create pure unhappiness. i get frustrated and give up.
the (probably?) good things about 2007 / 2010 is multi threading and millions or rows and cols. but even with multi threading the performance of 2007 and 2010 sucks in comparison to 2003 – i do not understand how microsoft managed to decrease performance by implementing multi threading. my spread sheets are many MB in size, loads of worksheets, with some serious VBA codes and solvers and splines (cubic, 3D) and charts – developed and maintained over many years, and yes they run faster on 2003.
i am writing this comment because people still seem to be appreciating excel in 2011 and i just dont understand their logic.
bottomline, microsoft has ruined a great product called excel 2003.
Calvin says
Agree totally with the preceding. I spent years learning and using 2003 with XP, including fairly advanced calcs, charts, stat functions, and formatting. I could do everything I wanted to in no time, and have GBs of spreadsheets archived. But then the need for a new machine meant Windows 7. Of course Excel ’03 is a problem with Win 7 and so on to Excel 2010. Now every operation is a research project, starting with figuring out the absurdly over-engineered Ribbon. Excel used to be a workhorse for me but at this point don’t even want to use it.
Doug says
This is way bigger than Excel. Microsoft SUCKS!! I just downgraded from 2003 to 2007. The web based help is almost completely useless. Will someone please put these idiots out of business! Great idea – let’s turn 30 second tasks into 30 minute tasks. Microsoft has reduced the productivity of the entire country (actually the entire world) by 10+%. Nice job Bill!
Anonymous says
Excel is designed to make a profit, not for its users needs or interest. It is greatly bloated. Useful features are crippled…deliberately. Hidden…deliberately. There is method to the madness. Its a dumbed-down system for people who are by their very nature of using spreadsheets in the first place power-type users. A terrible mismatch indeed.
Tim says
All M$ needed to do was increase the rows and columns and keep EVERYTHING ELSE the same. They would have made a killing of a profit. Overengineered nonsense.
Rick Gilbert says
Office 2007 irreparably damaged Excel charting (which wasn’t great to begin with), and buried lots of useful old commands and features in layers of ribbon-aise. I like what MS did to improve conditional formatting, and more columns and rows, but almost nothing else was an improvement over Excel 2003. Were it not for my customized QAT (Quick Access Toolbar), I’d pull my hair out daily clicking down to buried commands (Add-In manager, workbook properties, etc.)
Kingsley Dyson says
Tim said: “All M$ needed to do was increase the rows and columns and keep EVERYTHING ELSE the same. They would have made a killing of a profit. Overengineered nonsense.”……. Now THAT is the most accurate statement I have seen in ages!
Finally I just got off the phone from talking to M$ (have been trying to talk to a PERSON at M$ for ages). I have been battling the VB “Runtime Error 1004: Unable to set the LineStyleproperty of the Border class” EVER SINCE EXCEL 2007 WAS RELEASED…. Take a perfectly functioning Excel 2003 workbook (same one I have used EVERY day since I created it in 2004 – it has developed to thousands of lines of VB code and is the most valuable and useful knowledge base I have access to). Open it in Excel 2007 and “Runtime Error 1004: Unable to set the LineStyleproperty of the Border class” happens if I try to format a border on a cell when there is a shaded cell in the row above or below. Worst of all, if I save the workbook then open it in Excel 2003, the issue migrates to Excel 2003. Since 2007 I have opened my Excel 2003 workbooks FOR VIEWING ONLY (no saving allowed) in Excel 2007 and later versions. The same issue happens in Excel 2010 and the same issue STILL happens in Excel 2013. There are thousands of posts on the internet about the same issue. NONE of the proposed solutions have worked consistently. Solution offered by M$ on the phone today: “Run a Repair on Excel 2003”. NO M$!!!! The problem is: You Broke Excel In 2007, YOU MADE IT WORSE IN 2010, and I don’t see any improvement in Excel 2013 (beyond that there are more rows than in 2003)! There are MANY other issues, but I don’t want to write a book here.
Excellent site Jon!
Kingsley Dyson says
Why do I use Excel at all?…. I use CAD software that REQUIRES Excel in order to work. VB is VERY powerful. I invested thousands of hours developing the VB code in my Excel 2003 workbooks and don’t desire to re-write in some other programming language. Most of my customers have Excel, so good format for distribution.
Why am I still using Excel 2003 on 32 bit Win XP machine you ask?…. Outlook 2010 constantly freezes on my Win 7 x64 power machine. Excel 2003 on my 32 bit Win XP machine runs MANY times faster (seriously faster) than Excel 2010 on my Win 7 x64 power machine, as in have a quick coffee while Excel 2003 runs, go buy lunch and eat it while the same code runs in Excel 2010. I have no interest in wishy-washy colours and playing hide and seek on ribbons of constantly moving buttons.
My advice to Microsoft:…. Take heed of your customer’s voices. Take a virgin copy of Excel 2003… Add the functionality that your customers actually use from your later offerings… Keep the simple, functional and non-screen space hogging UI of Excel 2003 instead adding of rivers (oh I meant ribbons) of frustration… Take advantage of faster hardware with more RAM and make Excel RUN FASTER!
Kingsley Dyson says
Does Excel Suck? That is a Really Good Question!
I have figured out how to use Excel for just about everything else, but I haven’t figured out how to use it to clean my carpets, so I can’t really say how well it sucks!
I do know a bit about spreadsheets….
I started with SuperCalc back in 1987, running under DOS on a Compaq Luggable with a tilt-up neon screen and a dock-able keyboard. It had a 20MB HDD (yes, 20MB, not 20GB!!!). It also used the 5-1/4″ HIGH capacity 1.2MB floppy disks, rather than the 5-1/4″ standard capacity 360kB floppy disks used by most other computers of the day. I was up and running with the macro language in SuperCalc in no time and drinking coffee while the computer did my work for me.
I LOVE the internet… I just searched for SuperCalc and found this… http://www.pcreview.co.uk/forums/supercalc-5-00-a-t3982283.html So I am not the only lover of old things! But I have moved on. (I am not surprised that they can’t print from SuperCalc under Win 7 x64!!!)
Later I moved to Lotus 123 (still DOS) as it gave me more rows and columns to work with. Macros had to be written in the (one and only) worksheet, so I reserved rows 1 – 99 and columns A – Z for macros and hid them, so my worksheets appeared to only have rows 100 – 256 and columns AA – ZZ. I couldn’t fit my macros into 99 rows, so developed subroutines that were stored on disk and read into the appropriate cells for running as required. The power level of Lotus 123 just went UP!!!
Next I moved to Lotus 123 Windows, cos Win 3.1 was the WAY of the Future!
Ultimately I moved to Excel cos it had a better charting engine AND a SEPARATE SHEET for storing macros. BIG advancement!!!
I found the progression from Excel 4 macro language to VB rather challenging, until I developed a library of useful code snippets, that I still add to and refer to (though less frequently) to this day.
Believe It Or Not (nothing to do with Rippley!)… I STILL run Excel 4 macros for my frequently used, simple operations that I have keyboard shortcuts for, that improve my productivity in Excel immensely. WHY would I do that, you ask?… When VB arrived, I started re-writing my Excel 4 macros in VB. I soon discovered that VB kills the Undo stack. Do ANYTHING in VB and Undo becomes greyed out. Run whatever you like in Excel 4 macros and if you made a mistake, Undo still works!!!
So, I say to M$:… If it works… DON’T BREAK IT!!!!!!!!!!!!!!!!!!!!!!
I hope someone else has some nostalgic tales to add!!
Cheers Jon!
Kingsley Dyson says
Well blow me down…
Perhaps the M$ phone techie might have been on the right track after all, though he told me to go to Control Panel > Add/Remove Programs, then click on Excel and click Repair. I hadn’t done this yet.
Thanks Jon… A link on one of your pages took me to http://www.jkp-ads.com. When I went to the Name Manager page, there was a help item about possible error on installation and a method for fixing this: “Excel 2003: open Excel and select Help, detect and repair from the menu.” I immediately wondered if this might resolve the “Runtime Error 1004: Unable to set the LineStyleproperty of the Border class” issue that migrated from Excel 2010 on my x64 machine to Excel 2003 on my x86 machine.
I followed this instruction, then ran the VB code that sets and clears shaded flag cells with borders, within the portion of the worksheet that was edited and corrupted in Excel 2010. Voila! No error!!!
Thank You! Thank You! Thank You Jon!!!
Well, I better thank Jan Karel Pieterse also. So Thank You Jan Karel Pieterse!!!