Feb 27 2021 02:42 PM
Feb 27 2021 02:42 PM
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
Feb 27 2021 10:16 PM - edited Jan 27 2022 12:18 PM
@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.
Jan 27 2022 12:11 PM
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):
Jan 27 2022 12:20 PM
@KimberB Not sure I understand the question. How would you like the chart to look like?
Jan 27 2022 12:22 PM - edited Jan 27 2022 12:27 PM
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:
Jan 27 2022 12:34 PM
@KimberBUse a structured table in stead. Add a month and it will expand. The chart will expand with it.
Jan 27 2022 12:39 PM
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):
Aug 19 2022 11:43 PM - edited Aug 19 2022 11:44 PM
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.
Aug 22 2022 06:47 AM
Thank you for your thoughtful reply. Because of how the graphs and data are laid out, however, I cannot convert the data to tables.