If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formatting of Data Labels in Histograms
I have used histograms extensively in MS2003 but have encountered a broblem
using MS2007. I have a spreadsheet that shows dollar amounts of contracts being awarded during certain months over a three year period. Some months more than one contract is awarded, others have one or none. Therefore, my histogram is a stacked column type. My X-axis shows months and my Y-axis dollars. The problem I am encountering is with the data labels which I put centered on the column. The dollar amounts show correctly but for some reason there is a zero at each interval of the X-axis, even for months where there are no values. There is also a zero at the top of each stacked column! When I delete the data labels the zeros disappear so I assume it has something to do with the formatting of the labels but I can't find it. Any suggestion would be appreciated. |
#2
|
|||
|
|||
Formatting of Data Labels in Histograms
Use a custom number format that suppresses zero values, for example:
$#,##0;;; For more information see Number Formats in Excel http://peltiertech.com/Excel/NumberFormats.html - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ hans L wrote: I have used histograms extensively in MS2003 but have encountered a broblem using MS2007. I have a spreadsheet that shows dollar amounts of contracts being awarded during certain months over a three year period. Some months more than one contract is awarded, others have one or none. Therefore, my histogram is a stacked column type. My X-axis shows months and my Y-axis dollars. The problem I am encountering is with the data labels which I put centered on the column. The dollar amounts show correctly but for some reason there is a zero at each interval of the X-axis, even for months where there are no values. There is also a zero at the top of each stacked column! When I delete the data labels the zeros disappear so I assume it has something to do with the formatting of the labels but I can't find it. Any suggestion would be appreciated. |
#3
|
|||
|
|||
Formatting of Data Labels in Histograms
Thanks. That works fine for MS2003 but I can't get it to work for MS2007.
There seems to be a discrepancy in functionality between the two formats. "Jon Peltier" wrote: Use a custom number format that suppresses zero values, for example: $#,##0;;; For more information see Number Formats in Excel http://peltiertech.com/Excel/NumberFormats.html - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ hans L wrote: I have used histograms extensively in MS2003 but have encountered a broblem using MS2007. I have a spreadsheet that shows dollar amounts of contracts being awarded during certain months over a three year period. Some months more than one contract is awarded, others have one or none. Therefore, my histogram is a stacked column type. My X-axis shows months and my Y-axis dollars. The problem I am encountering is with the data labels which I put centered on the column. The dollar amounts show correctly but for some reason there is a zero at each interval of the X-axis, even for months where there are no values. There is also a zero at the top of each stacked column! When I delete the data labels the zeros disappear so I assume it has something to do with the formatting of the labels but I can't find it. Any suggestion would be appreciated. . |
#4
|
|||
|
|||
Formatting of Data Labels in Histograms
An update. I've experimented with a small dataset and the problem is that
when it appears you are formatting a whole series of data it formats only one number. The "handles" that appear to indicate what is being formatted are misleading. Only when you format the final number do the zeros disappear. That raises the question: "how do you capture all the data labels at once". "hans L" wrote: Thanks. That works fine for MS2003 but I can't get it to work for MS2007. There seems to be a discrepancy in functionality between the two formats. "Jon Peltier" wrote: Use a custom number format that suppresses zero values, for example: $#,##0;;; For more information see Number Formats in Excel http://peltiertech.com/Excel/NumberFormats.html - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ hans L wrote: I have used histograms extensively in MS2003 but have encountered a broblem using MS2007. I have a spreadsheet that shows dollar amounts of contracts being awarded during certain months over a three year period. Some months more than one contract is awarded, others have one or none. Therefore, my histogram is a stacked column type. My X-axis shows months and my Y-axis dollars. The problem I am encountering is with the data labels which I put centered on the column. The dollar amounts show correctly but for some reason there is a zero at each interval of the X-axis, even for months where there are no values. There is also a zero at the top of each stacked column! When I delete the data labels the zeros disappear so I assume it has something to do with the formatting of the labels but I can't find it. Any suggestion would be appreciated. . |
#5
|
|||
|
|||
Formatting of Data Labels in Histograms
All labels in a series are selected when you first click on one of the
labels. When you click again on any of the labels, just the clicked label is selected. You may have to select something different (the plot area) before clicking on a label. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ hans L wrote: An update. I've experimented with a small dataset and the problem is that when it appears you are formatting a whole series of data it formats only one number. The "handles" that appear to indicate what is being formatted are misleading. Only when you format the final number do the zeros disappear. That raises the question: "how do you capture all the data labels at once". "hans L" wrote: Thanks. That works fine for MS2003 but I can't get it to work for MS2007. There seems to be a discrepancy in functionality between the two formats. "Jon Peltier" wrote: Use a custom number format that suppresses zero values, for example: $#,##0;;; For more information see Number Formats in Excel http://peltiertech.com/Excel/NumberFormats.html - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ hans L wrote: I have used histograms extensively in MS2003 but have encountered a broblem using MS2007. I have a spreadsheet that shows dollar amounts of contracts being awarded during certain months over a three year period. Some months more than one contract is awarded, others have one or none. Therefore, my histogram is a stacked column type. My X-axis shows months and my Y-axis dollars. The problem I am encountering is with the data labels which I put centered on the column. The dollar amounts show correctly but for some reason there is a zero at each interval of the X-axis, even for months where there are no values. There is also a zero at the top of each stacked column! When I delete the data labels the zeros disappear so I assume it has something to do with the formatting of the labels but I can't find it. Any suggestion would be appreciated. . |
Thread Tools | |
Display Modes | |
|
|