excel chart - plotting blank cells

phiero21

Board Regular
Joined
Sep 20, 2007
Messages
136
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 =""
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
No Luck.

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

I am using Excel 2007 & simple bar graphs (if that helps)
 
Upvote 0
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.

Code:
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.
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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.

Code:
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,217,356
Messages
6,136,078
Members
449,988
Latest member
Mabbas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top