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

%3CLINGO-SUB%20id%3D%22lingo-sub-2171649%22%20slang%3D%22en-US%22%3EHow%20do%20I%20make%20a%20line%20chart%20ignore%20empty%20cells%20and%20NOT%20log%20then%20as%200%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2171649%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20made%20a%20line%20chart%20and%20I%20want%20it%20to%20log%20a%20potential%20of%2016%20data%20points%2C%20but%20those%20data%20points%20will%20be%20filled%20in%20over%20time%20so%20I%20want%20the%20line%20chart%20to%20only%20graph%20the%20real-time%20data%20for%20the%20number%20of%20cells%20that%20have%20been%20entered.%26nbsp%3B%20For%20example%20if%20cell%201%20is%20200%2C%20cell%202%20is%20225%2C%20and%20cell%203%20is%20250%2C%20and%20cell%204%20is%20empty%20I%20want%20the%20line%20graph%20to%20chart%20200%20to%20225%20and%20then%20225%20to%20250%20and%20then%20just%20end%20at%20250%2C%20NOT%20drop%20down%20to%200%20for%204%20to%2016.%26nbsp%3B%20%26nbsp%3BHow%20cand%20I%20do%20this%20in%20excel%20office%20365%20please.%26nbsp%3B%20The%20methods%20I%20have%20found%20via%20google%20do%20not%20work.%20For%20example%2C%20I%20found%20a%20method%20to%20%22ignore%20gaps%22%2C%20but%20that%20option%20isn't%20actually%20available%20to%20choose%20in%20365%20so%20how%20else%20can%20I%20do%20it%20please%3F%26nbsp%3B%20Thank%20you!%20Jay%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2171649%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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

6 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