excel chart - plotting blank cells

[phiero21]

Hi,

I have a problem while creating chart and searched the internet for solution. Found that many ppl are having the same problem but didn't find a solution that suits my needs.

I have a chart whose range contains of formulaes. I have used formulae to evaluate to blank (="") in certain scenarios.

When i am creating a graph (bar graph) showing data labels, the corresponding bar shows the value as 0 instead of blank.

I know that if i include a blank cell (truly blank, not =""), the data label won't show any value but this isn't happening in case of ="".

Is there any way i can show the data label as:

0 - If the cell equates to 0,
blank - if the cell equates to =""

 

[VoG]

Instead of having your formulas return "", try NA() instead.

 

[phiero21]

No Luck.

Now it shows "#N/A" in the data label.

I am using Excel 2007 & simple bar graphs (if that helps)

 

[phiero21]

Have uploaded the sample file here (in case it helps):

http://rapidshare.com/files/407641924/test.xlsx
http://www.esnips.com/webfolder/ecb09c50-d7a4-479d-a784-07418efa9e8c

 

[AlphaFrog]

The macro below clears cells that don't have numbers in them. Select all your cells with the Y axis values (C3:C9) and run the macro.

Sub ClearNullStrings()
    Dim cell As Range
    
    For Each cell In Selection
        If Not IsNumeric(cell.Value) Then cell.ClearContents
    Next cell
    
End Sub

Alternatively, you could have a macro that loops through the data labels on the chart and delete the individual non-numeric labels. That way the formulas are not deleted.

 

[JoeMo]

Hi,

I have a problem while creating chart and searched the internet for solution. Found that many ppl are having the same problem but didn't find a solution that suits my needs.

I have a chart whose range contains of formulaes. I have used formulae to evaluate to blank (="") in certain scenarios.

When i am creating a graph (bar graph) showing data labels, the corresponding bar shows the value as 0 instead of blank.

I know that if i include a blank cell (truly blank, not =""), the data label won't show any value but this isn't happening in case of ="".

Is there any way i can show the data label as:

0 - If the cell equates to 0,
blank - if the cell equates to =""

If you select the chart and go to Data Labels/Format Data Labels/Number and format number as 0;0;; - the zero data points will not be labeled.

 

[phiero21]

If you select the chart and go to Data Labels/Format Data Labels/Number and format number as 0;0;; - the zero data points will not be labeled.

Thanks Joe. But this is not working. I am using excel 2007. It should not be a problems ?

 

[phiero21]

The macro below clears cells that don't have numbers in them. Select all your cells with the Y axis values (C3:C9) and run the macro.

Sub ClearNullStrings()
    Dim cell As Range
    
    For Each cell In Selection
        If Not IsNumeric(cell.Value) Then cell.ClearContents
    Next cell
    
End Sub

Alternatively, you could have a macro that loops through the data labels on the chart and delete the individual non-numeric labels. That way the formulas are not deleted.

Thanks. But it's a sort of dynamic chart. So I can't delete the values.

Also, a macro that loops through the data label on the chart can be an option but since every time i change the data, will the macro autorun ? Also, if it run every time a value is updated, i guess it will make te workbook very slow to work with.

Thanks for your time and effort anyways.

 

[AlphaFrog]

How many data points are on your chart (10s, 100s, 1000s)?
Is there more than one data series?
What is the data range(s) where the Y values are?
Is it a "Column" chart?

 

[phiero21]

How many data points are on your chart (10s, 100s, 1000s)?
Is there more than one data series?
What is the data range(s) where the Y values are?
Is it a "Column" chart?

Have uploaded the sample file here (in case it helps):

http://rapidshare.com/files/407641924/test.xlsx
http://www.esnips.com/webfolder/ecb0...4-07418efa9e8c