Chart Feature: Dynamic Legends
Instead of fixed legends, please allow the user to create a dynamic legend, so that if there are no values for a particular category, the legend does not show up for that category.
Quote Scott - "If you had the ability to graph and equation; e.g. x^2 + y^2 = 1 would that address your needs?"
I was thinking of posting something along those lines on this site so now I have:
Brett Ables commented
I certainly agree that many of the creative uses of Excel are to fill or work around gaps in Excel's capabilities!
Regarding plotting geometric figures... I think plotting of equations is a fantastic idea that would save time and "worksheet scratch space" for examples like the one I showed. In my experience as an engineer at NASA I constantly see folks implementing geometries of physical parts in Excel for a variety of reasons. Sometimes these geometries are circles, arcs, ellipses, or other shapes that can be represented by equations, but often they are custom curves pulled from a drawing or CAD file. I think parametric equation plotting, or iso-curve plotting (think contour lines from an elevation map or isobars from a weather radar map), would be valuable time savers that enable visualizing a relationship without first generating "dummy data" from the relationship.
However, this "Chart Feature: Dynamic Legends" feature request is addressing the specific limitation of Excel Charts' legends. At the end of the day, the relationship between the legend seen on the chart and the series that are plotted is very static. If I create a legend, it defaults to including all series in the legend. If I have lines and dots and they are color coordinated, I may only want legend entries for the lines to avoid clutter. To do this I have to then delete the legend entries for the dots, 1 by 1, by hand. If I then have a dynamic setting that groups the dots into dots, triangles and squares based on type, but maintains the color association with the lines, I now have more legend entries to delete. And if for some reason I want some of those legend entries back or I delete the wrong one, I believe I'd have to delete the entire legend, and start over. Excel encourages and enables the creation of dynamic plots, but is hampered by the static nature of the legends. Honestly the static nature of the axes bounds is even more annoying in this regard, but this feature is addressing the legends specifically.
Thanks for the great example. As you have illustrated, series can be used for a wide variety of very creative uses. In some cases they are used for filling a functional gap in the product. If you had the ability to graph and equation; e.g. x^2 + y^2 = 1 would that address your needs? Essentially, the equation graph would be supported as a real feature.
Brett Ables commented
I often create interactive sheets with options that may result in adding or removing curves from a plot. I can add or remove the curves from the plot easily with an if() statement and setting the series data to na() so that the series isn't plotted. However, the legend will still show these "invisible" series.
Here's an example: https://www.dropbox.com/s/1icwvpucsu3qxph/LegendExample.xlsx?dl=0
In the example I'm plotting pill-shaped figure (two semi-circles with a rectangular space between them), allowing the user to select the radius and middle length. If the middle length is 0, the figure simply becomes a circle and the extension is "deactivated" with formulas which set it's plotting data to na(). However, the legend still shows the entry for the series. This is a very simple example, where much more complex examples exaggerate this effect.
I've made data plotting sheets allowing users to select up to 10 parameters to plot. Once plotted, the names of the parameters are shown in the legend. However, if the user has only selected 2 parameters, without macros I have no choice but to have 8 empty legend entries.
There should be an option to hide legend entries for series that aren't visible because they are empty or na(), or perhaps an option to hide legends for series when nothing is visible in the current view. Example: A plot showing latitude and longitude locations of restaurants by chain could then automatically reduce the legend dynamically to only the 3 chains represented in the town being focused on in the plot.
Another tactic would be to allow the legend to be dynamically removed if the series name is set to na(). This would at least allow programmatic control for dynamic legend display.