A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formatting of Data Labels in Histograms



 
 
Thread Tools Display Modes
  #1  
Old November 4th, 2009, 12:17 AM posted to microsoft.public.excel.charting
hans L
external usenet poster
 
Posts: 14
Default 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  
Old November 4th, 2009, 04:50 PM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default 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  
Old November 5th, 2009, 04:48 PM posted to microsoft.public.excel.charting
hans L
external usenet poster
 
Posts: 14
Default 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  
Old November 5th, 2009, 06:31 PM posted to microsoft.public.excel.charting
hans L
external usenet poster
 
Posts: 14
Default 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  
Old November 5th, 2009, 09:02 PM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.