Suppress zero value from chart

chrismbmc

New Member
Joined
Sep 2, 2002
Messages
2
Building 12 month running chart. I want to be able to add values as the year goes by and have the chart automatically update -Need to suppress 0 values that represent future periods from chart. I know blank cells can be dropped. Can you assign a blank cell via a formula?

Thanks for the input.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
> Can you assign a blank cell via a formula?

No, but you can assign #N/A.
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
You could also change the formatting... though I'm not sure it will affect your chart.

Select the cells to be formatted.
Press CTRL+1.
Click the NUMBER tab.
Under Category select CUSTOM.
To the left under Type:, key in #.
Click on OK.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Charted values are the internally stored values (which can include 0). Formatting never changes the internally stored value in a cell. A 0 is still a 0 regardless of its format.
 

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920

ADVERTISEMENT

chrismbmc,

Just follow up Mark´s answer with an example:

=IF(SUM(A4:A20)=0,NA(),SUM(A4:A20))
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
& just another addition - excel only charts visable cells, so you can exclude zeros (or indeed any other value) from a chart by applying an appropriate filter to the source data.

Paddy
 

Forum statistics

Threads
1,144,209
Messages
5,723,039
Members
422,476
Latest member
beck85

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