How not to graph blank cells

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Jul 11, 2002

• #2

Mike,

welcome to the board.

excel wont chart #n/a's - can you change the data / formula to replace blanks with #N/A ?

Paddy

 

Upvote 0

G

gnaga

Well-known Member

Joined

Jul 9, 2002

Messages

748

Office Version

1. 365
2. 2016
3. 2013

Platform

1. Windows

• Jul 11, 2002

• #3

mike

goto tools|options|chart

Choose option of plot empty cells as : Not ploted

gnaga

 

Upvote 0

T

Tommy

New Member

Joined

Jul 10, 2002

Messages

3

• Jul 11, 2002

• #4

Mike L, I just joined the forum as a member because I have the same problem. I'll be watching for someone to answer.
Tommy

Paddy & Gnana

Tried these options, don't work for me.

Tommy
This message was edited by Tommy on 2002-07-11 19:45

 

Upvote 0

T

Tommy

New Member

Joined

Jul 10, 2002

Messages

3

• Jul 11, 2002

• #5

I'm trying to plot blank cells that have formulas in them to a chart and it plots 0's even with the option to not plot blank cells.

I tried using the Iferror formula to force a blank cell, but it still plots a 0 on the graph. Help!!

Tommy

 

Upvote 0

G

gnaga

Well-known Member

Joined

Jul 9, 2002

Messages

748

Office Version

1. 365
2. 2016
3. 2013

Platform

1. Windows

• Jul 11, 2002

• #6

Hello Mike & Tommy

Answer for your question is only three options
1.Not ploted (Leave Blank)
2.Zero
3.Interpolated

You can try these three options or

Copy the Y axis value to other sheet and give that as source data. While copying do not copy the empty cell.

gnaga

 

Upvote 0

T

Tommy

New Member

Joined

Jul 10, 2002

Messages

3

• Jul 11, 2002

• #7

gnaga,

Thanks for the reply, but I would think that there must be an easier way to not plot the blank cells (that have formulas in them) as 0's in a graph besides copying Y axis data to another sheet and then plot.

Tommy

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Jul 11, 2002

• #8

if they've got formulas in them they're not blank!

Change the formula to return #N/A rather than nothing.

Paddy

 

Upvote 0

M

Mike L

New Member

Joined

Jul 10, 2002

Messages

18

• Jul 11, 2002

• #9

Thanks for all the help - got it so that it is graphing sweet by getting the formula to return #N/A instead of "".

But now I have #N/A showing up in the data table (which I also want to print). I've tried a conditional format on these (to effectively hide these cells from the printed page) but it doesn't work...any ideas?

Cheers

-Mike

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Jul 11, 2002

• #10

although you've entered the #N/A as text, excel recognises it as an error message (obviously - that's why it wont chart them!)

so

=iserror(a1)

with text font = white will do it.

Paddy

 

Upvote 0