Forum Discussion
How do I make a line chart ignore empty cells and NOT log then as 0?
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
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.
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):
KimberB Not sure I understand the question. How would you like the chart to look like?
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.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
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
I can validate this, switching from blanks to errors fixed my identical problem. Thanks!
