How do I make a line chart ignore empty cells and NOT log then as 0?

[jayzzone11]

Hello,

I made a line chart and I want it to log a potential of 16 data points, but those data points will be filled in over time so I want the line chart to only graph the real-time data for the number of cells that have been entered.  For example if cell 1 is 200, cell 2 is 225, and cell 3 is 250, and cell 4 is empty I want the line graph to chart 200 to 225 and then 225 to 250 and then just end at 250, NOT drop down to 0 for 4 to 16.   How cand I do this in excel office 365 please.  The methods I have found via google do not work. For example, I found a method to "ignore gaps", but that option isn't actually available to choose in 365 so how else can I do it please?  Thank you! Jay

[#2]

@jayzzone11 Like this?

If so, on the Chart Design ribbon,  choose Select Data (or right-click on the line and choose Select Data) and look for the option where you can tell Excel how to treat Hidden and Empty Cells. Choose "Connect data points with a line".

I'm on a Mac right now so it will be different from the above picture on a PC, but it's similar.

[#3]

@Riny_van_Eekelen 

I've had this work in the past, but it's not working for me this time around. Do you know if this changed since you posted this reply?

Here's what my graph looks like (ick):

Here's my data (I am selecting 12 rows for each line. They will become populated as the months go by):

[#4]

@KimberB Not sure I understand the question. How would you like the chart to look like?

[#5]

I'm sorry. I want it to only plot the three data points for each line right now. Then, as I add rows to the data, plot those too. I know I've done this a million times before, but for some reason, it is not working for me now.

I am selecting 12 rows like this, where 9 are blanks.

I want it to look like this:

[#6]

@KimberBUse a structured table in stead. Add a month and it will expand. The chart will expand with it.

[#7]

@Riny_van_Eekelen 

I don't think the way I have it laid out will work. I have 18 pages of graphs, four to a page, with the data for each to the right in the same spreadsheet. Eventually this will go in the DB and will use PowerBI, but had to do it quickly for now.

One page looks like this (but only print the graphs):

[#8]

Hi, I assume you would like a dynamic chart. I was in the same situation as you and I found out this tutorial: https://trumpexcel.com/dynamic-chart-range/, it basically use Excel Tables or formulas (if you still prefer to maintain the values in a range of cells).

I found the Excel Table method easier to follow, so I went with it:
1. Simply convert your range of values into table
2. Select the wanted cells that contain values
3. Insert your chart

As soon as you key in new value(s) under the last row of the table, it will be automatically included into the table, and also reflected on the chart.

[#9]

@carmen_cjm 

Thank you for your thoughtful reply. Because of how the graphs and data are laid out, however, I cannot convert the data to tables.

thanks,

Kimber

[#10]

@KimberB This is not working in 365 for me, either. The data popup has the button, but when you select (or Deselect/Save/Select) it still shows the data points as 0 on the graph. (0.0%, in my case)
Have tried to format the cells to no avail. 

[#11]

@jayzzone11 usually I populate the cellls to be filled in later with #N/A

Excel doesn’t include errors in charts. 

I use this to chart monthly sales through the year.  Months yet to happen get a #N/A. 

Leila gaharani (XCEL PLUS) has a video about this, too. I’ve spelled her name wrong I’m sure. 

mike

[#12]

@Michael_Morris620 

I can validate this, switching from blanks to errors fixed my identical problem. Thanks!