Hi everyone,

I know a big issue for people is that when you use an IF formula to
return a blank cell and graph it, it returns a zero value, for example
if(a10,a1,"") returns a blank cell that charts as a zero.

There is a solution to this problem that does not involve the more
common returning of an NA() value and conditionally formatting it out.

The solution is the use of dynamic ranges to set the chart values. It
is relatively easy and good practice as you can easily update and
reference data on other sheets. What you do is define a named range
for your data which dynamically extends with your information.

Step 1:
From the top file menu's select Insert Name Define.

Step 2:
Give it a name that you will remember, that cannot start with a number.

Step 3:

Define your range using the following formula:

=$b$17:INDEX($b$17:$m$17,1,MAX(IF($b$17:$m$17<"", COLUMN($b$17:$m$17)))-COLUMN($b$17)+1)

But replace $b$17 with the FIRST cell in the row, and replace $m$17
with the LAST cell in the row. (Make sure you use absolute references
($) otherwise it will not work!).

NB: If your data is in columns, not rows, you can reverse the last two
formulas in the INDEX formula and use the ROW function instead of
COLUMN. (ie, the INDEX formula uses row and then column -
INDEX(array,row_num,column_num)). Please post if you would like a ROW
formula posted.

Step 4:

In your chart, right click, select Source Data, select the series you
wish to set dynamically, and in the Values box enter ='[Sheet
name]'![Named range from Step 2].

Good luck!