The Future of Excel Charting

With the recent Excel beta, which now has become a released product, I’ve been thinking about how Microsoft can enhance the charting experience. In Excel 2007, there were precious few features added to charts. The new chart formatting is certainly nice, but the new user interface is awkward. The only real enhancement to charts in Excel 2007 is that the minimum and maximum of a logarithmic axis scale are no longer tied to powers of ten. However, we’ve given up a few things, some perhaps just to bugs which crept in during the complete restructuring of the charting module. The Size with Window setting for chart sheets was deprecated purposefully, and I’m hoping they bring it back. In Excel 2007, among the things I consider bugs is the inability of an XY series to be plotted on a line chart’s category axis, which means you need to use a secondary axis for the XY series, and that means the secondary axis is no longer available for another series that provides an additional effect.

Part of my thinking has been about new chart types that should be built into Excel. I’ve decided that Microsoft should not spend much effort to recreate the chart types that clever people can build already with combinations of existing Excel chart types. One type of chart that Excel does not do is a 3D XYZ type of chart. To fake this requires some real heroics, and I think Microsoft should develop a 3D XYZ chart type, with points only and also with a surface. To fake a histogram, waterfall chart, box and whisker chart, and other similar charts, on the other hand, requires only careful assembly of individual series, and I think Microsoft’s efforts can be more wisely spent in other ways, rather than trying to make everyone happy with the difficult design decisions. Add examples, templates, and tutorials to the MS Knowledge Base, and include links to pages on the web sites of charting heroes.

My feeling is that Microsoft should add features to charts that enable more of the fancy combinations we can come up with. I have a fuzzy list of items in my mind, which change in importance depending on what I’m doing this week. I’ll describe some of them, and ask for additional suggestions.

1. Give us real text boxes for chart and axis titles and data labels, which can be resized and which have VBA properties like height and width.

2. Enhance a series, so it has more associated ranges. Allow us to more directly interact in VBA with the ranges describing the X and Y values of a series, without painful and error-prone parsing of addresses. Formalize the association of a series to its custom error bar values, so we can deal with error bar ranges in VBA. Add a range to a series that contains data labels for the series.

3. Allow more chart types to be combined. I’d like to be able to combine area charts with bubble charts, so the bubbles have a nice patterned background to lie on. Let me add an XY series to a contour chart. Don’t ask me why I want this, just enable it and a hundred users will have a hundred whys.

4. Combine Line and XY series into a single type. If the X values are labels, then treat the series as today’s line series. If the Y values are categories, then treat the series as a dot plot (a rotated line chart). If X and Y are both labels, well, we’ve heard people asking how to plot, say, classroom along X and letter grades along Y.

5. Construct an Axis object model, so we can apply any arbitrary funtion (or even range) to describe the axis and define its scale and ticks. Then such things as probability axes, reciprocal axes, even broken axes will be much easier to construct.

6. Let any numerical and text aspects of a chart be linked to a worksheet cell or range. For text, we can do this with titles and data labels, but not with axis tick labels. We should be able to link axis scales to the worksheet and use our favorite algorithms to provide autmatic scaling that suits our needs.

I could go on and on, but I’ve said enough to prime the pump.

I know that this is pretty much out of the box thinking, but if features like these are added, they provide flexibility that users like you and I can use to make a much wider array of great charts than a handful of added chart types could provide. What kind of features would you like to see? What would allow you to make better charts? What would make Excel 2010 a kick-ass charting product?

Posted in Uncategorized

