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 #,,).
I completely agree. I proof and copy edit articles and grants which have many figures. Every millimeter of space counts in the figures and the default of having decimal places on the y-axis(axes) takes up a lot of space. It makes little sense to have the axis decimal places the same as the values, when the point is to visualize the data in general, if I have to use decimals it would be in a similar fashion to Jon.
It also doesn't make sense that the axis font size is the same as the axis label. default larger font size for chart axis values, they should be smaller than the labels.
I never have had to increase the decimal place or increase the axis value font size.
Several iterations ago Microsoft got rid of the ability to use the formatting options on the toolbar for charts. Instead of clicking on the element and then using the same Font, Alignment, Number etc that we use for other programs, we have to use the chart options. This adds multiple mouse clicks.
Making the default no decimal would be helpful, however, bringing back the ability to format with a single click on the axis and then using the toolbar buttons would be even better.
Adding an easy way of displaying the axis in scientific notation format without using the space stealing E-01 format would be amazing. The x10^n belongs in the axis label most of the time. Currently to fix this I have to add an additional column to the data and divide the final values to get the format I need..
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.