Intelligent Chart Axis Numberformat Would Eat Decimal Points
Charts default the numberformat for the axis to the numberformat of the first series. It would be better design (easier for the reader to understand) if the default minimized the number of decimal places. For example, if the y-values are between 2 and 8, I might show 2 or 3 decimal places in the raw data--but I want the chart axis to show whole numbers. I think of this as the chart axis should eat the decimal places where possible. That would be how a professional designer would build the chart, and it should also be the way the Excel builds the chart.
I realize that the user can easily override the default numberformat in the chart axis task pane. The point is to minimize the need for the user to do that by having Excel get it right the first time. Delight the user rather than making her curse Microsoft after having to override the default chart numberformat for the umpteenth time that day.
One exception to eliminating the decimal places on the chart axis occurs when either the scale min/max value or scale major increment has a decimal value like 2.5. But even there, I would want Excel to minimize the number of decimal places displayed. For example, use a single decimal place even if the major scale increment were set at 2.500 by the user.
There may also need to be an exception if the data uses a currency numberformat. For US dollars, if the scale major increment were 2.5, the default should show 2 decimal places on the axis because that i customary for users of that currency. For other currencies where a single decimal place is standard, the default should match (e.g. 2.5) even if the scale major increment were 2.50.
Along the same lines, if the axis is showing large numbers (say in the millions), I'd like to see the default numberformat to use commas. And the task pane should make it easy to switch to thousands or millions as the units (i.e. the equivalent of a Custom numberformat like #, or #,,).
Brad Yundt commented
Actually, I do want the smart chart axis numberformat to override a selection I made for the raw data. For calculated engineering data, I usually want to see 3 or 4 significant figures, so I invariably change from General to a specific number of decimal places. But on the chart, I want the axis labels to show only 1 or 2 significant figures--and I am hoping for a chart builder that is smart enough to do this for me.
The General numberformat eats superfluous decimal places--what I am asking for--but unfortunately, it does so on a value by value basis. So I end up with a varying number of decimal places displayed in both the raw data as well as on the chart. To illustrate the problem on the chart axis, if you choose 0.25 as the major scale increment and a General numberformat, the chart axis annoyingly shows 0, 1 or 2 decimal places depending on the value. I'd much prefer to see a consistent number (2 in this case) of decimal places--and a smart chart builder would do this for me automatically.
Jon Peltier commented
I voted to give this suggestion visibility. But I have reservations any time there is a chance that Excel will override something I'm trying to do on purpose. Couldn't chart number formats have a "literal mode" (what I've told it) and also a "smart mode" (what Brad is suggesting here)?
There are other weaknesses in the ability to apply number formats to charts. For example, there are not the spinner buttons to increase or decrease decimals that we have in the Format Cells dialog, and the Number group on the Home tab is disabled for charts (WHY? A confusing subset of the Font and Alignment controls on the Home tab are enabled for charts). Also, in the Format Cells dialog, you can insert a line feed into the format by pressing Crtl+J, which is totally undocumented but has been around since the Stone Age. This format is good for displaying month and year, or month and day, on separate lines, and is arguably more useful in a chart than in a cell. However, you can't enter Ctrl+J in the Format Code box in the Format Chart task pane; you can copy a line feed character from somewhere else (NotePad?), but that is seriously tedious.
Tim Heng commented
Voted, but caveated by concerns I've raised elsewhere - this should only apply to certain number types (e.g. General, Number), and should definitely not override custom number formats in the data series that would otherwise flow on to the chart axis.