excel chart - plotting blank cells

phiero21

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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Instead of having your formulas return "", try NA() instead.
 

phiero21

Board Regular
Joined
Sep 20, 2007
Messages
129
No Luck.

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

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

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400

ADVERTISEMENT

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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,427
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Sep 20, 2007
Messages
129

ADVERTISEMENT

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

Board Regular
Joined
Sep 20, 2007
Messages
129
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.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,683
Messages
5,637,780
Members
416,982
Latest member
lisam77

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
Top