Hui, one of our in-house excel ninjas at Chandoo.org Forums, has asked an interesting question.
Microsoft is already planning the next version of Excel and is looking for our feedback into what extra chart types it should offer/include. [more]
It is an interesting question, so lets have a poll.
What new chart types you prefer to see next version of excel?
I would love to see box plots, bullet charts and dot plots.
What about you?
Use comments to share your views.
Btw, here are excel tutorials for creating bullet charts, box plots (box plot add-in) and dot plots (add-in).
PS: Only 2 more days before I close the net-book contest.
PPS: Only 3 more days before I close excel school registrations. Sign up today.
PPPS: Only 4 more hours before I shut down the laptop for weekend. Happy weekend 🙂
33 Responses to “What new chart types you want to see in Excel? [poll]”
I hope we get a lot of contributions and then Chandoo, as a MVP, can at least forward the list with some authority.
So I'll kick it off
Two Items for me.
1. Change in Focus
I would like the primary focus of any chart to be the Plot Area not the Chart Area as it is now.
By making this change it would allow people to easily align chart axis in either direction and if a user decides to drag the Chart Area in and hence obscure/cover the axis titles or labels that should be there choice.
ie: Line up a Y axis with a grid and then adjust the Chart Area to suit what is around it.
2. Cell References
Allow cell references to control X and Y Ranges as well as Grid Line Spacing
Bullet graphs and easy mashup with maps for geo analysis.
Thanks
Kiran
Gantt charts. I know you can create them, but a one click, built in option would be nice...
Box charts. I would also like to see more flexibility in being able to position data point labels.
Motion Charts, similar to gapminder.org.
freedom to properly edit the in chart legend components please
Chandoo,
Here are a few improvements to Excel’s charting engine that would be helpful, in no particular order:
.
Streamgraph Chart Type:
http://www.excelhero.com/blog/2010/04/excel-streamgraph.html
.
Geographic Location Mapping:
http://www.excelhero.com/blog/2010/04/excel-location-mapping.html
.
Dynamic Charting Transitions:
http://www.excelhero.com/blog/2010/05/animated-business-chart.html
.
Ability to graph filled polygons without using Radar charts AND without having to layer Area Chart series:
http://www.excelhero.com/blog/2010/03/excel-optical-illusions-6.html
Ability to rotate those graphed polygons, individually.
.
Chart broken lines on XY Scatter chart when the data is produced via formula. As it stands now, ridiculous number of series is required in some circumstances, pushing that max limit of 255:
http://www.excelhero.com/blog/2010/03/excel-optical-illusions-4.html
.
Sankey Diagrams:
http://www.excelhero.com/blog/2010/03/energy-flow-chart.html
Or at least line thickness proportionality, similar to the proportionality offered by bubble charts, but instead for lines. Allow the thickness of lines to be controlled by range references.
.
Conditional Formatting for chart elements!
.
The ability to have more that 255 characters in a text box (either inserted into a chart or on a worksheet) when the source of the textbox is a formula.
.
Marker Labels controlled by a range references!
.
Chart a mathematical formula typed into a cell.
http://www.excelhero.com/blog/2010/03/excel-lissajous-explorer.html
.
Ability to use different size images for custom data point markers within one series. In Excel 2007, the first marker determines the size of all subsequent markers within that same series. The only workaround is to make a series of one data point for each different sized marker. Rotating an image changes its size. Thus the following needed 30 data series, when it should have only needed one:
http://www.excelhero.com/blog/2010/06/excel-optical-illusions-16-1.html
.
Ability to rotate custom data markers, so there is no need to do this:
http://www.excelhero.com/blog/2010/04/excel-optical-illusions-10.html
.
Ability to address all chart properties that can be addressed from the format dialog box from VBA, and vice versa:
http://www.excelhero.com/blog/2010/05/interactive-advanced-excel-chart.html
.
Ability to rotate Radar chart series in the same fashion as a Pie Chart series.
.
Ability to rotate an entire chart, exactly the same way that other shapes can be rotated on a worksheet.
.
Ability to precisely and easily track mouse movements over a chart (through VBA) so that a developer can control zooming by selecting a region with the mouse or double-clicking a data point and changing the axes ranges. Overlay shading of such area when while selection occurring with mouse.
.
Chart events should be enabled for charts embedded in a worksheet without having to resort to a custom event class AND having the chart selected before the events fire:
http://www.excelhero.com/blog/2010/03/eurovision-2009.html
.
Fix the Excel 2007 Scatter chart bug where a data point produced by a NA() function gets plotted as a ZERO value instead of being omitted as correctly happens in previous versions. My current work around is to name a blank cell “null” and reference it in the formula producing the data for the series instead of the traditional NA() function. A demonstration can be seen here:
http://www.excelhero.com/blog/2010/03/excel-a-presentation-platform.html
.
Orbitally Oriented Marker Labels:
http://www.flickr.com/photos/36343059@N08/4587514074/sizes/o/
.
Ability to use the Format Painter between chart series.
.
Ability to combine Bubble charts with other types of charts, i.e. XY Scatter.
.
Ability to control the Z-Order of chart elements, including labels and Trend Lines.
.
True 3D charting.
.
Ability to control the Z-Axis in the faux 3d charts:
http://www.excelhero.com/blog/2010/02/excel-optical-illusions-2.html
.
Option to have Smooth Lines on Area charts, the same as XY Scatter charts.
.
Orbital Gradients on fill formatting.
.
Add Bezier Curves to XY Scatter charts with one or more ranges of control points:
http://www.excelhero.com/blog/2010/03/bezier-curve-chart.html
.
Raise the limit on the number of value constants that can be programmatically added to a chart when defining a series via VBA to at least the same limit applied to data defined by a range.
.
Ability to fade charts in and out via VBA:
http://www.excelhero.com/blog/2010/02/excel-2007-nfl-drive-chart.html
Hover over the golden key to see what I mean.
.
Hierarchical Charts, vertical, horizontal, and circular:
http://queue.acm.org/detail.cfm?id=1805128
Figures 4A and 4B
.
Proper Chloropleths:
http://queue.acm.org/detail.cfm?id=1805128
Figure 3B
.
Scatter Plot Matrix:
http://queue.acm.org/detail.cfm?id=1805128
Figure 2C
.
Other Panel Charts...
.
Parallel Coordinates
http://queue.acm.org/detail.cfm?id=1805128
Figure 2D
.
Heat Map Charts
.
Regards,
Daniel Ferry
Excelhero.com
real 3D plots: lines, surfaces, etc.
I would like to see bullet graphs in the next release.
Vertical Line Chart:
On a recent dashboard I was working on I needed a vertical line chart with categorical data down the Y axis and values across the X axis. As a work-around I used an "XY Scatter with data points connected" chart. I plotted the values on the X axis and for the Y axis used place-holder sequential values, e.g. 1 thru 8 (because I had eight categories of data). Once I created the chart, I hid the Y axis and then created a "fake" Y axis using a text box to display the categorical data labels.
Box whisker plots.
This suggestion is less about new, sexy ways of displaying data. It simply is what we very frequently use in the field of medical science or any scientific field dealing with population comparisons.
And one more: I don't know how it is termed, with bar charts, when you want to show a maximum on the y-axis related to one or more bars who's value is so much higher than the other bars, but you want to keep the scale of the y axis such that you focus on the lower range of the values. Usually this is done by displaying a broken bar and broken y-axis to show the reader that the high value would be off scale.
I would want to upgrade scatter plots so that I can use the mouse to click on a point and move it into or out of a series. This would help me deal with invalid data, outliers, etc that would not be of value for regression purposes.
1 - Ability to specify and retain color wedges for Pivot Table Pie Charts. When the number of wedges displayed changes, the colors also change. Even with a macro to specify the color for each label, you are limited to the Excel basic color Pallette. The result is one of the ugliest set of charts I have ever made.
2 - Gantt Chart that makes it easier to plot Start Time and Duration in Hours. While it is possible with h AM/PM Custom Format and Conditional Format, it's still not the desired outcome. Much needed for Schedulers and Peak Demand Convergence.
3 - Yes! Conditional Formatting for Chart Elements.
Map plotting resurrected would be nice!
Along with Waterfall charts, Box plots and Pie-Chart - Sparklines!
I would like tight integration with PDA's for developing plots of my choosing (buildings, campuses, whatever the need) and then define custom choropleths for occupancies, traffic patterns or whatever else interests me in whatever geospace I choose (I don't want to be confied to counties, cities, states, countries)
This should be integrated within Excel and not a separate expensive custom mapping software such as Map Point
"Choropleth" or "Chloropeth"? Google seems to prefer "Choropleth"
Kind regards,
Winston
1. Multiple Plots from one chart: When a chart comes with multiple categories (like East, west, south & north zone) there'd be an option to break in to seperate, multiple charts to show one chart for one zone. This kind of chart will enable easy comparison between groups. This fantastic option is already available in MS-Access 2003. If so, why this feature can't be extended to Excel too?
2. Uniform Axis for multiple charts: For multiple plots there'd be an option to switch between unitform axis to non-uniform axis. Consider we have seperated the chart in to 4 zonal charts. Based on the values of the graphs the y-axis min-max values will be adjusted. Eg. East zone chart will have y-axis max as 20; South may have 30 as max at y-axis. Obviously it's hard to compare each other. If we've a option to automatically make all the zonal charts to have uniform axis (i.e., all charts y-max is 40), comparison'd be realistic.
Again, this (fantastic?) option is available in Access-2003.
@ Chandoo
If you feel that I've explain it in better way, I can send you some sample chart excerpts from MS-Access. Cheers!
@ Chandoo
If you feel that I’ve to explain it in better way, I can send you some sample chart excerpts from MS-Access. Cheers!
I'd like to see tag clouds and some other visualization techniques like that.
One-dimensional scatter charts, with category on the other axis. William Cleveland calls these "point charts," and they can be made up in Excel, but only by cheating: assigning a number in another column to each category, creating a scatter chart, and creating a category axis by hand.
Daniel -
"Fix the Excel 2007 Scatter chart bug where a data point produced by a NA() function gets plotted as a ZERO value"
Under what conditions does this bug appear?
What I want most of all is getting back chart functionality that was taken away in 2007. Dragging points to change the underlying values. Fitting a chart sheet to its enclosing window. The ability to use F4 to repeat a whole sequence of steps from a visit to the formatting dialog. The clean merging of different chart types on particular axes, eg, XY series on a line chart's date axis. Putting formatting commands (data labels, error bars, and more) back on the right click menu and format dialog instead of hiding them on the ribbon, or at least re-enable customization of chart element context menus.
Jon,
It is not just the NA() function, but rather any representation of the #N/A error value in a named formula or a named array of constants used as the data source for an Excel 2007 chart. For example:
.
={23,32,#N/A,14}
.
whether entered as a defined array constant in the name manager, or as the result of a defined formula. In previous versions of Excel, if the above example was the source for a chart value axis, it would be charted as 23, 32, , 14. In other words the error is skipped and not charted.
.
If that same chart is then opened in Excel 2007, the value axis will be charted as 23, 32, 0, 14. In other words the error is charted as a zero value data point, when it should be skipped.
.
Interestingly, if the source of the chart is a worksheet range, or even a named range (as opposed to a named formula or named constant array), the bug does not manifest, and the chart is plotted correctly.
.
Regards,
Daniel Ferry
excelhero.com
Daniel -
Hmm, that's buried deep. I've never come across that one.
This just shows that the original builders of Excel's charting capability were extraordinarily detailed with their creation, clever with their implementation, and designed a great deal of flexibility into it. Of course, it was built over several generations of the product.
The rebuilders of the Excel 2007 charting module could not hope to have captured every feature of the original. So many of the features were lightly documented, if at all, and unfortunately in the clash between form and functionality (and tight resources), well, many features were lost in the glare.
Daniel -
I entered this as the series formula:
=SERIES(,{1,2,#N/A,4,5,6,7},{1,2,3,4,#N/A,6,7},1)
And #N/A worked as intended. It did not when I defined names with Refers To as ={1,2,3,4,#N/A,6,7}
When I copied the chart to edit the data further, the series formula would not display, and the data in the Select Data dialog - Edit subdialog looked like:
={1,2,-1.#QNAN,4,5,6,7}
Weird and wonderful.
bullet charts!!!
I could really use a Yamazumi chart and would like to see one added here! I don't know if you can make on in Excel, but it's an interesting chart to develop.
I would like to see XY charts that can be made to ignore null ("") or zero values if so desired.
I would like a box plot.
I would like a format called "Engieering notation" that constrains scientific number results to powers of 10 in multiples of 3 (10^-3, 10^-6, 10^-9, etc.)
@Greg
With regards "XY charts that can be made to ignore null" have you trued using =na() where you want a Null
With regards "Engieering notation" have you tried using Scientific Notation which is an existing number format within excel, which constrains numbers to x*10^0,3,6,9 etc
Greg -
The custom number format ###.00E+00 will constrain your notation to exponents divisible by 3.
Hui -
We've been asking Microsoft (for ages it seems) for a function like BLANK() or NULL() which would behave just like a blank cell. The XY chart avoids plotting a point where there is NA() in the data, but it's not like a true blank cell: If there are connecting lines between points, the line spans the gap across the NA().
@Jeff,
You can use a Stacked Column chart as a Yamazumi chart
@Jon,
Doesn't the Hidden and Empty Cells button on the Select Data Source dialogue allow you to do that (Excel 2007 & 2010 only).
Hui -
No, that only works for blank cells. A cell containing #N/A isn't blank, nor is a cell that contains a formula that returns "".
Categorical scatter plots, for the love of God.