Ignore cells on Excel line graph

excel excel-2010 linegraph
t0mmyw picture t0mmyw · Aug 19, 2013 · Viewed 126.3k times · Source

I am trying to draw a line graph in Excel 2010. The y column data source has some gaps in it and I want these to be ignored for the graph. Seems to default these to zero. I know the "Hidden and Empty Cell Settings" exists, but this is only giving the option to set it to zero. Any other way to get my graph looking the way I want it

Image available once I have enough reputation!

Answer

SeanC picture SeanC · Aug 19, 2013

if the data is the result of a formula, then it will never be empty (even if you set it to ""), as having a formula is not the same as an empty cell

There are 2 methods, depending on how static the data is.

The easiest fix is to clear the cells that return empty strings, but that means you will have to fix things if data changes

the other fix involves a little editing of the formula, so instead of setting it equal to "", you set it equal to NA().
For example, if you have =IF(A1=0,"",B1/A1), you would change that to =IF(A1=0,NA(),B1/A1).
This will create the gaps you desire, and will also reflect updates to the data so you don't have to keep fixing it every time

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Insert picture into Excel cell

I'm tying to generate a report with pictures, but I cannot get the pictures into a single cell. I can get the pictures to "float" around my worksheet, but I need to put them into a cell. How can I …

Read More