Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

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

Copper Contributor

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

10 Replies

@jayzzone11 Like this?

Screenshot 2021-02-28 at 07.08.42.png

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".

Screenshot 2021-02-28 at 07.06.40.png

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

@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):

KimberB_0-1643314044045.png

 

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

KimberB_1-1643314194133.png

 

KimberB_2-1643314240269.png

 

 

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

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.

KimberB_1-1643315221519.png

 

 

I want it to look like this:

KimberB_0-1643315032989.png

 

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

 

@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):

KimberB_0-1643315901118.png

 

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.

@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

@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. 

@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