38 thoughts on “The Future of Excel Charting

  1. In future versions, I’d like to see a binomial tree/lattice chart that can plot stock and option prices. I don’t expect MS to include this as there is not widespread demand for it, but it would be extremely useful for those who have a need for it. I built a ‘fake’ chart of this type in XL which was not a trivial task and consumed far too much of my free time. I’m in the process of making it user friendly and hope to post it to cyberspace one of these days.

  2. I’d like to see a second or third chart title field. Often, especially when you’re dynamically creating pivot charts, you have to concatenate too many items to make a chart’s title readable. A second chart title field would allow more options this way, or allow for things like a label for data source or whatever.

  3. I would like to see the current “3d” chart removed. And if MS could manage it replaced with some propper ones.

    I don’t now that the 2007 chart rendering is like, but upuntill 2003 it was shocking so that needs to be improved. – I think they have improved a bit in this area to be fair.

    Spike line type functionality out of the box?

  4. It’d be nice if Microsoft added a #BLANK value that functioned like the value of blank cells.

    It’d also be nice if chart “ranges” could optionally be arrays instead (using the number formatting for the Normal style by default).

    As for true 3D scatter charts, there’d need to be some way to rotate the chart axes.

    Anyway, there are very good chart engines built into most statistics software. Consider S-Plus or even R with GnuPlot. And then there are the true math packages like Mathematica, Maple and MatLab in which you can choose light source direction and intensity and choose color gradients to represent more than 3D. In comparison, Excel provides baby charts for business. I’d guess Microsoft correctly sees minimal ROI from wholesale expansion of Excel’s charting features.

  5. There are some things that I would like to change about Excel’s charting capabilities. More importantly, however, is that we as users (myself included) should better understand how to convey information more effectively.

    Perhaps the greatest achievement is any art form (graphical or otherwise) is the ability to present information with minimal clutter. In other words, “less is more”. One of the best resources for learning this technique is found in books by Edward Tufte. His writings offer excellent examples of how to create graphs and spreadsheets that achieve this objective. His web site, https://www.edwardtufte.com, also addresses similar issues when using PowerPoint.

  6. fzz –

    You can use arrays, but you are limited by how long an array you can squeeze in. After trying for years to get around this, I’ve decided it’s easier to just dump data into a sheet and plot the range.

    The existing bad 3D charts have built-in rotation control, so if they built a good 3D chart, I would think that it would have these controls.

    “I’d guess Microsoft correctly sees minimal ROI from wholesale expansion of Excel’s charting features.”

    It’s more complicated that that, but obviously folks like me are grossly outnumbered by marketing types who like column charts with 3D effects and seventeen ways to shade the bars. I do things in work for clients that I wouldn’t do in my own programs; I recommend against things but do them anyway. Microsoft’s bigger clients are the big corporate users with 10,000+ seats who need quick and dirty, not a handful of engineering or science types who can figure out how to get what they want despite it not being built-in.

    Ross –

    If by “rendering” you mean default formatting, yes, pre-2007 is pretty bad. However, I don’t really think 2007 is capable of better charts than 2003, if the 2003 user has half an eye for effective data presentation.

    I’d scrap the existing 3D charts and 2D charts with 3D effects, but I’d keep the contour and surface types. Despite their shortcomings, they can be used effectively with a little effort.

    Graham –

    I just end up using textboxes when I need more titles, especially if I’m automating the chart anyway.

    Joe –

    Sounds interesting, could you send me a copy? jonxlmvp at peltiertech dot com.

  7. Completely agree Jon. Adding more customization would be much better than a new chart style or 2.

    Here are a few things,

    The ability to flood fill enclosed xy scatter series.

    Being able to plot a formula without having to compute the points in cells.

    Better control over the plotting order across series, chart types and primary/secondary axis. Something more akin to Z-order.

  8. I use charts to make large amounts of data more consumable. How about a few easy to use dashboard style charts. Speedometers, thermometers and traffic lights spring to mind with sliders to move through the time dimension with some form of goals applied. With some effort you can get close but a dedicated charting type would be nice. Just my 2 cents…

  9. I am not a charting hero, and when I see what a charting hero can do I am truly amazed.

    But I think we need to encourage MSoft to do both things: add some new chart types to make things easier for us charting idiots/get good publicity and at the same time add more API/object model infrastructure to make it easier for BOTH Msoft and the heroes to do more gobsmacking stuff.

    seems to me thats the way to maximise the ROI …

  10. Being in the business of dealing with lots of X-Y charts, statistics, etc., I would love to see the following items:

    1- X-Y-Z plots – not graphically three dimensional, but having a the X_Y points discriminated by data residing in another series where the point color changes to reflect the data range bins in the Z series.

    2- X-Y plot point identifier and manipulator. I’ve harped on this for awhile, but it would be incredibly useful to click on outliers, and move them to another series and change the point type & color to reflect which points were removed. Have the regression statistics and lines change to reflect the removal of the point. I’ve harped on this before, but it would be a terrific addition.

    3- The ability to shade Scatter Plot X-Y points when a line type is specified between points and the ability to shade between series. I can fake it to a degree with error bars, but I’d love to specify pattern types.

    4- Real THICK lines to join points beyond what’s offered in Excel.

  11. Hi Jon,

    By rendering I ment the quality of say lines in xy charts, antialiasing really i guess, I have always felt that Excel (if fact most of office) is a bit carppy in this area.

  12. – The ability to put formulae (including VBA and XLL calls) directly in the source range / series function, and everywhere that currently only accepts a link to a cell or a hard-coded value. Use X, Y and Z as built-in keywords, so we can easily create lines/planes as functions of the other axes values. So I could say that the source range for a line is “Y=4? or “X=2*Y+3? or “Z=2*X+3*y+5?
    – Spacing out overlapping data labels, so instead of all having to be exactly next to their point, they can be displayed so they don’t overlap, with a ‘callout line’ linking the label to the point.
    – Add anchor points to all the individual chart objects, so we can anchor other drawing objects to points/boxes/lines.
    – Be able to lock multiple charts across multiple sheets such that they all have exactly the same interior plot area size.
    – Reinstate the ability to be able to drag a point around the chart and have it update the underlying sheet – with an event model, so we can control which cell(s) get updated if the chart is the result of a formula.

  13. Andy –

    You know about the tricks to fill within and between XY series. Workarounds, sure, but at least possible.

    Stephen demonstrated how to plot a formula without cells about a decade ago. Possible, but beyond the reach of most users. Tushar has improved upon the technique with an add-in.

    You’ve added a good one: Control over plotting order, as if it’s Z-order of the series. Add to it improved control over legend entry order. I’d give the series a few properties: .LegendEntryOrder (which may differ from .PlotOrder) and .LegendEntryVisible.

    Dave –

    1 – This can be achieved through my conditional charting techniques (http://peltiertech.com/Excel/Charts/ConditionalChart1.html), which are not as much work as some of the other workarounds.

    2 – I have something like this half done on my hard drive. Maybe more than half, it just needs a little attention, but I have too much project work.

    3 – Andy also mentioned this, and there are ways to handle it, kludgy workarounds (http://peltiertech.com/Excel/Charts/BackgroundFill.html).

    4 – Perhaps 2007 offers this, and so does http://peltiertech.com/Excel/Charts/VBAdraw.html

    Ross –

    In this regard, I feel that 2007 is not any better than 2003, and worse if you want to use a metafile picture of the chart.

    Stephen –

    Good solid points from someone who obviously uses many charts in daily combat.

  14. Ross –

    “Spike line type functionality out of the box?”

    I’ve heard that Bissantz has been developing an updated SparkMaker which has eliminated the problems which led me to uninstall their first commercial version. When I test it, I’ll give a report.

  15. Jon:

    This is a sample of my wish list:

    (…)
    – Putting the maps back would be nice;
    – A decent box-and-whisker;
    – Treemaps;
    – sparkelines;
    – More visual interaction (brushing, drill down/up…);
    – More control over pivot charts;
    – Less need for add-ins/VBA for basic formating (I work for a large company and installing a new add-in is always a problem);
    – Automatic small multiples;
    – Finer control over typography;
    – VBA-free event setup (mouse hover, mouse click…);
    (…)

  16. On the topic of chart types, the Excel charting team frequently hears the need for true 3D charts; e.g. charts that have 3 value axes (XYZ). I’d like to better understand the scenarios where this chart type is used, why it’s useful, and the must have features it should support. If you could share your thoughts on this, it would be really helpful.

  17. Apologies if this has been posted before or if i am wrong. I constantly have to work with pivot tables and charts. One of my major complaints has been tht pivot table generated charts tend to loose their data labels / values whenever its refreshed. Has this been addressed in Excel 2007?( i am talking non-vba here)

  18. Jon,

    The flood fill tricks only works if the x values increment in the same direction. Whilst the xy series can handle a backwards point the area chart is happy to sort the value into the correct date order.

    I see Stephen was better able to put into words my feeble attempt at explaining non cell computed formula ;)

    Another feature is the ability to have multiple or compound axes. This would make construction of panel and matrix charts much easier.

  19. Owner drawn charts whose regeneration code can optionally be stored and transported with the xls file.

    Optionally, the above but with the ability to extend built in charts.

    I dont mind which language. even if this is via .NET languages, it would enable richer charting and graphics reporting.

    Rob

  20. Jon,
    Hi, i got a copy of BonVist spikelines from the chap that writes the code. I though the results where very good and the UI was quite good. However, i think there is some .Net code and the addin is not shim(ed), so when i was doing some .Net stuff it crashed out – i have not got round to fixing it up yet.
    The other thing, and i have not tested this, but are these spikelines portable? I think they use fonts, so if i send the wks will the charts appare on a PC without the fonts installed, my feeling is they will not. Of course i could find this all out, only i currently dont really have the intrest.

    Cheers
    Ross

  21. Scott, Most of the times I’ve seen the need for XYZ charts is a sort of “Poor man’s CAD”, where there’s a need to generate and display true 3D coordinate diagrams, but insufficient to warrant the expense and complexity of a true CAD application. I’ve also seen situations that are more like a 3D representation of a 2D bubble chart – but where the Z value is the height of a column rather than the size of a bubble. Typically, this is where the X and Y values relate to a physical coordinate system (e.g. a map) and the Z value is a measurement taken at that value. Then, of course, there’s just the simple act of making the existing countour charts usable, with numeric X and Y values and using a set of X, Y and Z columns, rather than a matrix of X and Y categories and Z values.

    Required features would be the ability to specify columns of X, Y and Z for each point, then:
    – Joined up in the order provided, or
    – Overlaid with a ‘smart’ triangular/square grid mesh (optionally straight-connections between points or a smoothed surface displaying and XY mesh grid)
    – Ability to rotate the view and change the viewing angle (as per prior versions)
    – Optionally to display as columns from the X, Y and/or Z axis plane, with a user-specific column width and either square or circular columns.

  22. I don’t understand why somebody would want to use Excel’s built-in charts when there is a ton of OLE-based chart controls out there.

    Sure, deploying an Excel file and an OLE control is not the always easy, and shame to Microsoft for missing the opportunity to make that scenario possible with the new packaging technique (namely, the package could have document-specific install actions whose binaries would have been embedded in the package).

    From a pure developer standpoint, the Excel chart state of the art sucks, and what Excel has to do is provide an object model for others to build their own charting mechanism. What does it mean? the plumbing is one which allows generic data binding and a presentation layer. Then on top of that, we can specialize it : for instance, what an axis means, what a series means, …

    I am baffled that Excel 2007 does not use the new WPF layer, although it is shipping the same day than Vista. It would have been a great incentive, and a great demo.

  23. Frustration with Excel 2007
    I’m having trouble doing some basic things I used to do in Excel in the new Excel 2007. For example, how do you make a bar graph that has a pattern fill (such as the checkerboard or diagonal lines) that you used to be able to do by going to format data series…patterns tab….fill effects…pattern tab…and clicking the one you want. If I can’t do this..printing in black and white is going to be a pain!

  24. Jennifer, pattern fills is one of the deprecated features. See:

    http://blogs.msdn.com/excel/archive/2006/08/28/724641.aspx

    Pattern fills for shape objects has been deprecated in favor of Picture and Texture fills. Existing files will appear the same when loaded. The ability to create new shapes with the previous pattern fills has been deprecated as part of the new drawing capabilities. We are hoping to provide pictures of the patterns that we had previously such that you can get similar results using texture fills.

    As far as I can tell, they haven’t provided pictures of those patterns.

  25. John –

    “By the way, VBA macros still work.”

    Nice find. It’s only kinda sorta deprecated.

    I’m with Jennifer, still having trouble doing basic things as easily as in Excel 97-2003. And I’ve only been using the Ribbon interface for almost 18 months. And I keep finding little things that work differently or not at all. The latest: you cannot enter a date or a time as an axis scale parameter for a value axis (a Y axis in any chart, or an X axis in an XY chart). So now you have to calculate what date or time you need (i.e., today = 39142, now = 0.88025).

  26. Yeah, that date thing is a problem. Dates are confusing enough in Excel without requiring users to enter a serial number.

    Regarding the patterns… At least I have something new to add to my Chart Tools add-in.

  27. The other date-related issue I have with 2007, which will prevent me from formally upgrading (except to support the unfortunate or misinformed early-adopters) is this:

    Suppose you make a line chart with a date scale axis (say, a stock chart). The line chart’s date scale axis provides nice formatting options. In 2003 and earlier, you can then add an XY series to the chart (say, a stock index, a moving average, what have you), using dates, and the XY series’ dates line up nicely on the line chart’s date axis.

    In 2007, the XY series is completely ignorant of the line chart’s date axis, even if you assign it to the primary axis. When you change the XY series to primary, the Y axis lines up as expected, but the X values remain as they were on the secondary X axis.

    Oh sure, you could use the secondary X axis to align the XY series, but now you have to align the primary and secondary X axes, because they are not going to do it themselves. This also means you lose the flexibility to add more series that need the secondary axes.

  28. This is probably a simple one, but… Is there any way to make Excel set the autoscaling on an axis based on only the visible data? When I manually restrict the x-axis, for example, I want it to automatically rescale the y-axis based on just those data points relevant to the restricted x-axis range.

  29. Kevin, I can’t reproduce the problem. More likely, I don’t understand the question.

    I created an XY chart, then hid some rows. The chart scaling adjusted to the visible data only.

  30. John –

    Kevin isn’t hiding rows, he’s narrowing the range of an axis, so points with values outside that range are not visible. But they’re still part of the series, so they affect the auto scale of the other axis.

    Kevin –

    One trick is to hide rows containing X values outside of your range, which is how John approached your issue. Try an autofilter.

    Another approach would be programmatic. Your code would determine which points fell within the X range, find the minimum and maximum Y values for those points, then calculate and apply appropriate Y axis scale parameters.

  31. For XY charts, I would like to be able to make the x scale and the y scale identical (or a fixed ratio). This enables simple 2D plots to have the correct proportions.

  32. I use excel charts to create a lot of plots relating a variety of material properties (soil, actually – the sand and clay sort) to depth below ground level. These always require x-y plots.

    Most of the workaround methods that various clever people have devised use things like line series, date-lines, and categories to do things (like shading between lines for instance). These don’t work for the y-axis. I’d like to see the same functionality for y-axes as for x-axes.

    I’d also like to be able to be able to control things like axis length and position so that when you create a series of plots with different scales (as would appear in an engineering report on different material property profiles), the plots don’t move around the page as you flick through the printed report. Presently if you change from an axis of scale say 0-10 to one of scale 0-1000 the whole axis moves in order to accomodate the longer label.

  33. I would like X,Y and X,Y,Z plots with the labels coming from another column. E.g. you have company names (labels) col 1, revenues col 2, income col 3 and return on investment col 4. I’d like to plot in 3D and then label with the company names.

  34. Hi.
    How do you flood fill a cell based on the result of a formula. Example if negative, cell flood fills red, else green or no fill.

  35. Renita,

    I think you are looking for conditional formatting. Format -> Conditional Formatting… You can assign many aspects of a cell’s format based on another cell’s value or how a formula evaluates (to TRUE or FALSE, not a value)


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.